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 : les verrous de niveau table, les verrous de niveau ligne et les verrous de niveau page. La transaction initiale demande un verrou et, s’il est accepté, le verrou demandé devient le verrou existant. Si une autre transaction tente d'obtenir un verrou sur les mêmes données, le verrou est accordé s'il n'y a pas de conflit avec la transaction initiale.
Par exemple, deux transactions peuvent interroger les mêmes données en même temps avec une instruction SELECT. Ces requêtes utilisent alors un verrou ACCESS SHARE et les deux sont autorisées. Dans un autre scénario, une transaction interroge des données avec une instruction SELECT et un verrou ACCESS SHARE, mais au même moment, une autre transaction tente de supprimer cette 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 des tables
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 complète peut être nécessaire si la table elle-même est modifiée, ou peut être plus efficace que de retirer de nombreux verrous de niveau ligne.
Il existe huit types de verrou de niveau table dans Azure Database pour PostgreSQL et ce sont des commandes SQL qui acquièrent ces verrous :
| Mode de verrouillage | Acquis par |
|---|---|
| ACCESS SHARE | 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 |
| ACCESS EXCLUSIVE | 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 les 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é | |
| ACCESS EXCLUSIVE demandé | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué | Bloqué |
Verrous de niveau ligne
Les verrous de niveau ligne sont plus précis et affectent uniquement une autre transaction qui accède à la même ligne. Ce type de verrou améliore l’accès concurrentiel, mais l’acquisition et la suppression de nombreux verrous ont un impact négatif sur les performances. Les verrous de niveau ligne sont automatiquement acquis par PostgreSQL et ne sont pas appliqués manuellement.
Il existe quatre types de verrou de niveau ligne dans Azure Database pour PostgreSQL, et ils sont acquis en fonction des autres types de verrous qui doivent être bloqués :
| -- | FOR KEY SHARE existant | FOR SHARE existant | FOR NO KEY UPDATE existant | FOR UPDATE existant |
|---|---|---|---|---|
| FOR KEY SHARE existant | Bloqué | |||
| FOR SHARE existant | Bloqué | Bloqué | ||
| FOR NO KEY UPDATE existant | Bloqué | Bloqué | Bloqué | |
| FOR UPDATE existant | Bloqué | Bloqué | Bloqué | Bloqué |
Verrous de niveau page
Les verrous de niveau 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.
Appliquer manuellement des verrous et afficher les verrous actuels
Pour appliquer manuellement un verrou de niveau table, vous pouvez utiliser la commande LOCK avec le mode de verrouillage nécessaire. 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;