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 trois modes de verrouillage :

• 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. 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. ShareRowExclusiveLock est acquis lors des opérations COPY, INSERT, UPDATE ou DELETE.

Lorsqu'une requête ou une transaction acquiert un verrou sur une table, le verrou reste bloqué pendant toute la durée de la requête ou de la transaction. Les autres requêtes ou transactions en attente d'acquisition du même verrou sont bloquées, à l'exception d'AccessShareLock. Pour plus d'informations sur les raisons pour lesquelles une requête peut ne pas répondre, consultez la section Résolution des problèmes liés aux requêtes.

Résolution

Remarque : La résolution suivante est destinée aux utilisateurs qui ne disposent pas d'un déploiement multi-AZ.

Identifiez le processus de la session qui détient le verrou, puis arrêtez-le. Si le processus ne s'arrête pas, redémarrez votre cluster.

Pour identifier les processus verrouillés, exécutez la requête suivante :

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,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 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 stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

Exemple de sortie :

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         | ----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock     |   351959 | lineorder | f       |        19813 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

Si le résultat de la colonne granted est f (faux), cela signifie qu'une transaction d'une autre session est bloquée. La colonne blocking_pid indique l'ID de processus (PID) de la session qui détient le verrou. Dans l'exemple précédent, le PID 19813 détient le verrou.

Pour libérer un verrou, attendez que la transaction qui le bloque soit terminée.

Vous pouvez également exécuter la commande suivante pour terminer le processus manuellement :

select pg_terminate_backend(PID);

Remarque : Dans la commande précédente, remplacez PID par l'ID de votre processus.

Lorsque vous arrêtez le processus, la session restaure toutes les transactions en cours et libère tous les verrous de la session. Les autres transactions qui attendent d'acquérir le verrou réclament ensuite le verrou.

Remarque : La commande PG_TERMINATE_BACKEND(PID) renvoie la valeur 1 pour indiquer que la commande a demandé avec succès l'arrêt du processus. Cependant, il se peut que le processus ne s'arrête toujours pas. Il est recommandé de vérifier STV_SESSIONS et les autres tables système pertinentes pour confirmer que le processus est arrêté.

Si PG_TERMINATE_BACKEND(PID) n'arrête pas correctement le processus, utilisez la console Amazon Redshift ou exécutez REBOOT_CLUSTER pour redémarrer le cluster et arrêter le processus.

Informations connexes

LOCK

Situation d'impasse potentielle pour les transactions d'écriture simultanées