L’objectif de cet article est de détecter et d’analyser les sessions bloquées par une autre session, lors d’une transaction en cours.

Sans une gestion correcte des verrous, une application peut non seulement être lente, mais parfois avoir des temps de traitements aléatoires.

Par conséquent, l’apprentissage de techniques de verrouillage appropriées est essentiel au bon fonctionnement de nos applications.

 

Environnement de test pour en savoir plus sur les verrous

Dans une base de test, nous allons créer une table client qui contient quelques enregistrements.

 testdb=# CREATE TABLE CLIENT (id int, nom text);
CREATE TABLE

 

INSERT INTO CLIENT (id, nom) 
testdb-# VALUES (1, 'Dupont'), (2, 'Weber'), (3, 'Dubois'), (4, 'Carnois'),(5, 'Leroux'),(5, 'Topalof'); 
INSERT 0 6 TABLE testdb=#

 select * from client; 
id | nom ----+--------- 
1 | Dupont 
2 | Weber 
3 | Dubois 
4 | Carnois 
5 | Leroux 
5 | Topalof 
(6 rows)

Nous allons démarrer deux connexions parallèles sur la base de données, une pour démarrer et arrêter des transactions et l’autre pour observer et lister les verrous créés.

Par souci de simplicité, nous appellerons ces deux connexions Elsa et David.

Les deux comptes auront été préalablement créés.

David va créer des transactions et des verrous, tandis qu’Elsa sera notre administrateur qui observera l’état de la base de données.

Ouvrez deux fenêtres de terminal parallèles. Dans le premier terminal, démarrez une session psql en changeant le nom de l’invite en David.

La vue pg_locks fournit un accès aux informations sur les verrous détenus par les transactions ouvertes sur la base de données.

 

Description de la vue pg_locks

testdb=# pg_locks
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          |
 database           | oid      |           |          |
 relation           | oid      |           |          |
 page               | integer  |           |          |
 tuple              | smallint |           |          |
 virtualxid         | text     |           |          |
 transactionid      | xid      |           |          |
 classid            | oid      |           |          |
 objid              | oid      |           |          |
 objsubid           | smallint |           |          |
 virtualtransaction | text     |           |          |
 pid                | integer  |           |          |
 mode               | text     |           |          |
 granted            | boolean  |           |          |
 fastpath           | boolean  |           |          |

Examinons les verrous ouverts dans la base de données avec la commande :

(Elsa) # SELECT locktype, relation, mode, pid FROM pg_locks;
  locktype  | relation |      mode       | pid
------------+----------+-----------------+------
 relation   |    11645 | AccessShareLock | 1714
 virtualxid |          | ExclusiveLock   | 1714

La relation 11645 n’est pas assez descriptive. Nous allons utiliser :: regclass pour convertir le nombre en nom de la relation.

(Elsa) # SELECT locktype, relation::regclass, mode, pid FROM pg_locks; 
 
 locktype   | relation | mode            |  pid 
------------+----------+-----------------+------ 
relation    | pg_locks | AccessShareLock | 1714 
virtualxid  |          | ExclusiveLock   | 1714

Le verrou ouvert dans la base de données est le résultat de l’instruction SELECT qui répertorie les verrous. Filtrons ce qui se verrouille dans la liste résultante :

SELECT locktype, relation::regclass, mode, pid
testdb-# FROM pg_locks WHERE pid != pg_backend_pid();
 locktype | relation | mode | pid 
----------+----------+------+-----
(0 rows)s à partir de la session d’Elisa.

Nous pouvons voir qu’il n’y a pas de verrous ouverts dans la base de données.

Nous avons utilisé pg_backend_pid () pour filtrer tous les verrous créés de notre pid.

 

Détection d’un verrou simple

David va maintenant ouvrir une transaction, lister tous les enregistrements de la table Client. Cependant il ne fermera pas la transaction.

Cela nous permettra d’examiner le verrou créé implicitement avec l’instruction SELECT de David.

(David) # ;BEGIN;
BEGIN
(David) # ;select * from client
;
 id |   nom
----+---------
  1 | Dupont
  2 | Weber
  3 | Dubois
  4 | Carnois
  5 | Leroux
  5 | Topalof
(6 rows)

Examinons les verrous ouverts dans la base de données avec la commande :

(Elsa) #   SELECT locktype, relation::regclass, mode, pid FROM pg_locks WHERE pid != pg_backend_pid();
  locktype  | relation |      mode       | pid
------------+----------+-----------------+------
 relation   | client   | AccessShareLock | 1784
 virtualxid |          | ExclusiveLock   | 1784

David va maintenant ouvrir une transaction, ajouter une colonne à la table Client, le téléphone des utilisateurs. Cependant il ne fermera pas la transaction.

Cela nous permettra d’examiner les verrous créés implicitement avec l’instruction ALTER TABLE de David.

(David) # BEGIN;
BEGIN
(David) # ALTER TABLE client  ADD tel  int;

(Elsa) # SELECT locktype, relation::regclass, mode, pid FROM pg_locks WHERE pid != pg_backend_pid();
   locktype    | relation |        mode         | pid
---------------+----------+---------------------+------
 virtualxid    |          | ExclusiveLock       | 1784
 transactionid |          | ExclusiveLock       | 1784
 relation      | client   | AccessExclusiveLock | 1784

La session de David a posé un verrou de type AccessExclusiveLock sur la table Client.

Ce type de verrous est en conflit avec les verrouillages de tous les modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE).

Ce mode garantit que le titulaire dispose de la seule transaction à accéder à la table de quelque manière que ce soit.

Acquis par les commandes ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER et VACUUM FULL.

C’est également le mode de verrouillage par défaut pour les instructions LOCK TABLE qui ne spécifient pas explicitement un mode.

Nous allons maintenant exécuter un SELECT de la session d’Elsa :

(Elsa) # SELECT * FROM client;

Nous constatons que l’instruction SELECT reste bloquée en attente.

Ouvrons une nouvelle session pour Elsa afin d’examiner les verrous créés implicitement avec l’instruction ALTER TABLE de David.

(Elsa) # SELECT locktype, relation::regclass, mode, pid FROM pg_locks WHERE pid != pg_backend_pid();
   locktype    | relation |        mode         | pid
---------------+----------+---------------------+------
 virtualxid    |          | ExclusiveLock       | 1784
 virtualxid    |          | ExclusiveLock       | 1692
 relation      | client   | AccessShareLock     | 1692
 transactionid |          | ExclusiveLock       | 1784
 relation      | client   | AccessExclusiveLock | 1784
(5 rows)

Nous constatons que la session de reste visiblement bloquée par le verrou posé par David.

Pour détecter plus facilement une session bloquante, nous pouvons utiliser la requête ci-dessous :

(Elsa) # SELECT  activity.pid,activity.usename,activity.query,blocking.pid AS blocking_id,
testdb-# blocking.query AS blocking_query
testdb-# FROM pg_stat_activity AS activity
testdb-#   JOIN pg_stat_activity AS blocking
testdb-# ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
 pid  | usename  |         query          | blocking_id |          blocking_query
------+----------+------------------------+-------------+-----------------------------------
 1692 | postgres | select * from client ; |        1784 | ALTER TABLE client  ADD tel  int;

 

Pour débloquer son instruction SELECT en cours, Elsa peux lancer la commande de kill suivante :

SELECT pg_terminate_backend(1784);

(Elsa) # SELECT pg_terminate_backend(1784); 
 pg_terminate_backend
----------------------
 t
(1 row)

 
Et si vous souhaitez vous former sur PostgreSQL, découvrez notre offre de formations PostgreSQL.