Transactions en Base de Données : ACID et Isolation

Pourquoi un virement bancaire a besoin de plus que deux UPDATE

Déplacez 50 € d’un compte à un autre et la version naïve, ce sont deux instructions : soustraire du compte A, ajouter au compte B. Exécutez-les séparément et, si le processus plante ou si la connexion tombe entre les deux, l’argent a disparu de A et n’arrive jamais sur B. La base de données a fait exactement ce qu’on lui a demandé, deux fois. L’entreprise a quand même perdu 50 €.

Une transaction enveloppe les deux instructions pour que la base de données les traite comme une seule unité : soit les deux se produisent, soit aucune. Cette garantie, plus trois autres, c’est ce qu’on appelle ACID. C’est pour cela que vous pouvez confier à une base de données de l’argent ou des niveaux de stock, là où « à moitié fait » est pire que « pas fait ».

Ce que garantissent les quatre propriétés ACID

• Atomicité. Toutes les instructions d’une transaction valident ensemble, ou aucune ne valide. Si le processus plante après le débit mais avant le crédit, la base de données annule aussi le débit. Il n’y a aucun état partiel à nettoyer à la main.
• Cohérence (Consistency). Une transaction ne peut faire passer la base de données que d’un état valide à un autre. Si une contrainte CHECK interdit un solde négatif, aucune transaction ne peut valider une ligne qui la viole, quoi que le code de l’application ait tenté.
• Isolation. Les transactions qui tournent en même temps ne voient pas le travail à moitié fait des autres. Le niveau de rigueur appliqué est un curseur, pas une règle fixe : ce curseur, c’est le niveau d’isolation, traité plus bas.
• Durabilité. Une fois que la base de données annonce qu’une transaction a été validée, cette donnée survit à un crash une seconde plus tard. C’est à cela que sert le write-ahead log : la modification est sur disque (ou répliquée) avant que le commit ne retourne.

L’atomicité et la durabilité sont le travail de la base de données, et les moteurs les plus utilisés les gèrent correctement. L’isolation, elle, dépend de vous. Une isolation plus stricte coûte de la concurrence, et ce coût se voit dans les graphes de latence en production.

Comment exécuter une transaction avec BEGIN, COMMIT et ROLLBACK

Dans PostgreSQL (et la plupart des bases SQL, à de petites différences de syntaxe près), une transaction est l’ensemble des instructions entre BEGIN et COMMIT :

1
2
3
4
5
6

BEGIN;

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

COMMIT;

Si quelque chose se passe mal avant le COMMIT — une violation de contrainte, ou une vérification métier dans le code de l’application — ROLLBACK annule tout ce qui s’est passé depuis BEGIN :

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- la vérification métier dans le code de l'application trouve un solde négatif
ROLLBACK;

Après le rollback, le solde du compte 1 est exactement celui d’avant l’exécution de BEGIN. Pour une transaction longue où vous ne voulez annuler qu’une partie du travail, un savepoint vous donne un rollback partiel :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- annule seulement le crédit
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- crédite un autre compte à la place

COMMIT;

En dehors d’un BEGIN explicite, la plupart des drivers exécutent chaque instruction dans sa propre transaction auto-validée. Chaque instruction est atomique en elle-même, mais rien ne lie deux instructions entre elles. Sautez le BEGIN dans le virement bancaire et vous retrouvez les 50 € perdus.

Ce que préviennent les niveaux d’isolation des transactions

Deux transactions concurrentes peuvent interférer entre elles de trois façons précises, et les niveaux d’isolation se définissent selon celles qu’ils autorisent :

Le standard SQL définit quatre niveaux autour de ces trois anomalies :

Vous voudrez rarement utiliser READ UNCOMMITTED. PostgreSQL ne l’implémente même pas et exécute Read Committed à la place sans le dire ; MySQL le respecte, lui, et vous montrera des dirty reads. En pratique, les deux niveaux qui comptent sont Read Committed et Repeatable Read, et les deux grandes bases open source n’ont pas le même réglage par défaut :

• PostgreSQL utilise Read Committed par défaut. Chaque instruction à l’intérieur d’une transaction voit un snapshot pris au début de cette instruction, donc deux SELECT dans la même transaction peuvent renvoyer des données différentes si une autre transaction a validé entre les deux.
• MySQL (InnoDB) utilise Repeatable Read par défaut. Le snapshot est pris une seule fois, à la première lecture de la transaction, et chaque SELECT ultérieur dans cette transaction voit ce même snapshot, même si une autre transaction a validé une mise à jour depuis.

Dans PostgreSQL, fixez le niveau juste après BEGIN, avant qu’aucune requête ne s’exécute :

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... plus tard dans la même transaction ...
SELECT balance FROM accounts WHERE id = 1; -- garanti la même valeur que ci-dessus
COMMIT;

Dans MySQL, la forme SESSION change le niveau pour chaque transaction suivante sur cette connexion :

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Comment Repeatable Read bloque un phantom read dans PostgreSQL

Ouvrez deux sessions psql sur la même table et lancez ceci. Le voir tourner une fois marque plus durablement que mémoriser le tableau.

Session A :

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Session B, pendant que la transaction de A est encore ouverte :

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Session A, même transaction ouverte, en relançant la requête identique :

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

La ligne que B a insérée et validée n’apparaît pas. Le Repeatable Read de PostgreSQL prend son snapshot à la première requête de la transaction de A et le garde jusqu’au COMMIT, donc les nouvelles lignes d’autres transactions n’apparaissent jamais. C’est plus strict que ce que demande le standard : le tableau ci-dessus autorise des phantoms à ce niveau, mais l’implémentation de PostgreSQL n’en produit pas. Lancez la même séquence avec A en Read Committed et le second SELECT renvoie la nouvelle ligne, parce que Read Committed prend un snapshot frais à chaque instruction.

Serializable va plus loin. Il détecte quand deux transactions concurrentes ont produit un résultat qui n’aurait pu se produire dans aucun ordre séquentiel, et en annule une avec une erreur de sérialisation (PostgreSQL renvoie le SQLSTATE 40001) plutôt que de laisser l’anomalie se valider. C’est le niveau qui ferme la classique race condition où deux transactions lisent chacune une valeur, décident en fonction d’elle, et écrivent toutes les deux. Le code qui utilise SERIALIZABLE doit être prêt à réessayer :

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... instructions ...
COMMIT;
-- en cas d'erreur 40001 ("could not serialize access due to read/write dependencies") :
-- faites un rollback et réessayez toute la transaction

Ce que coûtent les niveaux d’isolation plus stricts

Le prix d’une isolation plus stricte, c’est la concurrence :

• Les écritures restent verrouillées jusqu’au commit, à tous les niveaux. Un UPDATE prend un verrou de ligne au moment où il s’exécute et le garde jusqu’à COMMIT ou ROLLBACK, quel que soit le niveau d’isolation. Ce que Repeatable Read et Serializable ajoutent, c’est de garder le même snapshot (ou, pour Serializable, des verrous de prédicat sur les lignes que vos requêtes ont examinées) pendant toute la transaction. Faites un appel API externe lent entre BEGIN et COMMIT et chaque ligne que vous avez écrite reste verrouillée, et chaque ligne que vous avez lue sous Serializable reste suivie, pendant tout le temps que dure cet appel.
• Les relances ne sont pas optionnelles sous Serializable. Il rejettera des transactions qui semblent valides sous charge concurrente, par conception. Si votre application ne capture pas les erreurs de sérialisation et ne relance pas, ces transactions échouent de façon intermittente, sous charge, ce qui est le pire moment pour s’en rendre compte. Repeatable Read peut lever la même erreur 40001 sur une simple mise à jour concurrente, donc le code de relance ne sert pas qu’à Serializable — toute transaction au-dessus de Read Committed en a besoin.
• Les transactions longues bloquent les changements de schéma et font gonfler le stockage. Le MVCC de PostgreSQL garde les anciennes versions des lignes aussi longtemps qu’une transaction ouverte pourrait encore en avoir besoin. Une transaction laissée ouverte pendant des heures (un bug courant : en ouvrir une dans une requête web et ne jamais faire ni commit ni rollback) empêche VACUUM de récupérer cet espace, et un ALTER TABLE sur une table qu’elle a touchée attend qu’elle se termine.
• Une écriture n’a pas toujours besoin d’une transaction explicite. Une seule instruction UPDATE est déjà atomique. En envelopper une dans BEGIN/COMMIT ajoute un aller-retour sans aucune garantie supplémentaire. Utilisez une transaction explicite quand la correction dépend de la réussite ou de l’échec conjoint de deux instructions ou plus : le virement bancaire, une inscription qui écrit à la fois une ligne dans users et dans accounts, une commande qui décrémente le stock et insère l’enregistrement de la commande.

Les ORM (Entity Framework, SQLAlchemy, ActiveRecord) ouvrent généralement une transaction par unité de travail et la valident pour vous. N’allez pas contre ça. Le bug à surveiller, c’est l’inverse : du code qui appelle .save() ou .commit() séparément pour deux écritures liées, en jetant l’atomicité que l’ORM vous aurait donnée.

Quel niveau d’isolation utiliser

Partez de Read Committed et ne montez que pour les transactions précises où vous savez nommer la course que vous fermez. C’est la même discipline que l’ajout d’un index seulement après avoir trouvé la requête lente. Si vous ne pouvez pas écrire cette course en une phrase, vous n’avez pas encore besoin d’un niveau plus strict.

Articles associés

• Comment Fonctionnent les Index de Base de Données
• Les Types de JOIN en SQL Expliqués avec des Exemples
• Qu’est-ce qu’une Race Condition et Comment la Corriger