06 - Transactions et ACID : garantir l'intégrité

Ce que tu vas apprendre

• Les 4 propriétés ACID et ce qu'elles garantissent concrètement
• BEGIN, COMMIT, ROLLBACK, SAVEPOINT
• Les niveaux d'isolation et les problèmes de concurrence
• Deadlocks, SELECT FOR UPDATE, advisory locks

Prerequisites

Avoir lu les articles précédents. Comprendre les bases des JOINs et des index.

ACID, c'est quoi concrètement

On parle beaucoup d'ACID sans toujours comprendre ce que ca implique en pratique. Les 4 propriétés :

Atomicite : une transaction est tout ou rien. Si tu transferes 100 euros du compte A au compte B, soit les deux opérations reussissent, soit aucune. Pas de cas ou A est debite mais B n'est pas credite.

Coherence (Consistency) : la base passe d'un état valide a un autre état valide. Si tu as une contrainte CHECK (balance >= 0), la transaction echoue plutot que de laisser un solde negatif.

Isolation : deux transactions concurrentes ne s'interferent pas (selon le niveau d'isolation). Transaction A ne voit pas les modifications non commitees de transaction B.

Durabilite : une fois le COMMIT fait, les donnees sont sur disque. Meme si le serveur crashe juste apres. PostgreSQL utilise le WAL (Write-Ahead Log) pour ca, voir le glossaire.

BEGIN, COMMIT, ROLLBACK

sql-- Transfert d'argent : les deux operations ou aucune
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Tout s'est bien passe ? On valide
COMMIT;

Si quelque chose se passe mal entre le BEGIN et le COMMIT :

sqlBEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Oups, erreur ici (contrainte violee, bug, timeout...)

ROLLBACK;  -- annule tout depuis le BEGIN

Avec un ORM comme Prisma, les transactions ressemblent a ca :

typescriptawait prisma.$transaction(async (tx) => {
  await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } });
  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } });
});
// Si une des deux operations echoue, Prisma fait automatiquement un ROLLBACK

C'est du BEGIN/COMMIT/ROLLBACK sous le capot. Mais avec l'ORM, tu ne vois pas les requêtes SQL, tu ne contrôles pas le niveau d'isolation, et tu ne peux pas utiliser les fonctionnalités avancees comme les SAVEPOINTs.

SAVEPOINT : le rollback partiel

sqlBEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 200.00);
SAVEPOINT before_items;

INSERT INTO order_items (order_id, product, price) VALUES (currval('orders_id_seq'), 'Widget', 200.00);
-- Oups, erreur sur les items

ROLLBACK TO before_items;
-- La commande est toujours la, mais sans les items

-- On peut reessayer ou continuer
INSERT INTO order_items (order_id, product, price) VALUES (currval('orders_id_seq'), 'Gadget', 200.00);

COMMIT;

Les SAVEPOINTs permettent des rollbacks partiels sans annuler toute la transaction. Utile pour les imports batch ou tu veux ignorer les lignes en erreur sans perdre les lignes valides.

Les niveaux d'isolation

C'est la qu'on entre dans le dur. PostgreSQL propose 3 niveaux d'isolation (4 selon le standard SQL, mais READ UNCOMMITTED est traite comme READ COMMITTED) :

READ COMMITTED (le défaut)

Chaque instruction de la transaction voit les donnees commitees au moment de son exécution.

sql-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
SELECT balance FROM accounts
WHERE id = 1;
-- balance = 1000
                                    UPDATE accounts SET balance = 900 WHERE id = 1;
                                    COMMIT;
SELECT balance FROM accounts
WHERE id = 1;
-- balance = 900 (voit le commit de B !)
COMMIT;

Le deuxieme SELECT voit la modification de B. C'est le "non-repeatable read". En READ COMMITTED, c'est normal et accepte.

REPEATABLE READ

La transaction voit un snapshot de la base au début de la transaction. Les modifications des autres transactions ne sont pas visibles.

sqlSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- balance = 1000

-- Meme si une autre transaction modifie et commit, on voit toujours 1000
SELECT balance FROM accounts WHERE id = 1;
-- balance = 1000 (snapshot fige)

COMMIT;

Mais attention : si tu essaies de modifier une ligne qui a ete modifiee par une autre transaction entre-temps, PostgreSQL leve une erreur :

ERROR: could not serialize access due to concurrent update

Ton code doit gerer ce cas (retry).

SERIALIZABLE

Le niveau le plus strict. PostgreSQL garantit que le résultat est le meme que si les transactions s'etaient exécutées l'une apres l'autre (pas en parallèle). Si c'est impossible, il annule une des transactions.

sqlSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... operations ...
COMMIT;
-- Peut echouer avec : ERROR: could not serialize access

Mon avis : READ COMMITTED suffit pour 95% des cas. Si tu as besoin de REPEATABLE READ, c'est probablement pour un rapport ou un export qui doit voir des donnees coherentes. SERIALIZABLE, c'est pour les calculs financiers ou l'intégrité absolue est requise (et tu dois gerer les retries dans ton code applicatif).

Les problèmes de concurrence

• Dirty read : lire des donnees non commitees (impossible en PostgreSQL, meme en READ COMMITTED)
• Non-repeatable read : la meme requête retourne des résultats différents dans la meme transaction
• Phantom read : de nouvelles lignes apparaissent dans un meme SELECT (un INSERT par une autre transaction)

La doc PostgreSQL sur les niveaux d'isolation est tres bien faite sur ce sujet.

Deadlocks

Deux transactions qui s'attendent mutuellement :

sql-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
UPDATE accounts SET balance = 900   UPDATE accounts SET balance = 500
WHERE id = 1;                       WHERE id = 2;
-- A a un lock sur la ligne 1       -- B a un lock sur la ligne 2

UPDATE accounts SET balance = 500   UPDATE accounts SET balance = 900
WHERE id = 2;                       WHERE id = 1;
-- A attend que B libere la ligne 2 -- B attend que A libere la ligne 1
-- DEADLOCK !

PostgreSQL détecté les deadlocks automatiquement (en général en moins d'une seconde) et annule une des deux transactions avec une erreur :

ERROR: deadlock detected

Pour éviter les deadlocks : verrouille toujours les ressources dans le meme ordre. Si tu dois modifier les comptes 1 et 2, commence toujours par le plus petit id. C'est une convention simple qui fonctionne bien.

SELECT ... FOR UPDATE

Verrouiller des lignes spécifiques pour empecher les modifications concurrentes :

sqlBEGIN;

-- Verrouille la ligne du compte 1 (les autres transactions attendront)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- On peut lire et modifier en toute securite
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;  -- libere le lock

Variantes :

• FOR UPDATE : lock exclusif (lecture et écriture bloquees pour les autres)
• FOR SHARE : lock partage (écriture bloquee, lecture autorisee)
• FOR UPDATE NOWAIT : echoue immédiatement au lieu d'attendre
• FOR UPDATE SKIP LOCKED : ignore les lignes deja verrouillees (utile pour les queues de jobs)

SKIP LOCKED est un pattern genial pour les file d'attente :

sql-- Worker qui prend le prochain job disponible sans attendre
BEGIN;
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- ... traitement du job ...
UPDATE jobs SET status = 'processing' WHERE id = ...;
COMMIT;

Plusieurs workers peuvent tourner en parallèle sans conflit. C'est comme ca que fonctionnent les systèmes de queues legers bases sur PostgreSQL (comme Graphile Worker).

Advisory locks

Des verrous applicatifs, pas lies a des lignes spécifiques :

sql-- Prend un lock applicatif (identifie par un numero)
SELECT pg_advisory_lock(42);

-- ... code critique ...

-- Libere le lock
SELECT pg_advisory_unlock(42);

Utile pour empecher deux crons de s'exécuter en meme temps, ou pour sérialiser des opérations qui ne touchent pas les memes lignes mais doivent etre mutuellement exclusives.

Sur paltemps.fr, on utilise les advisory locks pour les migrations de donnees qui tournent sur plusieurs instances. Le premier worker prend le lock, les autres attendent ou partent faire autre chose.

Résumé

• Une transaction est tout ou rien (BEGIN/COMMIT/ROLLBACK)
• READ COMMITTED est le défaut et suffit dans la plupart des cas
• SERIALIZABLE garantit l'equivalence sequentielle mais nécessité des retries
• Les deadlocks sont detectes automatiquement par PostgreSQL
• FOR UPDATE SKIP LOCKED est parfait pour les queues de jobs
• Les advisory locks servent a la coordination applicative

Article précédent : 05 - Les index Article suivant : 07 - Fonctions et stored procedures

Sources

• PostgreSQL Documentation - Transaction Isolation
• PostgreSQL Documentation - Explicit Locking
• Citus Data - PostgreSQL Advisory Locks
• pganalyze - PostgreSQL Locking Visibility