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, dans ce scénario, ne serait pas accordé.
Verrous au niveau de la table
Les verrous de niveau table acquièrent des verrous sur une table entière, même s’ils ont 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 |
| ROW SHARE | COMMANDES « SELECT FOR UPDATE » et « SELECT FOR SHARE » |
| ROW EXCLUSIVE | Commandes UPDATE, DELETE et INSERT |
| SHARE UPDATE EXCLUSIVE | Commandes ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, certaines commandes ALTER INDEX et ALTER TABLE, et VACUUM (pas VACUUM FULL) |
| Partager | Commande CREATE INDEX (pas CREATE INDEX CONCURRENTLY) |
| SHARE ROW EXCLUSIVE | Commande CREATE TRIGGER et certaines commandes ALTER TABLE |
| EXCLUSIVE | Commande REFRESH MATERIALIZED VIEW CONCURRENTLY |
| Accès exclusif | Commandes DROP TABLE, REINDEX, TRUNCATE, CLUSTER, REFRESH MATERIALIZED VIEW (pas CONCURRENTLY), 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 :
| -- | ACCESS SHARE existant | ROW SHARE existant | ROW EXCLUSIVE existant | SHARE UPDATE EXCLUSIVE existant | SHARE existant | SHARE ROW EXCL existant | EXCLUSIVE existant | ACCESS EXCLUSIVE existant |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE demandé | Bloqué | |||||||
| ROW SHARE demandé | Bloqué | Bloqué | ||||||
| ROW EXCLUSIVE demandé | Bloqué | Bloqué | Bloqué | Bloqué | ||||
| SHARE UPDATE EXCLUSIVE demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |||
| SHARE demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |||
| SHARE ROW EXCLUSIVE demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | ||
| EXCLUSIVE demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | |
| Accès exclusif demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué |
Verrous de niveau ligne
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 :
| -- | FOR KEY SHARE existant | FOR SHARE existant | FOR NO KEY UPDATE existant | FOR UPDATE existant |
|---|---|---|---|---|
| Demande POUR PARTAGE DE CLÉ | Bloqué | |||
| FOR SHARE existant | Bloqué | Bloqué | ||
| FOR NO KEY UPDATE existant | 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;