Comment détecter les verrous dans Amazon Redshift et les désactiver ?

Date de la dernière mise à jour : 06/10/2020

Mes requêtes sont bloquées par des verrous de tables dans Amazon Redshift. Comment les détecter et les désactiver ?

Brève description

Le blocage est un mécanisme de protection qui permet de contrôler le nombre de sessions pouvant accéder à une table au même moment. Le blocage détermine également les opérations qui peuvent être effectuées avec ces sessions. La plupart des bases de données relationnelles appliquent des blocages par verrou au niveau des lignes. En revanche, Amazon Redshift applique un blocage au niveau des tables. Vous pouvez rencontrer des problèmes de blocage si vous exécutez fréquemment des instructions DDL au niveau de tables utilisateur ou de requêtes DML.

Amazon Redshift comprend trois modes de blocage :

  • AccessExclusiveLock : principalement lors des opérations DDL, par exemple de type ALTER TABLE, DROP ou TRUNCATE. Le mode AccessExclusiveLock bloque toutes les autres tentatives de verrouillage.
  • AccessShareLock : lors des opérations UNLOAD, SELECT, UPDATE ou DELETE. Le mode AccessShareLock ne bloque que les tentatives de blocage AccessExclusiveLock. Le mode AccessShareLock ne bloque pas les autres sessions qui tentent de lire ou d'écrire des données dans la table.
  • ShareRowExclusiveLock : lors des opérations COPY, INSERT, UPDATE ou DELETE. Le mode ShareRowExclusiveLock empêche les blocages AccessExclusiveLock et d'autres tentatives de blocage de type ShareRowExclusiveLock, mais pas de type AccessShareLock.

Lorsqu'un verrou est appliqué à une requête ou une transaction au niveau d'une table, il reste effectif pendant la durée de cette même requête ou transaction. Les autres requêtes ou transactions en attente du même verrou sont bloquées. Pour plus d'informations sur le blocage des requêtes, consultez la section La requête se bloque.

Pour remédier à un problème de blocage, identifiez la session qui en est à l'origine, puis mettez-y fin. Si la session ne se termine pas, redémarrez votre cluster.

Résolution

Exécutez une requête pour identifier les sessions qui comporte un verrou :

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;

Le résultat doit être similaire à ce qui suit :

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 correspond à f (false), une transaction s'exécutant dans une autre session comporte le verrou. La colonne blocking_pid comporte l'ID de processus associé à la session qui comporte le verrou. Dans cet exemple, il s'agit de la session « PID 19813 ».

Pour désactiver un verrou, attendez que la transaction qui est à l'origine du blocage se termine. Vous pouvez également mettre fin manuellement à la session en exécutant la commande suivante :

select pg_terminate_backend(PID);

La fin d'une session entraîne l'annulation de toutes les transactions en cours d'exécution et la désactivation de tous les verrous. Les verrous sont ensuite enregistrés par les autres transactions qui attendent d'acquérir le verrou.

Remarque : lorsque la commande PG_TERMINATE_BACKEND(PID) produit le résultat « 1 », cela signifie généralement qu'une requête demandant la fin d'une session a été transmise avec succès. Toutefois, cela ne garantit pas que la session a réellement pris fin. Dans certains cas, l'état d'une session ne peut pas être déterminé. Il est donc recommandé de vérifier également la section STV_SESSIONS (et les autres tables système pertinentes) pour s'assurer que la session a effectivement pris fin.

Si la commande PG_TERMINATE_BACKEND(PID) ne s'effectue pas avec succès, redémarrez le cluster pour mettre fin au processus.


Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?