🔝 Retour au Sommaire
Imaginez une bibliothèque où plusieurs personnes veulent emprunter, retourner et consulter le même livre simultanément. Sans règles, le chaos s'installe : deux personnes prennent le même exemplaire en même temps, quelqu'un modifie la fiche de prêt pendant qu'un autre la lit, etc.
Les verrous (locks) sont le mécanisme que PostgreSQL utilise pour coordonner l'accès concurrent aux données. Ils garantissent que les opérations se déroulent de manière ordonnée et cohérente, même lorsque des centaines de transactions travaillent simultanément.
Analogie centrale : Les verrous sont comme les règles de circulation sur une route :
- 🚦 Feu rouge : Stop, attendez (verrou exclusif)
- 🚦 Feu vert : Passez (pas de verrou)
- 🚦 Feu orange : Certains peuvent passer, d'autres attendent (verrous partagés)
Nous avons vu que PostgreSQL utilise MVCC (Multiversion Concurrency Control) pour gérer la concurrence. Mais MVCC ne suffit pas dans tous les cas :
MVCC gère :
- ✅ Les lectures concurrentes (multiples versions)
- ✅ Les lectures pendant les écritures (snapshots)
- ✅ La visibilité des données
Les verrous gèrent :
- 🔒 Les écritures concurrentes sur la même ligne
- 🔒 Les modifications de structure (DDL)
- 🔒 La coordination entre transactions qui modifient les mêmes données
Exemple :
-- Transaction A
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
-- PostgreSQL pose automatiquement un VERROU sur la ligne id=1
-- Transaction B (en parallèle)
BEGIN;
UPDATE comptes SET solde = solde + 50 WHERE id = 1;
-- Doit ATTENDRE que Transaction A libère le verrou
-- Sinon, les deux modifications pourraient s'écraser (Lost Update)Sans verrou, les deux UPDATE pourraient se produire simultanément et créer une incohérence.
PostgreSQL utilise un système de verrous hiérarchique avec plusieurs niveaux de granularité :
Appliqués sur une table entière. Plusieurs modes existent.
Appliqués sur des lignes individuelles. Plus fins, permettent plus de concurrence.
Verrous de très courte durée, posés et relâchés par PostgreSQL en interne lors de la lecture/écriture d'une page de buffer partagé. Non manipulables par l'utilisateur — vous n'avez ni à les comprendre ni à les configurer.
PostgreSQL pose un verrou exclusif sur son propre XID pendant toute la durée de la transaction. Ce verrou n'est jamais en conflit, mais il sert de mécanisme d'attente : quand T2 attend que T1 libère un verrou de ligne, T2 attend en réalité ce « verrou XID » de T1, qui sera libéré au COMMIT/ROLLBACK de T1. C'est pour cela qu'on voit ShareLock on transaction NNN dans les messages de deadlock.
Verrous applicatifs personnalisés que vous pouvez créer. Ignorés par PostgreSQL au niveau intégrité des données : c'est à votre application de les respecter (voir chapitre 12.6).
PostgreSQL définit 8 modes de verrous de table, de moins restrictif à plus restrictif :
| Mode | Nom complet | Bloque | Usage typique |
|---|---|---|---|
| ACCESS SHARE | Lecture simple | ACCESS EXCLUSIVE | SELECT |
| ROW SHARE | Lecture avec intention de mise à jour | EXCLUSIVE, ACCESS EXCLUSIVE | SELECT FOR UPDATE |
| ROW EXCLUSIVE | Modification de lignes | SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | INSERT, UPDATE, DELETE |
| SHARE UPDATE EXCLUSIVE | Modification non-concurrente | Lui-même, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | VACUUM, CREATE INDEX CONCURRENTLY |
| SHARE | Lecture partagée protégée | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | CREATE INDEX |
| SHARE ROW EXCLUSIVE | Lecture partagée exclusive | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | Rarement utilisé |
| EXCLUSIVE | Exclusion des écritures | ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | REFRESH MATERIALIZED VIEW CONCURRENTLY |
| ACCESS EXCLUSIVE | Exclusion totale | TOUS | DROP TABLE, TRUNCATE, VACUUM FULL, REFRESH MATERIALIZED VIEW (sans CONCURRENTLY), ALTER TABLE (certains cas) |
Deux transactions peuvent tenir des verrous sur la même table si ces verrous sont compatibles.
Règle simple :
- ✅ ACCESS SHARE (SELECT) est compatible avec presque tout
- ✅ Plusieurs ROW EXCLUSIVE (UPDATE) sont compatibles entre eux
- ❌ ACCESS EXCLUSIVE est incompatible avec tout
Exemple de compatibilité :
-- Transaction A
BEGIN;
SELECT * FROM produits; -- Pose un verrou ACCESS SHARE
-- N'empêche pas les autres de lire ou modifier
-- Transaction B (en parallèle, compatible)
BEGIN;
UPDATE produits SET prix = 100 WHERE id = 1; -- Pose un verrou ROW EXCLUSIVE
-- Fonctionne ! Les deux verrous sont compatibles
-- Transaction C (en parallèle, compatible aussi)
BEGIN;
SELECT * FROM produits; -- Pose aussi un ACCESS SHARE
-- Fonctionne !Exemple d'incompatibilité :
-- Transaction A
BEGIN;
TRUNCATE TABLE produits; -- Pose un verrou ACCESS EXCLUSIVE
-- Bloque TOUT accès à la table !
-- Transaction B (en parallèle)
BEGIN;
SELECT * FROM produits; -- Pose un ACCESS SHARE
-- BLOQUÉE ! Doit attendre que A termineLes verrous de ligne sont plus granulaires : ils ne bloquent qu'une ligne spécifique, pas toute la table.
| Mode | Commande | Bloque | Permet |
|---|---|---|---|
| FOR UPDATE | SELECT ... FOR UPDATE | Autres FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE | Lectures simples |
| FOR NO KEY UPDATE | SELECT ... FOR NO KEY UPDATE | FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE | FOR KEY SHARE, lectures |
| FOR SHARE | SELECT ... FOR SHARE | FOR UPDATE, FOR NO KEY UPDATE | FOR SHARE, FOR KEY SHARE, lectures |
| FOR KEY SHARE | SELECT ... FOR KEY SHARE | FOR UPDATE | FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE, lectures |
BEGIN;
SELECT * FROM commandes WHERE id = 123 FOR UPDATE;
-- Verrouille la ligne id=123 en mode EXCLUSIF
-- Autres transactions ne peuvent ni la modifier, ni la verrouiller
-- Traitement...
UPDATE commandes SET statut = 'Traité' WHERE id = 123;
COMMIT; -- Libère le verrouUsage typique : Empêcher les modifications concurrentes pendant un traitement critique.
Exemple concret : Réservation de place
-- Transaction A (Alice réserve)
BEGIN;
-- Verrouiller la place pour vérifier et modifier
SELECT disponible FROM places WHERE id = 42 FOR UPDATE;
-- Si disponible = true
UPDATE places SET disponible = false, client_id = 'Alice' WHERE id = 42;
COMMIT;
-- Transaction B (Bob essaie en parallèle)
BEGIN;
SELECT disponible FROM places WHERE id = 42 FOR UPDATE;
-- BLOQUÉ ! Doit attendre qu'Alice termine
-- Quand Alice commit, Bob obtient enfin le verrou
-- Mais maintenant disponible = false, donc Bob ne peut pas réserver
COMMIT;BEGIN;
SELECT * FROM produits WHERE id = 1 FOR SHARE;
-- Verrouille la ligne en mode PARTAGÉ
-- Autres peuvent aussi lire (FOR SHARE)
-- Mais personne ne peut modifier (FOR UPDATE/UPDATE bloqués)
-- Lecture garantie stable
-- ...
COMMIT;Usage typique : Garantir qu'une ligne ne changera pas pendant votre traitement, tout en permettant aux autres de la lire.
Permet de mettre à jour des colonnes non-clés (pas la clé primaire) sans bloquer complètement les références de clés étrangères.
BEGIN;
SELECT * FROM produits WHERE id = 1 FOR NO KEY UPDATE;
-- Verrouille pour mise à jour, mais les FK peuvent toujours référencer
UPDATE produits SET description = 'Nouveau texte' WHERE id = 1;
-- OK car 'description' n'est pas une clé
COMMIT;Le moins restrictif. Empêche uniquement les modifications de clé primaire.
BEGIN;
SELECT * FROM produits WHERE id = 1 FOR KEY SHARE;
-- Verrou très faible
-- Permet même des UPDATE de colonnes non-clés
COMMIT;Restrictivité croissante :
FOR KEY SHARE < FOR SHARE < FOR NO KEY UPDATE < FOR UPDATE
Lectures simples (SELECT)
↓
FOR KEY SHARE (empêche UPDATE de PK)
↓
FOR SHARE (empêche tout UPDATE)
↓
FOR NO KEY UPDATE (empêche UPDATE de PK et verrous exclusifs)
↓
FOR UPDATE (empêche tout verrouillage et modification)
PostgreSQL pose automatiquement des verrous lors des opérations courantes :
-- SELECT : Pose automatiquement ACCESS SHARE sur la table
SELECT * FROM produits;
-- UPDATE : Pose automatiquement ROW EXCLUSIVE sur la table + verrou de ligne
UPDATE produits SET prix = 100 WHERE id = 1;
-- DELETE : Pareil que UPDATE
DELETE FROM produits WHERE id = 1;
-- INSERT : Pose ROW EXCLUSIVE sur la table
INSERT INTO produits (nom, prix) VALUES ('Nouveau', 50);
-- ALTER TABLE : Pose ACCESS EXCLUSIVE
ALTER TABLE produits ADD COLUMN description TEXT;Vous pouvez demander des verrous manuellement :
BEGIN;
-- Verrouiller explicitement une table
LOCK TABLE produits IN ACCESS EXCLUSIVE MODE;
-- Personne d'autre ne peut accéder à la table
-- Faire vos opérations
UPDATE produits SET prix = prix * 1.1;
COMMIT;Modes disponibles :
LOCK TABLE ma_table IN ACCESS SHARE MODE;
LOCK TABLE ma_table IN ROW SHARE MODE;
LOCK TABLE ma_table IN ROW EXCLUSIVE MODE;
LOCK TABLE ma_table IN SHARE UPDATE EXCLUSIVE MODE;
LOCK TABLE ma_table IN SHARE MODE;
LOCK TABLE ma_table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE ma_table IN EXCLUSIVE MODE;
LOCK TABLE ma_table IN ACCESS EXCLUSIVE MODE; Quand utiliser LOCK TABLE :
- ✅ Opérations en batch nécessitant un accès exclusif
- ✅ Éviter les deadlocks dans des scénarios complexes
⚠️ Rarement nécessaire (PostgreSQL gère bien automatiquement)
BEGIN;
-- Verrouiller des lignes spécifiques
SELECT * FROM commandes WHERE client_id = 42 FOR UPDATE;
-- Verrouille toutes les commandes du client 42
-- Faire vos modifications
UPDATE commandes SET statut = 'En cours' WHERE client_id = 42;
COMMIT;Un deadlock (interblocage) se produit lorsque deux (ou plus) transactions s'attendent mutuellement pour des verrous, créant une situation de blocage circulaire où aucune ne peut progresser.
Analogie : Deux voitures arrivent face à face dans une rue étroite à sens unique. Chacune attend que l'autre recule. Personne ne peut avancer : c'est un interblocage.
Contexte : Deux comptes bancaires (A et B).
Transaction 1 :
BEGIN;
-- Verrouiller le compte A
UPDATE comptes SET solde = solde - 100 WHERE id = 'A';
-- ✅ Obtient le verrou sur la ligne A
-- [Petit délai]Transaction 2 (en parallèle) :
BEGIN;
-- Verrouiller le compte B
UPDATE comptes SET solde = solde - 50 WHERE id = 'B';
-- ✅ Obtient le verrou sur la ligne B
-- [Petit délai]Transaction 1 (suite) :
-- Essayer de verrouiller le compte B
UPDATE comptes SET solde = solde + 100 WHERE id = 'B';
-- ⏳ BLOQUÉ ! Transaction 2 détient le verrou sur B
-- Attend que Transaction 2 libère...Transaction 2 (suite) :
-- Essayer de verrouiller le compte A
UPDATE comptes SET solde = solde + 50 WHERE id = 'A';
-- ⏳ BLOQUÉ ! Transaction 1 détient le verrou sur A
-- Attend que Transaction 1 libère...Situation finale : DEADLOCK ! 🔴
Transaction 1 : détient A, attend B
Transaction 2 : détient B, attend A
┌───────────────┐
│ Transaction 1 │
│ détient A │
│ attend B │
└───────┬───────┘
│
↓
┌───────────────┐
│ Ligne B │
│ (détenue par │
│ Transaction 2)│
└───────┬───────┘
│
↓
┌───────────────┐
│ Transaction 2 │
│ détient B │
│ attend A │
└───────┬───────┘
│
↓
┌───────────────┐
│ Ligne A │
│ (détenue par │
│ Transaction 1)│
└───────────────┘
│
│ Cycle !
└─────────┐
│
↓
♾️ DEADLOCK
PostgreSQL n'a pas de tâche périodique qui scrute en permanence — la détection est déclenchée à la demande :
- Une transaction attend un verrou (par exemple, T2 attend que T1 libère une ligne).
- Si l'attente dépasse
deadlock_timeout(défaut : 1 seconde), PostgreSQL construit le graphe des attentes dans le lock manager. - Recherche d'un cycle : la transaction qui attend depuis trop longtemps est-elle dans une attente circulaire ?
- Si un cycle est trouvé → DEADLOCK détecté. La transaction qui a déclenché la détection (celle qui attendait depuis >
deadlock_timeout) est généralement choisie comme victime : elle reçoit l'erreurdeadlock_detected(SQLSTATE40P01) et doit faireROLLBACK. Les autres transactions peuvent alors progresser. - Si aucun cycle → la transaction continue d'attendre normalement.
Message d'erreur :
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 67889; blocked by process 12345.
HINT: See server log for query details.
La transaction annulée reçoit cette erreur et doit faire un ROLLBACK.
Quatre paramètres jouent ensemble pour contrôler la concurrence et éviter les transactions zombies :
-- Dans postgresql.conf ou via SET au niveau session/transaction
-- Temps d'attente avant de lancer la DÉTECTION de deadlock (défaut : 1s)
-- deadlock_timeout = 1s ← ligne de postgresql.conf (en session : SET deadlock_timeout = '1s';)
-- Temps maximum d'attente pour OBTENIR un verrou avant erreur (0 = infini, défaut = 0)
SET lock_timeout = '30s';
-- Temps maximum d'EXÉCUTION d'une instruction (0 = infini, défaut = 0)
SET statement_timeout = '60s';
-- Tuer automatiquement les sessions "idle in transaction" trop longues
-- (défaut = 0 = jamais). 5 minutes est une valeur saine en production.
SET idle_in_transaction_session_timeout = '5min';💡
idle_in_transaction_session_timeoutest un garde-fou crucial : une transaction qui reste ouverte sans rien faire (par exemple, un client qui a planté entreBEGINetCOMMIT) continue de détenir ses verrous et bloque VACUUM sur les versions mortes plus récentes. Le timeout coupe automatiquement la session et émet unROLLBACK. Plus strict :idle_session_timeout(PG 14+) coupe aussi les sessions idle hors transaction.
Le cas classique vu précédemment : T1 attend T2, T2 attend T1.
T1 détient A, attend B
T2 détient B, attend C
T3 détient C, attend A
Cycle : T1 → T2 → T3 → T1
Exemple :
-- Transaction 1
BEGIN;
UPDATE t1 SET ... WHERE id = 1; -- Verrouille t1 ligne 1
-- [attend]
UPDATE t2 SET ... WHERE id = 1; -- Veut t2 ligne 1 (détenue par T2)
-- Transaction 2
BEGIN;
UPDATE t2 SET ... WHERE id = 1; -- Verrouille t2 ligne 1
-- [attend]
UPDATE t3 SET ... WHERE id = 1; -- Veut t3 ligne 1 (détenue par T3)
-- Transaction 3
BEGIN;
UPDATE t3 SET ... WHERE id = 1; -- Verrouille t3 ligne 1
-- [attend]
UPDATE t1 SET ... WHERE id = 1; -- Veut t1 ligne 1 (détenue par T1)
-- DEADLOCK !-- Transaction 1
BEGIN;
UPDATE produits SET prix = 100 WHERE id = 1; -- Verrou ligne
-- [attend]
ALTER TABLE produits ADD COLUMN description TEXT; -- Veut verrou table (T2 l'a)
-- Transaction 2
BEGIN;
ALTER TABLE produits ADD COLUMN stock INT; -- Verrou table (ACCESS EXCLUSIVE)
-- [attend]
UPDATE produits SET stock = 10 WHERE id = 1; -- Veut verrou ligne (T1 l'a)
-- DEADLOCK !-- Transaction 1
BEGIN;
UPDATE commandes SET montant = 500 WHERE id = 100; -- Verrouille commande 100
-- [attend]
INSERT INTO lignes_commande (commande_id, produit_id)
VALUES (200, 1); -- Veut verrouiller commande 200 (FK, détenue par T2)
-- Transaction 2
BEGIN;
UPDATE commandes SET montant = 300 WHERE id = 200; -- Verrouille commande 200
-- [attend]
INSERT INTO lignes_commande (commande_id, produit_id)
VALUES (100, 2); -- Veut verrouiller commande 100 (FK, détenue par T1)
-- DEADLOCK !Règle d'or : Toujours accéder aux tables/lignes dans le même ordre dans toutes les transactions.
❌ Mauvais exemple (ordre différent) :
-- Transaction A
UPDATE comptes SET ... WHERE id = 'A'; -- A puis B
UPDATE comptes SET ... WHERE id = 'B';
-- Transaction B
UPDATE comptes SET ... WHERE id = 'B'; -- B puis A (ordre inversé!)
UPDATE comptes SET ... WHERE id = 'A';
-- Risque de deadlock !✅ Bon exemple (ordre identique) :
-- Transaction A
UPDATE comptes SET ... WHERE id = 'A'; -- A puis B
UPDATE comptes SET ... WHERE id = 'B';
-- Transaction B
UPDATE comptes SET ... WHERE id = 'A'; -- A puis B (même ordre)
UPDATE comptes SET ... WHERE id = 'B';
-- Pas de deadlock possible !Astuce : Trier les IDs par ordre croissant
-- Si vous devez modifier plusieurs comptes
SELECT * FROM comptes WHERE id IN ('B', 'A', 'C') ORDER BY id FOR UPDATE;
-- Résultat ordonné : A, B, C (toujours le même ordre)Plus une transaction est courte, moins elle a de chances de rentrer en conflit avec d'autres.
-- ❌ MAUVAIS : Transaction longue
BEGIN;
UPDATE comptes SET ... WHERE id = 1;
-- [Traitement long dans l'application : 30 secondes]
-- [Appels API externes]
-- [Calculs complexes]
UPDATE autre_table SET ...;
COMMIT;
-- ✅ BON : Transaction courte
-- [Faire tous les calculs AVANT]
BEGIN;
UPDATE comptes SET ... WHERE id = 1;
UPDATE autre_table SET ...;
COMMIT; -- Rapide ! -- Au niveau de la session
SET lock_timeout = '10s';
SET statement_timeout = '30s';
BEGIN;
-- Si un verrou n'est pas obtenu en 10s → erreur
-- Si la requête prend plus de 30s → erreur
UPDATE ...;
COMMIT; Pour des opérations complexes, verrouillez les tables au début dans le bon ordre :
BEGIN;
-- Verrouiller toutes les tables nécessaires immédiatement
LOCK TABLE comptes IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE transactions IN SHARE ROW EXCLUSIVE MODE;
-- Faire toutes les opérations
UPDATE comptes ...;
INSERT INTO transactions ...;
COMMIT;Au lieu d'attendre indéfiniment qu'un verrou se libère, deux options permettent de réagir immédiatement :
BEGIN;
SELECT * FROM produits WHERE id = 1 FOR UPDATE NOWAIT;
-- Si la ligne est déjà verrouillée → erreur immédiate (SQLSTATE 55P03,
-- nom symbolique : lock_not_available)
-- Pas d'attente → pas de risque de deadlock
COMMIT;La gestion de l'erreur doit être faite côté client (try/except, etc.) ou dans un bloc PL/pgSQL :
DO $$
BEGIN
PERFORM 1 FROM produits WHERE id = 1 FOR UPDATE NOWAIT;
-- … traitement …
EXCEPTION
WHEN lock_not_available THEN
RAISE NOTICE 'Ligne déjà verrouillée, on réessaiera plus tard';
END $$;BEGIN;
SELECT * FROM produits WHERE id = 1 FOR UPDATE SKIP LOCKED;
-- Renvoie 0 ligne si la ligne id=1 est verrouillée par une autre transaction
-- Pas d'erreur, pas d'attente → idéal pour des queues de travail
-- où chaque worker prend un job différent
COMMIT;💡 Différence clé :
NOWAITlève une erreur si le verrou est indisponible ;SKIP LOCKEDignore silencieusement les lignes verrouillées et renvoie ce qu'il a pu verrouiller. Aucun des deux n'est un timeout à proprement parler — pour borner l'attente d'un verrou, utilisez plutôt le paramètrelock_timeout.
-- ❌ MAUVAIS : Verrouiller ligne par ligne depuis l'applicatif (pseudo-code)
-- for each_id in select_ids:
-- BEGIN; UPDATE comptes SET ... WHERE id = each_id; COMMIT;
-- → N transactions, N rondes réseau, contention répétée
-- ✅ BON : Une seule opération SQL en lot
BEGIN;
UPDATE comptes
SET solde = solde * 1.01
WHERE statut = 'actif';
COMMIT;Une seule instruction UPDATE prend ses verrous, les libère ensemble au COMMIT, et évite N allers-retours réseau.
-- ❌ TRÈS MAUVAIS : Transaction ouverte pendant une interaction utilisateur
BEGIN;
UPDATE comptes SET statut = 'En modification' WHERE id = 1;
-- [Attente de validation utilisateur : peut prendre des minutes !]
-- [Pendant ce temps, le verrou est détenu]
UPDATE comptes SET solde = nouveau_solde WHERE id = 1;
COMMIT;
-- ✅ BON : Pas de transaction pendant l'interaction
-- 1. Lire les données
SELECT * FROM comptes WHERE id = 1;
-- 2. [Interaction utilisateur]
-- 3. Transaction courte pour écrire
BEGIN;
UPDATE comptes SET solde = nouveau_solde WHERE id = 1;
COMMIT; PostgreSQL enregistre les deadlocks dans ses logs si configuré :
# Dans postgresql.conf
log_lock_waits = on # Logger les attentes de verrous longues
deadlock_timeout = 1s # Temps avant de vérifier les deadlocks Exemple de log :
2024-01-15 10:30:45.123 UTC [12345] ERROR: deadlock detected
2024-01-15 10:30:45.123 UTC [12345] DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 67889; blocked by process 12345.
Process 12345: UPDATE comptes SET solde = solde - 100 WHERE id = 'B';
Process 12346: UPDATE comptes SET solde = solde + 50 WHERE id = 'A';
2024-01-15 10:30:45.123 UTC [12345] HINT: See server log for query details.
2024-01-15 10:30:45.123 UTC [12345] CONTEXT: while updating tuple (0,42) in relation "comptes"
-- Vue pg_locks : Tous les verrous actifs
SELECT
pid,
locktype,
relation::regclass AS table_name,
mode,
granted
FROM pg_locks
WHERE NOT granted; -- Verrous non accordés (en attente) -- Requête complète pour voir les blocages
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query,
blocked_activity.application_name AS blocked_app
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted; Disponible depuis PG 9.6, pg_blocking_pids(pid) retourne directement la liste des PID bloquants. Préférez-la systématiquement à la requête lourde sur pg_locks ci-dessus :
-- Quels PID bloquent une session spécifique ?
SELECT pg_blocking_pids(12345);
-- Résultat : un tableau d'entiers, p. ex. {12346, 12347}Requête de diagnostic recommandée — toutes les sessions actuellement bloquées et qui les bloque :
SELECT
a.pid AS blocked_pid,
a.usename AS blocked_user,
a.query AS blocked_query,
b.pid AS blocking_pid,
b.usename AS blocking_user,
b.query AS blocking_query,
a.wait_event_type,
a.wait_event,
age(now(), a.state_change) AS waited_for
FROM pg_stat_activity a
JOIN pg_stat_activity b
ON b.pid = ANY (pg_blocking_pids(a.pid))
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;C'est l'équivalent moderne, lisible, et qui inclut aussi les soft blocks (sessions ahead in the wait queue) — ce que la requête manuelle sur pg_locks ne capture pas toujours bien.
Si une transaction bloque tout et ne répond plus :
-- Terminer gentiment (laisse la transaction faire ROLLBACK)
SELECT pg_cancel_backend(12346);
-- Tuer brutalement (si pg_cancel_backend ne fonctionne pas)
SELECT pg_terminate_backend(12346);pg_terminate_backend ferme la connexion brutalement. À utiliser en dernier recours.
Les advisory locks sont des verrous que vous créez manuellement pour coordonner des opérations dans votre application. PostgreSQL ne les utilise pas automatiquement : c'est à vous de les gérer.
- ✅ Coordonner des tâches distribuées (jobs, workers)
- ✅ Implémenter des mutex au niveau base de données
- ✅ Empêcher l'exécution simultanée d'une opération critique
- ✅ Créer des sémaphores ou des compteurs partagés
Actifs pendant toute la session (connexion).
-- Obtenir un verrou exclusif
SELECT pg_advisory_lock(123);
-- Si le verrou est déjà pris, ATTEND qu'il soit libéré
-- [Faire des opérations critiques]
-- Libérer le verrou
SELECT pg_advisory_unlock(123);Variante non-bloquante :
-- Essayer d'obtenir le verrou sans attendre
SELECT pg_try_advisory_lock(123);
-- Retourne : true (succès) ou false (déjà pris)Côté applicatif, on teste la valeur retournée pour décider d'enchaîner ou non :
# Pseudo-code (psycopg / asyncpg, etc.)
got_it = cur.execute("SELECT pg_try_advisory_lock(123)").fetchone()[0]
if got_it:
# … opérations critiques …
cur.execute("SELECT pg_advisory_unlock(123)")
else:
# Verrou déjà pris : abandon, retry plus tard, autre stratégie
...Ou en PL/pgSQL si vous voulez tout faire côté serveur :
DO $$
BEGIN
IF pg_try_advisory_lock(123) THEN
-- … opérations critiques …
PERFORM pg_advisory_unlock(123);
ELSE
RAISE NOTICE 'Verrou déjà pris, on passe';
END IF;
END $$;Libérés automatiquement à la fin de la transaction.
BEGIN;
-- Obtenir un verrou transactionnel
SELECT pg_advisory_xact_lock(456);
-- Sera libéré automatiquement au COMMIT ou ROLLBACK
-- [Opérations]
COMMIT; -- Le verrou est libéré automatiquementPostgreSQL permet d'utiliser deux entiers de 32 bits au lieu d'un seul de 64 bits :
-- Verrou basé sur (table_id, row_id)
SELECT pg_advisory_lock(42, 100);
-- Équivalent à un verrou sur le "tuple" (42, 100)
-- Libération
SELECT pg_advisory_unlock(42, 100);Empêcher deux workers de traiter le même job :
-- Worker 1 essaie de prendre un job
BEGIN;
-- Essayer d'obtenir le verrou sur le job 789
SELECT pg_try_advisory_xact_lock(789);
-- Retourne : true
-- Si true, traiter le job
UPDATE jobs SET status = 'En cours' WHERE id = 789;
-- [Traitement du job]
UPDATE jobs SET status = 'Terminé' WHERE id = 789;
COMMIT; -- Verrou libéré automatiquement
-- Worker 2 essaie en parallèle
BEGIN;
SELECT pg_try_advisory_xact_lock(789);
-- Retourne : false (déjà pris par Worker 1)
-- Passer au job suivant
COMMIT;-- Verrou EXCLUSIF (par défaut)
SELECT pg_advisory_lock(123);
-- Personne d'autre ne peut obtenir ce verrou
-- Verrou PARTAGÉ
SELECT pg_advisory_lock_shared(123);
-- Plusieurs processus peuvent obtenir le verrou partagé
-- Mais un verrou exclusif est bloquéSELECT
locktype,
classid,
objid,
pid,
mode,
granted
FROM pg_locks
WHERE locktype = 'advisory'; Connaissez quel type de verrou pose chaque opération :
SELECT → ACCESS SHARE (lecture)
UPDATE/DELETE → ROW EXCLUSIVE (table) + verrou ligne
INSERT → ROW EXCLUSIVE (table)
CREATE INDEX → SHARE
TRUNCATE/DROP → ACCESS EXCLUSIVE
ALTER TABLE → Varie selon l'opération
-- ❌ DANGEREUX en production active
ALTER TABLE produits ADD COLUMN description TEXT;
-- Pose un verrou ACCESS EXCLUSIVE → bloque TOUT
-- ✅ MEILLEUR : Pendant une fenêtre de maintenance
-- ou utiliser CREATE INDEX CONCURRENTLY (pour les index)-- ❌ Bloque les écritures
CREATE INDEX idx_produits_nom ON produits(nom);
-- ✅ N'empêche pas les écritures (plus lent, mais non-bloquant)
CREATE INDEX CONCURRENTLY idx_produits_nom ON produits(nom);-- Trouver les verrous qui durent longtemps
SELECT
pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as query_text,
age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';Surveillez :
- Nombre de transactions en attente de verrous
- Fréquence des deadlocks
- Durée des verrous
- Transactions en idle in transaction
-- Transactions en "idle in transaction" (danger !)
SELECT
pid,
usename,
state,
query,
age(clock_timestamp(), state_change) AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND age(clock_timestamp(), state_change) > interval '5 minutes';import time
import random
import psycopg2
def execute_with_deadlock_retry(conn, operation, max_retries=3):
"""
Retry sur les erreurs transitoires de la classe SQLSTATE 40 :
serialization_failure (40001) ET deadlock_detected (40P01).
`operation(conn)` doit être idempotente.
"""
for attempt in range(max_retries):
try:
operation(conn)
conn.commit()
return True
except psycopg2.extensions.TransactionRollbackError as e:
conn.rollback()
if attempt == max_retries - 1:
raise Exception(
f"Échec après {max_retries} tentatives ({e.pgcode})"
) from e
# Backoff exponentiel + jitter, plafonné à 5 s
wait_time = min(
5.0,
(0.1 * (2 ** attempt)) * (1 + random.uniform(0, 0.3))
)
time.sleep(wait_time)
except Exception:
conn.rollback()
raise
# Utilisation
def mon_transfert(conn):
cur = conn.cursor()
# Accès dans un ordre cohérent (A puis B) pour limiter les deadlocks
cur.execute("UPDATE comptes SET solde = solde - 100 WHERE id = 'A'")
cur.execute("UPDATE comptes SET solde = solde + 100 WHERE id = 'B'")
execute_with_deadlock_retry(conn, mon_transfert)Problème : Deux clients achètent le dernier article simultanément.
Solution 1 — SQL pur : un seul UPDATE conditionnel atomique. On vérifie côté application le nombre de lignes affectées.
BEGIN;
UPDATE produits
SET stock = stock - 2 -- 2 = quantité demandée
WHERE id = 123
AND stock >= 2;
-- Si l'UPDATE renvoie 0 ligne → stock insuffisant → ROLLBACK côté appli
INSERT INTO commandes (produit_id, quantite) VALUES (123, 2);
COMMIT;Solution 2 — PL/pgSQL avec verrouillage explicite et exception serveur :
DO $$
DECLARE
stock_actuel INT;
quantite_demandee INT := 2;
BEGIN
-- Verrouiller la ligne pour vérifier et décrémenter sans interférence
SELECT stock INTO stock_actuel
FROM produits
WHERE id = 123
FOR UPDATE;
IF stock_actuel >= quantite_demandee THEN
UPDATE produits SET stock = stock - quantite_demandee WHERE id = 123;
INSERT INTO commandes (produit_id, quantite) VALUES (123, quantite_demandee);
ELSE
RAISE EXCEPTION 'Stock insuffisant (% disponibles, % demandés)',
stock_actuel, quantite_demandee;
END IF;
END $$;💡 La solution 1 est plus performante (un seul UPDATE) et suffit dans 95% des cas. La solution 2 illustre
FOR UPDATEquand on a besoin d'une vraie logique de branchement côté serveur.
Problème : Incrémenter un compteur depuis plusieurs workers.
❌ Mauvaise approche (Lost Update) :
-- Worker 1
SELECT compteur FROM stats WHERE id = 1; -- Lit : 100
-- [Calcul : 100 + 1]
UPDATE stats SET compteur = 101 WHERE id = 1;
-- Worker 2 (en parallèle)
SELECT compteur FROM stats WHERE id = 1; -- Lit : 100 aussi !
-- [Calcul : 100 + 1]
UPDATE stats SET compteur = 101 WHERE id = 1; -- Écrase !
-- Résultat final : 101 au lieu de 102✅ Bonne approche (Atomique) :
-- Les deux workers
UPDATE stats SET compteur = compteur + 1 WHERE id = 1;
-- PostgreSQL gère l'atomicité
-- Résultat correct : 102Problème : Plusieurs workers doivent traiter des jobs sans conflit.
Solution avec SKIP LOCKED : chaque worker prend un job différent sans s'attendre.
-- Chaque worker exécute ceci, idéalement en une seule requête combinant
-- la sélection et le marquage du job pour éviter une seconde requête
BEGIN;
UPDATE jobs
SET status = 'En cours', started_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'En attente'
ORDER BY priority DESC, created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING id;
-- Si rien n'est renvoyé → aucun job dispo, le worker peut dormir un peu
COMMIT;
-- Puis le worker traite le job, et au bout du traitement :
UPDATE jobs SET status = 'Terminé', finished_at = now() WHERE id = :job_id;💡 Le pattern
UPDATE … WHERE id = (SELECT … FOR UPDATE SKIP LOCKED)est l'idiome de référence pour une file de jobs PostgreSQL : pas de doublon, pas de contention entre workers, et tout tient en une seule requête.
| Type | Niveau | Usage | Obtention |
|---|---|---|---|
| ACCESS SHARE | Table | SELECT | Automatique |
| ROW EXCLUSIVE | Table | INSERT/UPDATE/DELETE | Automatique |
| ACCESS EXCLUSIVE | Table | DDL (DROP, TRUNCATE) | Automatique |
| FOR UPDATE | Ligne | Modification exclusive | Explicite (SELECT FOR UPDATE) |
| FOR SHARE | Ligne | Lecture protégée | Explicite (SELECT FOR SHARE) |
| Advisory Lock | Applicatif | Coordination custom | Explicite (pg_advisory_lock) |
- ✅ Accéder aux ressources dans un ordre cohérent (trier les IDs)
- ✅ Garder les transactions courtes
- ✅ Éviter les transactions interactives
- ✅ Utiliser NOWAIT ou SKIP LOCKED quand approprié
- ✅ Configurer des timeouts (lock_timeout, statement_timeout)
- ✅ Implémenter une logique de retry avec backoff exponentiel
- ✅ Monitorer les deadlocks dans les logs
- ✅ Préférer les opérations atomiques SQL au Read-Modify-Write
- ✅ Éviter les DDL pendant les heures de pointe
- 🚨 Transactions en "idle in transaction" pour longtemps
- 🚨 Nombreux processus en attente de verrous
- 🚨 Deadlocks fréquents dans les logs
- 🚨 Lock timeout dépassé régulièrement
- 🚨 Requêtes bloquées pendant plusieurs minutes
- 🚨 Opérations DDL en production active
Les verrous sont un mécanisme essentiel de PostgreSQL pour coordonner l'accès concurrent aux données. Bien comprendre leur fonctionnement vous permet de :
- ✅ Concevoir des transactions robustes sans Lost Updates
- ✅ Éviter les deadlocks par une approche méthodique
- ✅ Diagnostiquer et résoudre les problèmes de performance liés aux blocages
- ✅ Utiliser les bons types de verrous pour chaque situation
- ✅ Implémenter une coordination applicative avec Advisory Locks
Principes clés :
- MVCC gère la visibilité, verrous gèrent les modifications concurrentes
- Les verrous de ligne permettent plus de concurrence que les verrous de table
- Les deadlocks sont détectés et résolus automatiquement, mais il faut les prévenir
- L'ordre d'accès cohérent est la meilleure défense contre les deadlocks
- Les advisory locks offrent une flexibilité pour la coordination applicative
Dans la prochaine section (12.6), nous explorerons les Advisory Locks en profondeur et des patterns avancés de coordination.
Points clés à retenir :
- 🔑 MVCC + Verrous = Gestion complète de la concurrence
- 🔑 8 modes de verrous de table, du moins au plus restrictif
- 🔑 FOR UPDATE = verrou exclusif de ligne
- 🔑 Deadlock = attente circulaire, détecté automatiquement
- 🔑 Ordre cohérent d'accès = prévention #1 des deadlocks
- 🔑 Transactions courtes = moins de conflits
- 🔑 SKIP LOCKED = utile pour les queues de travail
- 🔑 Advisory locks = coordination applicative personnalisée