PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 13.16 » Langage SQL » Contrôle d'accès simultané » Verrouillage explicite

13.3. Verrouillage explicite

PostgreSQL fournit de nombreux modes de verrous pour contrôler les accès simultanés aux données des tables. Ces modes peuvent être utilisés pour contrôler le verrouillage par l'application dans des situations où MVCC n'a pas le comportement désiré. De plus, la plupart des commandes PostgreSQL acquièrent automatiquement des verrous avec les modes appropriés pour s'assurer que les tables référencées ne sont pas supprimées ou modifiées de façon incompatible lorsque la commande s'exécute (par exemple, TRUNCATE ne peut pas être exécuté de façon sûre en même temps que d'autres opérations sur la même table, donc il obtient un verrou de type ACCESS EXCLUSIVE sur la table pour s'assurer d'une bonne exécution).

Pour examiner une liste des verrous en cours, utilisez la vue système pg_locks. Pour plus d'informations sur la surveillance du statut du sous-système de gestion des verrous, référez-vous au Chapitre 27.

13.3.1. Verrous de niveau table

La liste ci-dessous affiche les modes de verrous disponibles et les contextes dans lesquels ils sont automatiquement utilisés par PostgreSQL. Vous pouvez aussi acquérir explicitement n'importe lequel de ces verrous avec la commande LOCK. Rappelez-vous que tous ces modes de verrous sont des verrous au niveau table, même si le nom contient le mot « row » (NdT : ligne) ; les noms des modes de verrous sont historiques. Dans une certaine mesure, les noms reflètent l'utilisation typique de chaque mode de verrou -- mais la sémantique est identique. La seule vraie différence entre un mode verrou et un autre est l'ensemble des modes verrous avec lesquels ils rentrent en conflit (voir Tableau 13.2). Deux transactions ne peuvent pas conserver des verrous de modes en conflit sur la même table au même moment (néanmoins, une transaction n'entre jamais en conflit avec elle-même. Par exemple, elle pourrait acquérir un verrou ACCESS EXCLUSIVE et acquérir plus tard un verrou ACCESS SHARE sur la même table). Des modes de verrou sans conflit peuvent être détenus en même temps par plusieurs transactions. Notez, en particulier, que certains modes de verrous sont en conflit avec eux-mêmes (par exemple, un verrou ACCESS EXCLUSIVE ne peut pas être détenu par plus d'une transaction à la fois) alors que d'autres n'entrent pas en conflit avec eux-mêmes (par exemple, un verrou ACCESS SHARE peut être détenu par plusieurs transactions).

Modes de verrous au niveau table

ACCESS SHARE (AccessShareLock)

En conflit avec le mode verrou ACCESS EXCLUSIVE.

Les commandes SELECT acquièrent un verrou de ce mode avec les tables référencées. En général, toute requête lisant seulement une table et ne la modifiant pas obtient ce mode de verrou.

ROW SHARE (RowShareLock)

En conflit avec les modes de verrous EXCLUSIVE et ACCESS EXCLUSIVE.

La commande SELECT FOR UPDATE et SELECT FOR SHARE acquièrent un verrou de ce mode avec la table cible (en plus des verrous ACCESS SHARE des autres tables référencées, mais pas sélectionnées FOR UPDATE/FOR SHARE).

ROW EXCLUSIVE (RowExclusiveLock)

En conflit avec les modes de verrous SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE.

Les commandes UPDATE, DELETE et INSERT acquièrent ce mode de verrou sur la table cible (en plus des verrous ACCESS SHARE sur toutes les autres tables référencées). En général, ce mode de verrouillage sera acquis par toute commande modifiant des données de la table.

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)

En conflit avec les modes de verrous SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE. Ce mode protège une table contre les modifications simultanées de schéma et l'exécution d'un VACUUM.

Acquis par VACUUM (sans FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, et certaines variantes de ALTER INDEX et ALTER TABLE (pour plus de détails, voir ALTER INDEX et ALTER TABLE).

SHARE (ShareLock)

En conflit avec les modes de verrous ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE. Ce mode protège une table contre les modifications simultanées des données.

Acquis par CREATE INDEX (sans CONCURRENTLY).

SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)

En conflit avec les modes de verrous ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE. Ce mode protège une table contre les modifications concurrentes de données, et est en conflit avec elle-même, afin qu'une seule session puisse le posséder à un moment donné.

Acquis par CREATE TRIGGER et différentes formes de ALTER TABLE (voir ALTER TABLE).

EXCLUSIVE (ExclusiveLock)

En conflit avec les modes de verrous ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS EXCLUSIVE. Ce mode autorise uniquement les verrous ACCESS SHARE concurrents, c'est-à-dire que seules les lectures à partir de la table peuvent être effectuées en parallèle avec une transaction contenant ce mode de verrouillage.

Acquis par REFRESH MATERIALIZED VIEW CONCURRENTLY.

ACCESS EXCLUSIVE (AccessExclusiveLock)

Entre en conflit avec 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 détenteur est la seule transaction à accéder à la table de quelque façon que ce soit.

Acquis par les commandes DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (sans l'option CONCURRENTLY). De nombreuses formes d'ALTER INDEX et d'ALTER TABLE acquièrent également un verrou de ce niveau. C'est aussi le mode de verrou par défaut des instructions LOCK TABLE qui ne spécifient pas explicitement de mode de verrouillage.

Astuce

Seul un verrou ACCESS EXCLUSIVE bloque une instruction SELECT (sans FOR UPDATE/SHARE).

Une fois acquis, un verrou est normalement détenu jusqu'à la fin de la transaction. Mais si un verrou est acquis après l'établissement d'un point de sauvegarde, le verrou est relâché immédiatement si le point de sauvegarde est annulé. Ceci est cohérent avec le principe du ROLLBACK annulant tous les effets des commandes depuis le dernier point de sauvegarde. Il se passe la même chose pour les verrous acquis à l'intérieur d'un bloc d'exception PL/pgSQL : un échappement d'erreur à partir du bloc lâche les verrous acquis dans le bloc.

Tableau 13.2. Modes de verrou conflictuels

Verrou demandéMode verrou existant
ACCESS SHAREROW SHAREROW EXCL.SHARE UPDATE EXCL.SHARESHARE ROW EXCL.EXCL.ACCESS EXCL.
ACCESS SHARE       X
ROW SHARE      XX
ROW EXCLUSIVE    XXXX
SHARE UPDATE EXCLUSIVE   XXXXX
SHARE  XX XXX
SHARE ROW EXCLUSIVE  XXXXXX
EXCLUSIVE XXXXXXX
ACCESS EXCLUSIVEXXXXXXXX

13.3.2. Verrous au niveau ligne

En plus des verrous au niveau table, il existe des verrous au niveau ligne. Ils sont listés ci-dessous, avec les contextes de leur utilisation automatique par PostgreSQL. Voir Tableau 13.3 pour une table complète des conflits de verrou niveau ligne. Notez qu'une transaction peut détenir des verrous en conflit sur la même ligne, y compris sur des sous-transactions différentes ; mais en dehors de cela, deux transactions ne peuvent jamais détenir des verrous en conflit pour la même ligne. Les verrous au niveau ligne n'affectent pas les lectures des données ; elles bloquent seulement les écrivains et verrouilleurs sur la même ligne. Les verrous au niveau ligne sont relachés à la fin de la transaction ou lors de l'annulation du savepoint, tout comme les verrous de niveau table.

Modes des verrous au niveau ligne

FOR UPDATE

FOR UPDATE verrouille pour modification les lignes récupérées par l'instruction SELECT. Cela les empêche d'être modifiées ou supprimées par les autres transactions jusqu'à la fin de la transaction en cours. Les autres transactions qui tentent des UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE ou SELECT FOR KEY SHARE sur ces lignes sont bloquées jusqu'à la fin de la transaction courante ; et inversement, SELECT FOR UPDATE attendra après une transaction concurrente qui a exécuté une de ces commandes sur la même ligne et qui verrouillera et renverra la ligne mise à jour (ou aucune ligne si elle a été supprimée). Néanmoins, à l'intérieur d'une transaction REPEATABLE READ ou SERIALIZABLE, une erreur sera renvoyée si une ligne à verrouiller a changé depuis que la transaction a commencé. Pour plus de détails, voir Section 13.4.

Le mode de verrouillage FOR UPDATE est aussi acquis par toute commande DELETE sur une ligne ainsi que par un UPDATE qui modifie les valeurs de certaines colonnes. Actuellement, l'ensemble de colonnes considéré par le cas UPDATE est celui qui a un index unique lors de son utilisation par une clé étrangère (donc les index partiels et fonctionnels ne sont pas considérés), mais cela pourra être modifié dans le futur.

FOR NO KEY UPDATE

FOR NO KEY UPDATE se comporte de la même façon que FOR UPDATE sauf que le verrou acquis est moins fort : ce verrou ne bloquera pas les commandes SELECT FOR KEY SHARE qui tenteraient d'acquérir un verrou sur les mêmes lignes. Ce mode de verrou est aussi acquis par tout UPDATE qui ne nécessite pas un verrou FOR UPDATE.

FOR SHARE

FOR SHARE a un comportement similaire à FOR NO KEY UPDATE, sauf qu'il obtient un verrou partagé plutôt qu'un verrou exclusif sur chaque ligne récupérée. Un verrou partagé bloque les autres transactions réalisant des UPDATE, DELETE, SELECT FOR UPDATE et SELECT FOR NO KEY UPDATE sur ces lignes, mais il n'empêche pas les SELECT FOR SHARE et SELECT FOR KEY SHARE.

FOR KEY SHARE

FOR KEY SHARE a un comportement similaire à FOR SHARE, sauf que le verrou est plus faible : SELECT FOR UPDATE est bloqué alors que SELECT FOR NO KEY UPDATE ne l'est pas. Un verrou à clé partagée bloque les autres transactions lors de l'exécution d'un DELETE ou d'un UPDATE qui modifie les valeurs clés, mais pas les autres UPDATE. Il n'empêche pas non plus les SELECT FOR NO KEY UPDATE, SELECT FOR SHARE et SELECT FOR KEY SHARE.

PostgreSQL ne garde en mémoire aucune information sur les lignes modifiées, il n'y a donc aucune limite sur le nombre de lignes verrouillées à un moment donné. Néanmoins, verrouiller une ligne peut causer une écriture disque ; ainsi, SELECT FOR UPDATE modifie les lignes sélectionnées pour les marquer verrouillées et cela aboutit à des écritures disques.

Tableau 13.3. Verrous en conflit au niveau ligne

Verrou demandéVerrou en cours
FOR KEY SHAREFOR SHAREFOR NO KEY UPDATEFOR UPDATE
FOR KEY SHARE   X
FOR SHARE  XX
FOR NO KEY UPDATE XXX
FOR UPDATEXXXX

13.3.3. Verrous au niveau page

En plus des verrous tables et lignes, les verrous partagés/exclusifs sur les pages sont utilisés pour contrôler la lecture et l'écriture des pages de table dans l'ensemble des tampons partagées. Ces verrous sont immédiatement relâchés une fois la ligne récupérée ou mise à jour. Les développeurs d'applications ne sont normalement pas concernés par les verrous au niveau page, mais nous les mentionnons dans un souci d'exhaustivité.

13.3.4. Verrous morts (blocage)

L'utilisation de verrous explicites accroît le risque de verrous morts lorsque deux transactions (voire plus) détiennent chacune un verrou que l'autre convoite. Par exemple, si la transaction 1 a acquis un verrou exclusif sur la table A puis essaie d'acquérir un verrou exclusif sur la table B alors que la transaction 2 possède déjà un verrou exclusif sur la table B et souhaite maintenant un verrou exclusif sur la table A, alors aucun des deux ne peut continuer. PostgreSQL détecte automatiquement ces situations de blocage et les résout en annulant une des transactions impliquées, permettant ainsi à l'autre (aux autres) de se terminer (savoir quelle transaction est réellement annulée est difficile à prévoir, mais vous ne devriez pas vous en préoccuper).

Notez que les verrous morts peuvent aussi se produire suite à des verrous de niveau ligne (et du coup, ils peuvent se produire même si le verrouillage explicite n'est pas utilisé). Considérons le cas où il existe deux transactions concurrentes modifiant une table. La première transaction exécute :

UPDATE comptes SET balance = balance + 100.00 WHERE no_compte = 11111;

Elle acquiert un verrou au niveau ligne sur la ligne spécifiée par le numéro de compte (no_compte). Ensuite, la deuxième transaction exécute :

UPDATE comptes SET balance = balance + 100.00 WHERE no_compte = 22222;
UPDATE comptes SET balance = balance - 100.00 WHERE no_compte = 11111;

La première instruction UPDATE acquiert avec succès un verrou au niveau ligne sur la ligne spécifiée, donc elle réussit à mettre à jour la ligne. Néanmoins, la deuxième instruction UPDATE trouve que la ligne qu'elle essaie de mettre à jour a déjà été verrouillée, alors elle attend la fin de la transaction ayant acquis le verrou. Maintenant, la première transaction exécute :

UPDATE comptes SET balance = balance - 100.00 WHERE no_compte = 22222;

La première transaction essaie d'acquérir un verrou au niveau ligne sur la ligne spécifiée, mais ne le peut pas : la deuxième transaction détient déjà un verrou. Donc, elle attend la fin de la transaction deux. Du coup, la première transaction est bloquée par la deuxième et la deuxième est bloquée par la première : une condition de blocage, un verrou mort. PostgreSQL détectera cette situation et annulera une des transactions.

Le niveau d'isolation Repeatable Read est implémenté en utilisant une technique connue dans la littérature académique sur les bases de données et dans certains produits de bases de données sous le nom de Snapshot Isolation. Des différences en comportement et en performance peuvent être observées lors de comparaisons avec des systèmes qui utilisent une technique de verrouillage traditionnelle qui réduit la concurrence. Quelques autres systèmes peuvent même proposer Repeatable Read et Snapshot Isolation sous la forme de niveaux d'isolation distincts avec des comportements différents. Les phénomènes qui distinguent les deux techniques n'ont pas été formalisés par les chercheurs en bases de données jusqu'à ce que le standard SQL ne soit écrit. Pour un traitement complet, voir [berenson95].

La meilleure défense contre les verrous morts est généralement de les éviter en s'assurant que toutes les applications utilisant une base de données acquièrent des verrous sur des objets multiples dans un ordre cohérent. Dans l'exemple ci-dessus, si les deux transactions avaient mis à jour les lignes dans le même ordre, aucun blocage n'aurait eu lieu. Vous devez vous assurer que le premier verrou acquis sur un objet dans une transaction est dans le mode le plus restrictif pour cet objet. S'il n'est pas possible de vérifier ceci à l'avance, alors les blocages doivent être gérés à l'exécution en réessayant les transactions annulées à cause du blocage.

Tant qu'aucune situation de blocage n'est détectée, une transaction cherchant soit un verrou de niveau table soit un verrou de niveau ligne attend indéfiniment que les verrous en conflit soient relâchés. Ceci signifie que maintenir des transactions ouvertes sur une longue période de temps (par exemple en attendant une saisie de l'utilisateur) est parfois une mauvaise idée.

13.3.5. Verrous informatifs

PostgreSQL fournit un moyen pour créer des verrous qui ont une signification définie par l'application. Ils sont qualifiés d'informatifs, car le système ne force pas leur utilisation -- c'est à l'application de les utiliser correctement. Les verrous informatifs peuvent être utiles pour des manières d'utiliser le verrouillage qui ne sont pas en phase avec le modèle MVCC. Par exemple, une utilisation habituelle des verrous informatifs est l'émulation de stratégie de verrouillage pessimiste typique des systèmes de gestion de données à partir de « fichiers à plat ». Bien qu'un drapeau stocké dans une table puisse être utilisé pour la même raison, les verrous informatifs sont plus rapides, évitent la fragmentation de la table et sont nettoyés automatiquement par le serveur à la fin de la session.

Il existe deux façons pour acquérir un verrou informatif dans PostgreSQL : au niveau de la session ou au niveau de la transaction. Une fois acquis au niveau de la session, un verrou informatif est détenu jusqu'à ce que le verrou soit explicitement relâché ou à la fin de la session. Contrairement aux demandes de verrou standard, les demandes de verrous informatifs au niveau session n'honorent pas la sémantique de la transaction : un verrou acquis lors d'une transaction qui est annulée plus tard sera toujours acquis après le ROLLBACK, et de la même façon, un verrou relâché reste valide même si la transaction appelante a échoué après. Un verrou peut être acquis plusieurs fois par le processus qui le détient ; pour chaque demande de verrou terminée, il doit y avoir une demande de relâche du verrou correspondant avant que ce dernier ne soit réellement relâché. D'un autre côté, les demandes de verrou au niveau transaction se comportent plutôt comme des demandes de verrous standards : les verrous sont automatiquement relâchés à la fin de la transaction, et il n'y a pas d'opération explicite de déverrouillage. Ce comportement est souvent plus intéressant que le comportement au niveau session pour un usage rapide d'un verrou informatif. Les demandes de verrou au niveau session et transaction pour le même identifiant de verrou informatif se bloqueront de la façon attendue. Si une session détient déjà un verrou informatif donné, les demandes supplémentaires par le même processus réussiront toujours, même si d'autres sessions sont en attente ; ceci est vrai, quel que soit le niveau (session ou transaction) du verrou détenu et des verrous demandés.

Comme tous les verrous dans PostgreSQL, une liste complète des verrous informatifs détenus actuellement par toute session est disponible dans la vue système pg_locks.

Les verrous informatifs et les verrous standards sont stockés dans une partie de la mémoire partagée, dont la taille est définie par les variables de configuration max_locks_per_transaction et max_connections. Attention à ne pas vider cette mémoire, sinon le serveur ne serait plus capable d'accorder des verrous. Ceci impose une limite supérieure au nombre de verrous informatifs que le serveur peut accorder, typiquement entre des dizaines et des centaines de milliers suivant la façon dont le serveur est configuré.

Dans certains cas utilisant cette méthode, tout spécialement les requêtes impliquant un tri explicite et des clauses LIMIT, une grande attention doit être portée au contrôle des verrous acquis, à cause de l'ordre dans lequel les expressions SQL sont évaluées. Par exemple :

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger !
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok
    

Dans les requêtes ci-dessus, la deuxième forme est dangereuse parce qu'il n'est pas garanti que l'application de LIMIT ait lieu avant que la fonction du verrou soit exécutée. Ceci pourrait entraîner l'acquisition de certains verrous que l'application n'attendait pas, donc qu'elle ne pourrait, du coup, pas relâcher (sauf à la fin de la session). Du point de vue de l'application, de tels verrous sont en attente, bien qu'ils soient visibles dans pg_locks.

Les fonctions fournies pour manipuler les verrous informatifs sont décrites dans Section 9.27.10.