Comprendre le verrouillage
Le contrôle d’accès concurrentiel multi-version (MVCC) fournit les paramètres d’accès concurrentiel appropriés pour la plupart des scénarios. Toutefois, si une application requiert des verrous spécifiques qui contrôlent exactement quelles lignes sont affectées et avec un niveau de verrou spécifique, les modes de verrouillage explicites activent ce contrôle affiné.
Dans Azure Database pour PostgreSQL, il existe trois types de verrous explicites, de verrous de niveau table, de verrous au niveau des lignes et de verrous au niveau de la page. La transaction initiale demande un verrou et, s’il est accepté, le verrou demandé devient le verrou existant. Si une autre transaction tente de retirer un verrou sur les mêmes données, le verrou est accordé s’il n’est pas en conflit avec la transaction d’origine.
Par exemple, deux transactions peuvent interroger les mêmes données en même temps avec une instruction SELECT. Ces demandes utiliseraient un verrou ACCESS SHARE et ils seraient tous les deux autorisés. Dans un autre scénario, une transaction interroge des données avec une instruction SELECT et un verrou ACCESS SHARE, mais en même temps une autre transaction tente de supprimer la même table. La suppression d’une table nécessite un verrou ACCESS EXCLUSIVE qui ne serait pas accordé dans ce scénario.
Verrous au niveau de la table
Les verrous au niveau de la table acquièrent des verrous sur une table entière, même s'ils incluent "ROW" dans leur nom. Le verrouillage d’une table entière peut être nécessaire si la table elle-même est modifiée ou peut être plus efficace que de retirer de nombreux verrous au niveau des lignes.
Il existe huit types de verrous au niveau de la table dans Azure Database pour PostgreSQL et les commandes SQL qui acquièrent ces types de verrous sont les suivants :
| Mode de verrouillage | Acquis par |
|---|---|
| PARTAGE D'ACCÈS | Commande SELECT |
| PARTAGE DE LIGNES | COMMANDES SELECT FOR UPDATE et SELECT FOR SHARE |
| LIGNE EXCLUSIVE | Commandes UPDATE, DELETE et INSERT |
| MISE À JOUR EXCLUSIVE PARTAGÉE | ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY commandes, certaines commandes ALTER INDEX et ALTER TABLE, et VACUUM (non FULL) |
| PARTAGER | Commande CREATE INDEX (pas SIMULTANÉMENT) |
| PARTAGE EXCLUSIF DE RANGÉE | Commande CREATE TRIGGER et certaines commandes ALTER TABLE |
| EXCLUSIF | COMMANDE ACTUALISER LA VUE MATÉRIALISÉE SIMULTANÉMENT |
| Accès exclusif | DROP TABLE, REINDEX, TRUNCATE, CLUSTER, REFRESH MATERIALIZED VIEW (pas SIMULTANÉMENT), la plupart des commandes ALTER INDEX et ALTER TABLE, et VACUUM FULL |
Chaque type de verrou existant bloque d’autres verrous demandés en cours d’acquisition. Le tableau suivant répertorie les verrous qui empêchent les autres verrous d’être acquis :
| -- | PARTAGE D'ACCÈS existant | PARTAGE DE LIGNES existant | Verrou EXCLUSIF sur LIGNE EXISTANTE | Mise à jour exclusive d'un partage existant | SHARE existant | EXCL DE LIGNE SHARE existante | Exclusif existant | ACCESS EXCLUSIF existant |
|---|---|---|---|---|---|---|---|---|
| PARTAGE D'ACCÈS demandé | Bloqué | |||||||
| PARTAGE DE LIGNES demandé | Bloqué | Bloqué | ||||||
| DEMANDE D'ACCÈS EXCLUSIF ROW | Bloqué | Bloqué | Bloqué | Bloqué | ||||
| Demande de mise à jour exclusive de partage | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |||
| Partage demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |||
| REQUÊTE SHARE ROW EXCLUSIVE | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | ||
| Demandé EXCLUSIF | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |
| Accès exclusif demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué |
Verrous au niveau des lignes
Les verrous au niveau des lignes sont plus granulaires et affectent uniquement une autre transaction qui accède à la même ligne. Ce type de verrou améliore la concurrence, mais l’acquisition et la suppression de nombreux verrous affectent négativement les performances. Les verrous au niveau des lignes sont automatiquement acquis par PostgreSQL et ne sont pas appliqués manuellement.
Il existe quatre types de verrous au niveau des lignes dans Azure Database pour PostgreSQL et ils sont acquis en fonction des autres types de verrous à bloquer :
| -- | VERROU EXISTANT POUR PARTAGE DE CLÉS | EXISTANT POUR PARTAGE | EXISTANT POUR PAS DE MISE À JOUR DE CLÉ | FOR UPDATE existant |
|---|---|---|---|---|
| Demande POUR PARTAGE DE CLÉ | Bloqué | |||
| Demande POUR PARTAGER | Bloqué | Bloqué | ||
| Requête POUR AUCUNE MISE À JOUR DE CLÉ | Bloqué | Bloqué | Bloqué | |
| Demande DE MISE À JOUR | Bloqué | Bloqué | Bloqué | Bloqué |
Verrous au niveau de la page
Les verrous au niveau de la page affectent une page de données, qui se compose généralement de plusieurs lignes. Bien que les processus PostgreSQL utilisent des verrous au niveau de la page, les développeurs d’applications n’ont généralement pas besoin de ce type de verrou.
Application manuelle de verrous et affichage des verrous actuels
Pour appliquer manuellement un verrou au niveau de la table, vous pouvez utiliser la commande LOCK avec le mode de verrouillage requis. La commande LOCK doit se trouver dans une transaction et les verrous sont libérés une fois la transaction terminée. Par exemple:
BEGIN TRANSACTION;
LOCK TABLE humanresources.department IN ROW EXCLUSIVE MODE;
COMMIT;
Pour afficher les verrous actuellement conservés sur la base de données, utilisez pg_locks. Par exemple, pour afficher tous les verrous actuels, utilisez la commande suivante :
SELECT * FROM pg_locks;