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

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. Toutefois, Amazon Redshift emploie un verrouillage au niveau de la table. Vous pouvez rencontrer des problèmes de verrouillage si vous exécutez fréquemment des instructions DDL au niveau de tables utilisateur ou de requêtes DML.

Amazon Redshift offre trois modes de verrouillage :

• 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 bloque les tentatives AccessExclusiveLock et d'autres tentatives de verrouillage de type ShareRowExclusiveLock, mais pas les tentatives AccessShareLock.

Lorsqu'une requête ou une transaction acquiert un verrou sur une table, le verrou reste en place pendant toute la durée de la requête ou de la transaction. Les autres requêtes ou transactions en attente du même verrou sont bloquées à l'exception d'AccessShareLock. Pour plus d'informations sur le blocage des requêtes, consultez 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.

Solution

Exécutez une requête pour identifier les sessions qui contiennent des verrous :

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;

La sortie ressemble à :

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 dans la colonne granted (accordé) est f (false), alors une transaction dans une autre session contient le verrou. La colonne blocking_pid indique l'ID du processus associé à la session qui contient 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 terminaison d'un PID annule toutes les transactions en cours et désactive tous les verrous de la session. Les verrous sont ensuite réclamés par les autres transactions en attente d'acquisition du verrou.

Remarque : la valeur « 1 » renvoyée par la commande PG_TERMINATE_BACKEND(PID) indique généralement que la demande de terminaison adressée au PID a abouti. Toutefois, cela ne garantit pas que le PID a effectivement pris fin. Dans certains cas, le PID ne peut pas être déterminé en fonction de son état interne. Par conséquent, une bonne pratique consiste à vérifier également STV_SESSIONS (et d'autres tables système pertinentes) pour confirmer que le PID a effectivement pris fin.

Si la commande PG_TERMINATE_BACKEND(PID) ne réussit pas à mettre fin à la session, redémarrez le cluster pour mettre fin au processus. Le redémarrage peut être effectué par un redémarrage du cluster via la console Amazon Redshift ou via un redémarrage de la base de données à l'aide de la commande REBOOT_CLUSTER.

Informations connexes

LOCK (Amazon Redshift)

Situation de blocage potentiel pour les transactions d'écriture simultanées