12.3. Verrouillage explicite
PostgreSQL™ fournit de
nombreux modes de verrous pour contrôler les accès concurrents 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,
ALTER TABLE
ne peut pas être
exécuté de façon sûr en même temps que d'autres opérations sur la
même table, donc il obtient un verrou exclusif sur la table pour
s'assurer d'une bonne exécution).
Pour examiner une liste des verrous actuels dans un serveur de base
de données, 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 25,
Surveiller l'activité de la base de données.
12.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. 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 pourraient être détenus en même temps par plusieurs
transactions. Notez, en particulier, que certains modes de verrous
sont en conflit avec eux-même (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ême (par exemple, un verrou ACCESS SHARE peut être détenu par plusieurs
transactions).
Modes de verrous au niveau table
-
ACCESS SHARE
-
En conflit avec le mode verrou ACCESS
EXCLUSIVE.
Les commandes
SELECT
acquièrent un verrou
sur ce mode avec les tables référencées. En général, tout
requête lisant seulement une table et ne la modifiant pas
obtiendra ce mode de verrou.
-
ROW SHARE
-
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
-
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
-
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 concurrentes de schéma et
l'exécution d'un
VACUUM
.
Acquis par
VACUUM
(sans FULL),
ANALYZE
et
CREATE INDEX CONCURRENTLY
.
-
SHARE
-
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 de données concurrentes.
Acquis par
CREATE
INDEX
(sans CONCURRENTLY).
-
SHARE ROW
EXCLUSIVE
-
En conflit avec les modes de verrous ROW
EXCLUSIVE, SHARE UPDATE
EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE et ACCESS
EXCLUSIVE.
Ce mode de verrouillage n'est automatiquement acquis par
aucune commande PostgreSQL™.
-
EXCLUSIVE
-
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.
Ce mode de verrouillage n'est pas automatiquement acquis sur
les tables utilisateur par une commande PostgreSQL™. Néanmoins, c'est
acquis sur certains catalogues systèmes pour certaines
opérations.
-
ACCESS
EXCLUSIVE
-
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 garantie
que le détenteur est la seule transaction à accéder à la
table de quelque façon que ce soit.
Acquis par les commandes
ALTER
TABLE
,
DROP
TABLE
,
TRUNCATE
,
REINDEX
,
CLUSTER
et
VACUUM FULL
. 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]](images/tip.png)
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
que
ROLLBACK
annule
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.
12.3.2. Verrous au niveau ligne
En plus des verrous au niveau table, il existe des verrous au
niveau ligne, qui peuvent être des verrous exclusifs ou partagés.
Un verrou exclusif sur une ligne spécifique est automatiquement
acquis lorsque la ligne est mise à jour ou supprimée. Le verrou est
détenu jusqu'à la fin de la transaction, que ce soit une validation
ou une annulation, de la même façon que les verrous de niveau
table. Les verrous au niveau ligne n'affectent pas les requêtes sur
les données ; ils bloquent seulement les
modifieurs d'une même ligne
.
Pour acquérir un verrou exclusif au niveau ligne sans modifier
réellement la ligne, sélectionnez la ligne avec
SELECT FOR UPDATE
. Notez qu'une
fois le verrou au niveau ligne acquis, la transaction pourrait
mettre à jour la ligne plusieurs fois sans peur des conflits.
Pour acquérir un verrou partagé niveau ligne sur une ligne
spécifique, sélectionnez la ligne avec
SELECT FOR SHARE
. Un verrou
partagé n'empêche pas les autres transactions d'obtenir le même
verrou partagé. Néanmoins, aucune transaction n'est autorisée à
mettre à jour, supprimer ou verrouiller exclusivement une ligne
dont une autre transaction a obtenu un verrou partagé. Toute
tentative de le faire bloquera tant que les verrous partagés
n'auront pas été enlevés.
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, par exemple,
SELECT
FOR UPDATE
modifiera les lignes sélectionnées pour
les marquer verrouillées et cela résultera en des écritures
disques.
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 de 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'application ne
sont normalement pas concernés par les verrous au niveau page mais
nous les mentionnons dans un souci d'exhaustivité.
12.3.3. 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 (quelle est exactement la transaction annulée est
difficile à prévoir mais vous ne devriez pas vous en préoccuper).
Notez que les verrous morts peuvent aussi se produire en résultat à
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.
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 devriez vous assurer que le
premier verrou acquis sur un objet dans une transaction est dans le
plus haut mode qui sera nécessaire pour cet objet. S'il n'est pas
possible de vérifier ceci à l'avance, alors les blocages devront
être gérés à l'exécution en ré-essayant les transactions annulées à
cause de 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 attendra 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.
12.3.4. Verrous informatifs
PostgreSQL™ fournit un moyen
pour créer des verrous qui ont une signification définie par
l'application. Elles 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 verrouiller des
stratégies qui sont un peu étranges pour le modèle MVCC. Une fois
acquis, un verrou informatif est détenu jusqu'à ce qu'il soit
relâché ou que la session se termine. Contrairement aux verrous
standards, les verrous informatifs n'honorent pas de sémantiques de
transactions : un verrou informatif acquis lors d'une transaction
qui sera par la suite annulée sera toujours détenu après
l'annulation, et de la même façon un déverrouillage sera toujours
vrai même si la transaction appelante échoue. Le même verrou peut
être acquis plusieurs fois par le processus qui le détient : à
chaque demande de verrou doit correspondre une demande de
déverrouillage avant que le verrou ne soit réellement supprimé. (Si
une session détient déjà un verrou donné, les demandes suivantes
réussiront même si d'autres sessions attendent le verrou.) Comme
tous les verrous de PostgreSQL™, une liste complète des
verrous informatifs détenus actuellement par une session est
visible dans la vue système pg_locks.
Les verrous informatifs sont alloués à partir d'une portion de
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 sera
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é.
Une utilisation commune des verrous informatifs est d'émuler des
stratégies de verrou pessimiste, typiques des systèmes de gestion
de données sur « fichier
plat ». Alors qu'une option stocké dans une table
pourrait être utilisée dans le même but, les verrous informatifs
sont plus rapides, éviter le grossissement de MVCC et sont
automatiquement nettoyés par le serveur à la fin d'une session.
Dans certains cas qui utilisent 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
que l'application de LIMIT n'est pas
garantie avant que la fonction du verrou soit exécutée. Ceci
pourrait être la cause de l'acquisition de certains verrous que
l'application n'attendait pas et, du coup, qu'elle ne pourrait 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écrits dans Tableau 9.50,
« Fonctions des verrous informatifs ».
Isolation des transactions
