Transactions SQL et concurrence : verrous, isolation, interblocages

Une transaction regroupe plusieurs commandes en un tout qui réussit ou échoue ensemble ; quand deux sessions modifient la même ligne, la seconde attend que la première valide ou annule, parce que la ligne est verrouillée. C'est ce qui garantit qu'un script de migration et un utilisateur ne s'écrasent pas, mais c'est aussi ce qui fait qu'une transaction oubliée ouverte bloque tout le monde. Cette leçon met deux sessions face à face sur la base fil rouge, sous PostgreSQL 18.6 : attente sur verrou, mise à jour perdue, niveaux d'isolation et interblocage, chaque fois mesurés.

Ce que vous allez apprendre

• Reconnaître une session bloquée par un verrou et trouver qui la bloque.
• Éviter la mise à jour perdue d'un « lire puis écrire ».
• Choisir entre READ COMMITTED et REPEATABLE READ.
• Réagir à un interblocage, à un lock_timeout et à une transaction restée ouverte.

Prérequis

La leçon Modifier les données, qui présente BEGIN, COMMIT et ROLLBACK. Pour rejouer les exemples, ouvrez deux terminaux, A et B, chacun avec sa session docker exec -it formation-sql psql -U postgres -d infra.

Une transaction, tout ou rien

Entre BEGIN et COMMIT, les modifications d'une session sont invisibles aux autres, et ROLLBACK les annule toutes. Une erreur dans la transaction la met en échec : mesuré, après un UPDATE refusé par une contrainte, toute commande suivante répond current transaction is aborted, commands ignored until end of transaction block, jusqu'au ROLLBACK.

Pour annuler seulement une partie, SAVEPOINT pose un point de reprise : après SAVEPOINT avant_ram, une erreur suivie de ROLLBACK TO SAVEPOINT avant_ram revient à ce point, et le COMMIT valide ce qui précédait. Mesuré, la note audit posée avant le point de reprise est conservée, la RAM erronée non.

Quand une session en attend une autre

Toute ligne modifiée par une transaction est verrouillée jusqu'à sa fin. Une seconde session qui veut modifier la même ligne attend :

-- terminal A
BEGIN;
UPDATE servers SET ram_gb = 64 WHERE hostname = 'db-prod-01';
-- A ne valide pas encore

-- terminal B : la commande reste suspendue
UPDATE servers SET ram_gb = 48 WHERE hostname = 'db-prod-01';

-- terminal A
COMMIT;
-- B se débloque aussitôt et répond UPDATE 1

Mesuré avec une transaction A qui garde le verrou trois secondes, B attend 2,6 secondes, puis écrase la valeur : la RAM finale vaut 48. Une lecture, elle, n'attend pas : pendant le verrou, un SELECT répond en 90 millisecondes avec l'ancienne valeur validée. PostgreSQL garde plusieurs versions d'une ligne (c'est le MVCC), si bien que les lecteurs ne bloquent jamais les écrivains, ni l'inverse.

Qui bloque qui ?

La vue pg_stat_activity liste les sessions, et la fonction pg_blocking_pids(pid) dit quelles sessions bloquent une session donnée. Lancée depuis un troisième terminal pendant l'attente :

SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS bloque_par, left(query, 50) AS requete
FROM pg_stat_activity
WHERE datname = 'infra' AND pid <> pg_backend_pid() AND state <> 'idle';

 pid  |        state        | wait_event_type |  wait_event   | bloque_par |                      requete
------+---------------------+-----------------+---------------+------------+----------------------------------------------------
 2786 | idle in transaction | Client          | ClientRead    | {}         | UPDATE servers SET ram_gb = 64 WHERE hostname = 'd
 2793 | active              | Lock            | transactionid | {2786}     | UPDATE servers SET ram_gb = 48 WHERE hostname = 'd

La session 2793, celle de B, attend un verrou (wait_event_type = Lock), et bloque_par désigne la 2786, celle de A : elle a modifié la ligne et attend, sans valider, à l'état idle in transaction. C'est la requête à lancer quand « la base ne répond plus » : on remonte jusqu'à la session qui tient le verrou.

La mise à jour perdue

Le piège classique de la concurrence ne produit aucune erreur. Deux scripts veulent ajouter 500 Go au disque de backup-01. Chacun lit la valeur, 2000, calcule 2500 dans le script, puis écrit 2500 :

-- ❌ chaque session écrit le résultat d'un calcul fait sur une lecture périmée
BEGIN;
SELECT disk_gb FROM servers WHERE hostname = 'backup-01';     -- 2000
UPDATE servers SET disk_gb = 2500 WHERE hostname = 'backup-01';
COMMIT;

Mesuré avec les deux sessions entremêlées : la valeur finale vaut 2500, et non 3000. La seconde écriture a effacé la première, et la transaction n'y change rien, puisque chaque session a lu avant que l'autre écrive. Deux corrections :

-- 1. faire le calcul dans l'UPDATE : la lecture et l'écriture sont atomiques
UPDATE servers SET disk_gb = disk_gb + 500 WHERE hostname = 'backup-01';

-- 2. verrouiller dès la lecture, quand le calcul doit rester dans le script
BEGIN;
SELECT disk_gb FROM servers WHERE hostname = 'backup-01' FOR UPDATE;
-- ... calcul ...
UPDATE servers SET disk_gb = 2500 WHERE hostname = 'backup-01';
COMMIT;

Les deux rendent 3000. SELECT ... FOR UPDATE pose le verrou dès la lecture : la seconde session attend sur son propre FOR UPDATE, puis lit la valeur déjà modifiée. La première correction est la plus simple, et suffit chaque fois que le calcul tient dans une expression SQL.

Les niveaux d'isolation

Le niveau d'isolation dit ce qu'une transaction voit des modifications des autres. PostgreSQL utilise par défaut READ COMMITTED : SHOW default_transaction_isolation le confirme. Chaque commande voit les données validées au moment où elle démarre. Mesuré, une transaction qui compte deux fois les alertes ouvertes, pendant qu'une autre session en acquitte une, obtient 8 puis 7 : deux lectures différentes dans la même transaction.

Avec BEGIN ISOLATION LEVEL REPEATABLE READ, la transaction travaille sur une photo prise à sa première requête : la même expérience rend 8 puis 8. C'est ce qu'on veut pour un rapport ou un export cohérent. Le prix est le suivant : si la transaction tente de modifier une ligne changée entre temps par une autre, PostgreSQL refuse :

ERROR:  could not serialize access due to concurrent update

L'application doit alors rejouer toute la transaction. Ce refus est voulu : il remplace la mise à jour perdue par une erreur visible. Le niveau SERIALIZABLE va plus loin et garantit qu'un ensemble de transactions donne le même résultat que si elles s'étaient exécutées l'une après l'autre, au prix de plus d'échecs à rejouer.

Niveau	Ce que voit chaque commande	Conflit d'écriture
READ COMMITTED (défaut)	les données validées à son démarrage	la seconde attend, puis écrase
REPEATABLE READ	la photo du début de la transaction	erreur, transaction à rejouer
SERIALIZABLE	la photo, plus un contrôle global	erreur, transaction à rejouer

Glissez pour voir

L'interblocage

Deux transactions qui verrouillent les mêmes lignes dans un ordre différent forment un interblocage, ou deadlock : A tient le serveur 1 et attend le 2, B tient le 2 et attend le 1. PostgreSQL le repère, au bout de deadlock_timeout (1 seconde par défaut), et annule l'une des deux :

ERROR:  deadlock detected
DETAIL:  Process 1611 waits for ShareLock on transaction 1121; blocked by process 1618.
Process 1618 waits for ShareLock on transaction 1120; blocked by process 1611.

La transaction annulée doit être rejouée ; l'autre se termine normalement. La prévention tient en une règle : verrouiller les lignes toujours dans le même ordre, par exemple par id croissant.

Ne pas attendre indéfiniment

Par défaut, une session attend un verrou sans limite. Dans un script ou une migration, mieux vaut échouer vite que rester suspendu. lock_timeout fixe une durée maximale d'attente :

SET lock_timeout = '1s';
UPDATE servers SET ram_gb = 48 WHERE hostname = 'db-prod-01';
-- ERROR:  canceling statement due to lock timeout

Mesuré : l'erreur tombe au bout de 1,1 seconde, au lieu d'attendre la fin de l'autre transaction. Le script peut alors réessayer après une pause.

La transaction restée ouverte

Une session qui a fait BEGIN, modifié une ligne, puis attend sans valider garde ses verrous : pg_stat_activity l'affiche à l'état idle in transaction, comme la session A plus haut. C'est la cause la plus fréquente d'une base « bloquée » : un psql laissé ouvert, un script interrompu entre deux commandes. Le réglage idle_in_transaction_session_timeout ferme ces sessions au bout d'un délai ; il vaut 0, désactivé, par défaut. Le réflexe reste de terminer chaque transaction par COMMIT ou ROLLBACK, et de surveiller cet état.

À retenir

• Une transaction est tout ou rien ; après une erreur, plus rien ne passe avant ROLLBACK, sauf retour à un SAVEPOINT.
• Une ligne modifiée est verrouillée jusqu'à la fin de la transaction : les écrivains attendent, les lecteurs jamais.
• pg_blocking_pids dans pg_stat_activity dit qui bloque qui.
• Un « lire puis écrire » perd des mises à jour : calcul dans l'UPDATE, ou SELECT ... FOR UPDATE.
• READ COMMITTED relit à chaque commande, REPEATABLE READ fige une photo et refuse un conflit d'écriture, à rejouer.
• Un interblocage annule une transaction ; lock_timeout borne l'attente ; une session idle in transaction garde ses verrous.

Contrôle de connaissances

Contrôle de connaissances

Validez vos connaissances avec ce quiz interactif

6 questions

6 min.

70% requis

Informations

• Le chronomètre démarre au clic sur Démarrer
• Questions à choix multiples, vrai/faux et réponses courtes
• Vous pouvez naviguer entre les questions
• Les résultats détaillés sont affichés à la fin

Démarrer le quiz

Lance le quiz et démarre le chronomètre

×

📖 Voir la documentation →