Anomalies lors de l’exécution des transactions : Dirty Read, Non-Repeatable Read, Phantom Read

On refait un tour sur les types de transactions ! Dans le monde des transactions SQL, il y a trois grands “méchants” qui peuvent te pourrir la journée : Dirty Read, Non-Repeatable Read et Phantom Read. Ces “anomalies” apparaissent à cause d’un niveau d’isolation pas assez strict. Aujourd’hui, on va voir qui sont ces méchants, comment ils se manifestent et — le plus important — comment les combattre.

Avant de passer aux exemples, on va rappeler ce que ça veut dire.

• Dirty Read (Lecture sale) :
  Tu lis des données qui ont été modifiées, mais la transaction qui les a changées n’est pas encore validée (COMMIT) ou, pire, peut être annulée (ROLLBACK). C’est comme si tu envoyais de l’argent à un pote, tu regardes ton solde et tu te crois ruiné, mais ensuite tu changes d’avis et tu te rembourses. Magique !

• Non-Repeatable Read (Lecture non répétable) :
  Tu lis deux fois les mêmes données dans une transaction, mais entre les deux lectures, une autre transaction les a modifiées, et tu vois deux résultats différents. C’est comme si tu regardais ta date de naissance sur ta carte d’identité, tu la passes à un pote qui change les chiffres, tu regardes à nouveau et hop, ta date a changé.

• Phantom Read (Lecture fantôme) :
  Tu fais deux fois la même requête, mais la deuxième fois tu vois des lignes en plus, ajoutées par une autre transaction. C’est comme si tu comptais les gens dans une pièce, et quelqu’un ramène discrètement des potes en plus.

Problème Dirty Read

Imaginons qu’on a une table accounts :

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL
);
INSERT INTO accounts (owner, balance) VALUES ('Alice', 1000), ('Bob', 500);

Transaction 1 modifie le solde, mais n’est pas encore terminée, et Transaction 2 essaie de lire ces données en même temps.

Transaction 1 :

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
-- Le solde de Alice est maintenant 800, mais la transaction n’est pas encore finie.

Transaction 2 :

BEGIN;
SELECT balance FROM accounts WHERE owner = 'Alice'; -- On voit le solde : 800 (lecture sale).
ROLLBACK; -- Transaction 1 est annulée.

Maintenant, Transaction 2 bosse avec des données fausses, parce que Transaction 1 a annulé les changements. On peut éviter ça en utilisant le niveau d’isolation READ COMMITTED, qui empêche de voir les changements des transactions non validées.

Problème Non-Repeatable Read

Imagine que Transaction 1 lit des données, une autre transaction les modifie, et Transaction 1 relit les données. Elles sont différentes.

Transaction 1 :

BEGIN;
SELECT balance FROM accounts WHERE owner = 'Bob'; -- On voit le solde : 500.

Transaction 2 en parallèle :

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Bob';
COMMIT;

Transaction 1 encore :

SELECT balance FROM accounts WHERE owner = 'Bob'; -- On voit le solde : 400.
COMMIT;

Tu vois, les données ont changé dans la même transaction. Dans la vraie vie, ça peut être critique, genre pour des rapports financiers. On règle ça avec un niveau d’isolation plus élevé, genre REPEATABLE READ.

Problème Phantom Read

Imaginons qu’on a une table orders :

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer TEXT NOT NULL,
    total NUMERIC(10, 2) NOT NULL
);
INSERT INTO orders (customer, total) VALUES ('Alice', 100), ('Bob', 200);

Transaction 1 compte le nombre de commandes, une autre transaction ajoute une nouvelle commande, et Transaction 1 recompte.

Transaction 1 :

BEGIN;
SELECT COUNT(*) FROM orders; -- On voit : 2.

Transaction 2 en parallèle :

BEGIN;
INSERT INTO orders (customer, total) VALUES ('Charlie', 300);
COMMIT;

Transaction 1 encore :

SELECT COUNT(*) FROM orders; -- On voit : 3. Nouvelle commande “fantôme” apparue !
COMMIT;

Pour éviter les lectures fantômes, il faut le niveau d’isolation SERIALIZABLE, qui bloque complètement les changements parallèles qui pourraient modifier le résultat.

Moyens d’éviter les anomalies

Niveaux d’isolation vs. Anomalies

Comment choisir le bon niveau d’isolation ?

1. Si tu veux un accès rapide aux données et que les anomalies ne sont pas critiques (genre de l’analytics sur des données pas fraîches) — utilise READ COMMITTED.
2. Si tu veux que les données restent stables dans une transaction — utilise REPEATABLE READ.
3. Si tu veux l’isolation et la cohérence max — prends SERIALIZABLE. Attention : ça peut ralentir les perfs.

Conseils pratiques

Utilise des transactions avec le niveau d’isolation adapté à ton cas. Par exemple :

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT ...;
COMMIT;

Ajoute des index pour minimiser les locks sur les tables et accélérer les requêtes.

Optimise tes requêtes pour éviter les locks longs, surtout en SERIALIZABLE.

Erreurs spécifiques et comment les éviter

Parfois, un mauvais niveau d’isolation provoque des conflits et fait baisser les perfs. Par exemple, utiliser SERIALIZABLE dans un système avec plein de transactions parallèles peut causer des blocages et du “starvation” de transactions.

Pour éviter ça, analyse tes requêtes, teste les perfs avec différents niveaux d’isolation et utilise les bons index.