Niveau d'isolation REPEATABLE READ : prévention du Non-Repeatable Read

Imagine que tu joues à un jeu en ligne, et qu'un cheater bidouille le code pour booster son perso, ou que tu lis un livre à la bibliothèque pendant que quelqu'un change les pages, ajoute des chapitres ou remplace carrément le livre. Pas cool, hein ? C'est justement ce genre de "surprises" que le niveau d'isolation REPEATABLE READ t'évite.

REPEATABLE READ te garantit que les données que tu vois dans une transaction restent les mêmes jusqu'à la fin de cette transaction. Même si une autre transaction essaie de modifier ces données, la tienne sera protégée contre ces changements.

Points clés :

• Empêche le Dirty Read (lecture de données pas encore validées).
• Et surtout, empêche le Non-Repeatable Read. Ça veut dire que si tu lis un jeu de données au début de ta transaction, tu récupéreras exactement les mêmes données si tu relis, même si quelqu'un d'autre les a modifiées entre temps.

Cependant, REPEATABLE READ ne protège pas contre le Phantom Read. Si une autre transaction ajoute de nouvelles lignes, elles peuvent apparaître dans ta requête suivante. Pour éviter aussi cette anomalie, il te faudra le niveau SERIALIZABLE, mais on en parlera plus tard.

Comment définir le niveau d'isolation REPEATABLE READ

Avant de passer aux exemples, voyons comment activer ce niveau d'isolation dans PostgreSQL. Il y a deux façons principales :

1. Définir le niveau d'isolation pour une transaction spécifique :

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN;
   -- Tes requêtes
   COMMIT;
   

2. Définir le niveau d'isolation pour la session courante :

   SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   

Dans le deuxième cas, toutes les transactions de la session utiliseront REPEATABLE READ.

Exemple : prévention du Non-Repeatable Read

Imaginons qu'on a une table accounts avec la structure suivante :

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'en attente'
);

INSERT INTO orders (customer_name, status)
VALUES ('Alice', 'en attente'), ('Bob', 'en attente');

On commence avec un scénario de base où une transaction modifie les données et une autre les lit.

Scénario sans REPEATABLE READ (niveau READ COMMITTED)

Transaction 1 commence :

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Résultat : 100

Pendant ce temps, Transaction 2 modifie les données :

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transaction 1 continue :

SELECT balance FROM accounts WHERE account_id = 1;
-- Résultat : 150 (les données ont changé !)
COMMIT;

Comme tu vois, avec le niveau READ COMMITTED, les données peuvent changer entre deux lectures dans la même transaction. C'est ça le Non-Repeatable Read.

Scénario avec REPEATABLE READ

Essayons maintenant le même exemple, mais avec le niveau d'isolation REPEATABLE READ.

Transaction 1 :

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Résultat : 100

Transaction 2 :

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transaction 1 continue :

SELECT balance FROM accounts WHERE account_id = 1;
-- Toujours : 100 (les données n'ont pas changé !)
COMMIT;

Peu importe les changements faits par l'autre transaction, la transaction 1 voit les données telles qu'elles étaient au début. Du coup, le Non-Repeatable Read est évité.

Comment fonctionne REPEATABLE READ

PostgreSQL utilise le mécanisme MVCC (Multi-Version Concurrency Control) pour gérer le niveau d'isolation REPEATABLE READ. Le principe de base du MVCC, c'est que chaque transaction reçoit un "snapshot" stable de la base, qui ne change pas jusqu'à la fin de la transaction. Ça marche grâce à la création et la gestion de plusieurs versions des lignes.

Quand une transaction démarre, elle voit les données telles qu'elles étaient au moment de son lancement. Si une autre transaction fait des modifs, PostgreSQL crée une nouvelle version de la ligne, mais l'ancienne reste visible pour toutes les transactions qui l'utilisent.

C'est pour ça que les transactions peuvent être lentes et consommer pas mal de mémoire. Et c'est aussi pour ça que peu de gens bossent tout le temps avec le niveau d'isolation le plus fort : c'est le plus fiable, mais il ralentit grave la base.

Limites de REPEATABLE READ : Phantom Read

Comme on l'a déjà dit, REPEATABLE READ ne protège pas contre le Phantom Read. Pour piger ce que ça veut dire, prenons un exemple avec des requêtes sur des plages de données.

Imaginons qu'on a une table orders :

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL
);

INSERT INTO orders (amount)
VALUES (50), (100), (150);

Transaction 1 :

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Résultat : 2

Transaction 2 :

BEGIN;
INSERT INTO orders (amount) VALUES (200);
COMMIT;

Transaction 1 continue :

SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Résultat : 3 (une nouvelle ligne est apparue dans le résultat !)
COMMIT;

Ici, une nouvelle ligne (avec amount = 200) a été ajoutée par une autre transaction, et elle "apparaît comme par magie" dans le résultat de la transaction 1, même avec le niveau d'isolation REPEATABLE READ.

Si tu veux éviter le Phantom Read, il faudra passer au niveau SERIALIZABLE, mais ça implique toujours un compromis sur les perfs.

Avantages et inconvénients de REPEATABLE READ

Le niveau d'isolation REPEATABLE READ est top quand tu veux être sûr que les données ne changent pas pendant ta transaction. Dès que tu lis un truc, cette valeur reste la même jusqu'au COMMIT, même si quelqu'un d'autre essaie de la modifier dans une autre transaction.

Ce mode évite à la fois les dirty read (lectures sales) et les non-repeatable read (lectures non répétables). Tu bosses avec les mêmes données qu'au début — pas de mise à jour surprise "en live". C'est super utile pour les rapports ou les décisions où la cohérence est importante.

Par contre, REPEATABLE READ ne gère pas les fameux "phantoms" (phantom read) — quand de nouvelles lignes apparaissent dans le résultat d'une requête déjà faite dans la même transaction. Et sous forte charge, ce niveau peut causer des conflits entre transactions, surtout si elles tapent souvent dans les mêmes données. Ça peut mener à des blocages et des rollbacks, même si tes requêtes sont nickel.

En gros, REPEATABLE READ c'est un bon compromis entre fiabilité et perfs, mais dans les scénarios très concurrents, il faudra peut-être ajuster et surveiller un peu plus.

Conseils utiles et erreurs courantes

• N'oublie pas que le choix du niveau d'isolation impacte les perfs. Utilise REPEATABLE READ seulement si tu as vraiment besoin de garantir l'immuabilité des données.
• Confondre REPEATABLE READ et SERIALIZABLE est une erreur fréquente. Si tu vois de nouvelles lignes dans une requête répétée, c'est normal avec REPEATABLE READ.
• Avec des transactions longues, fais gaffe aux conflits de locks. Les transactions qui traînent peuvent bloquer d'autres opérations.

PostgreSQL te file plein d'outils pour gérer l'isolation des transactions. Le niveau REPEATABLE READ est parfait quand tu veux être sûr que les données déjà lues dans une transaction ne bougeront pas.