Comment puis-je détecter et débloquer les verrous dans Amazon Redshift ?

Brève description

Vous pouvez rencontrer des conflits de verrouillage lorsque vous exécutez fréquemment des instructions DDL (Data Definition Language) sur des tables utilisateur ou des requêtes DML (Data Manipulation Language).

Amazon Redshift propose les trois modes de verrouillage suivants :

• Le verrouillage AccessExclusiveLock bloque toutes les autres tentatives de verrouillage et est acquis principalement lors d'opérations DDL, telles que ALTER TABLE, DROP ou TRUNCATE.
• Le verrouillage AccessShareLock bloque uniquement les tentatives AccessExclusiveLock et est acquis lors des opérations UNLOAD, SELECT, UPDATE ou DELETE. Le verrouillage AccessShareLock ne bloque pas les autres sessions qui tentent de lire ou d'écrire sur la table.
• Le verrouillage ShareRowExclusiveLock bloque AccessExclusiveLock et les autres tentatives ShareRowExclusiveLock, mais ne bloque pas les tentatives AccessShareLock. Le verrouillage ShareRowExclusiveLock est acquis lors des opérations COPY, INSERT, UPDATE ou DELETE.

Pour résoudre ce problème, identifiez les verrous de table problématiques, identifiez la requête de problème (si nécessaire), puis relâchez les verrous de table problématiques.

Résolution

Remarque : Si des erreurs surviennent lorsque vous exécutez des commandes de l'interface de la ligne de commande AWS (AWS CLI), consultez la section Résoudre des erreurs liées à l’AWS CLI. Vérifiez également que vous utilisez bien la version la plus récente de l'interface.

Détecter les verrous

Pour identifier les processus qui détiennent des verrous, exécutez la requête suivante :

SELECT
  a.txn_start,
  datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' AS txn_duration,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(trim(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
FROM SVV_TRANSACTIONS a
LEFT JOIN (SELECT pid, relation, granted FROM PG_LOCKS GROUP BY 1,2,3) b
  ON a.relation = b.relation AND a.granted='f' AND b.granted='t'
LEFT JOIN (SELECT * FROM SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = 'database name'
  AND tablename = 'table name';

Pour détecter les verrous dans une table spécifique, remplacez database name par le nom de votre base de données et remplacez table name par le nom de votre table.

Remarque : Vous pouvez exécuter la requête précédente à la fois sur un cluster provisionné Amazon Redshift et sur Amazon Redshift sans serveur.

Exemple de sortie :

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

Si le résultat de la colonne accordé est f (faux), cela signifie que la transaction est en attente de verrouillage car le verrou est détenu par une autre transaction. La colonne blocking_pid indique l'ID de processus (PID) de la session qui détient le verrou.

Détecter la requête problématique

Si le problème de verrouillage de la table spécifique se produit régulièrement, utilisez SYS_QUERY_HISTORY pour identifier la requête à l'origine du problème.

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

Libérer les verrous

Pour libérer les verrous, procédez comme suit :

1. Attendez que la transaction qui détient le verrou soit libérée.
2. Exécutez la fonction PG_TERMINATE_BACKEND pour libérer le verrou manuellement.
   Remarque : La requête PG_TERMINATE_BACKEND(PID) renvoie la valeur 1 lorsque la commande demande avec succès l'arrêt du processus. Il est recommandé de vérifier SYS_SESSION_HISTORY pour confirmer que le processus est arrêté.
3. Pour un cluster provisionné par Redshift, si PG_TERMINATE_BACKEND n'interrompt pas correctement le processus, exécutez la fonction REBOOT_CLUSTER.
   Remarque : REBOOT_CLUSTER redémarre le cluster sans fermer les connexions.
4. Pour un cluster provisionné par Redshift, si REBOOT_CLUSTER n'interrompt pas correctement le processus, redémarrez le cluster à partir de la console Amazon Redshift ou exécutez la commande reboot-cluster.
   Remarque : reboot-cluster ferme toutes les connexions en cours.