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.
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
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.
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).
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.
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 .
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).
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™.
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.
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)
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.
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é.
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.
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
