Gérez ses transactions – Principes ACID

On parle souvent de « transaction » dans PostgreSQL mais apprendre à les maitriser peut s’avérer utile lorsque vous devez annuler certaines instructions.

Qu’est-ce qu’une transaction ?

Les requêtes que vous envoyez au serveur peuvent modifier profondément les données stockées et leur structure. Pour s’assurer du bon déroulement de vos requêtes, PostgreSQL (comme toutes les bases de données) utilise le principe des transactions.

Une transaction est une unité de travail dans laquelle des instructions sont exécutées.

Imaginez un virement bancaire. Vous débitez 100 € d’un compte et vous créditez 100 € sur un autre. Si le système plante entre les deux opérations, l’argent disparaît dans le vide.

Une transaction est le mécanisme qui garantit que ces deux opérations forment un tout indivisible : soit tout réussit, soit rien n’est appliqué. C’est le principe ACID (Atomicité, Cohérence, Isolation, Durabilité).

Quand on travaille avec des bases, il faut toujours penser aux ACID

Arthur Bazin

Les transactions respectent toujours les propriétés ACID (plus de détails sur Wikipedia) :

• Atomicité : la transaction se fait totalement ou pas du tout, en cas d’échec les données sont remise dans le même état qu’avant la transaction (même en cas de panne pendant la transaction).
• Cohérence : la transaction n’entraine pas d’invalidité dans un objet de la base (les objets étaient valides avant et le restent après).
• Isolation : chaque transaction s’exécute sans aucun lien avec une quelconque autre transaction.
• Durabilité : les opérations effectuées dans la transaction sont effectuées de façon définitive une fois la transaction terminée (même en cas de panne immédiatement après la transaction).

Les transactions vont donc contenir vos requêtes afin qu’elles s’exécutent correctement.

Voici une liste des mots clés contrôlant les transactions :

• BEGIN, BEGIN TRANSACTION ou START TRANSACTION : permet de démarrer une transaction. Un point de sauvegarde de la BDD est créé à ce moment là.
• SAVEPOINT mon_pointdesauvegarde : permet de créer un point de sauvegarde à l’intérieur d’une transaction.
• END ou COMMIT : permet de terminer la transaction et de la valider. Les points de sauvegarde concernant cette transaction sont tous supprimés.
• ROLLBACK : Annule la transaction et restaure la BDD au point de sauvegarde créé lors du BEGIN.
• ROLLBACK TO mon_poindesauvegarde : Annule les opérations effectuées depuis la création du point de sauvegarde indiqué.

BEGIN et COMMIT – les bases

Démarrons par le minimum vital. Créons une table de test et faisons notre premier virement.

-- Table de démonstration
CREATE TABLE comptes (
  id   INT PRIMARY KEY,
  nom  TEXT,
  solde NUMERIC
);

INSERT INTO comptes VALUES
  (1, 'Alice', 500),
  (2, 'Bob',   200);

Voici un premier test :

BEGIN;  -- démarre la transaction

UPDATE comptes SET solde = solde - 100 WHERE id = 1;
-- Alice passe de 500 à 400

UPDATE comptes SET solde = solde + 100 WHERE id = 2;
-- Bob passe de 200 à 300

COMMIT;  -- valide et persiste tout

Jusqu’au COMMIT, les modifications ne sont visibles que dans votre session. Les autres connexions voient encore les anciennes valeurs.

L’autocommit – le piège invisible

Par défaut, PostgreSQL (et la plupart des clients comme psql, pgAdmin ou DBeaver) fonctionne en mode autocommit. Chaque requête est automatiquement commitée si vous n’ouvrez pas de BEGIN explicite.

-- Sans BEGIN, chaque UPDATE est sa propre transaction
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE 1  -- ← déjà commitée, impossible à annuler !

UPDATE comptes SET solde = solde + 100 WHERE id = 2;
UPDATE 1  -- ← également commitée

Si votre script plante entre les deux UPDATE, Alice a perdu 100 € mais Bob ne les a jamais reçus. Le script doit donc débuter par BEGIN et se terminer par COMMIT pour valider toutes les opérations liées ou aucune.

ROLLBACK pour tout annuler

Si quelque chose tourne mal à l’intérieur d’une transaction, ROLLBACK annule l’ensemble des modifications depuis le BEGIN.

BEGIN;

UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE 1

-- On vérifie avant de continuer
SELECT solde FROM comptes WHERE id = 1;
 solde
-------
   400

-- Oups, Alice n'a pas assez de fonds pour ce virement
ROLLBACK;
ROLLBACK

-- Le solde d'Alice est revenu à 500
SELECT solde FROM comptes WHERE id = 1;
 solde
-------
   500

PostgreSQL effectue aussi un ROLLBACK automatique si votre connexion est interrompue ou si une erreur grave survient dans la transaction. La base reste toujours dans un état cohérent.

SAVEPOINT – le rollback chirurgical

Parfois, on ne veut pas annuler toute la transaction mais juste revenir à un point intermédiaire. C’est le rôle du SAVEPOINT.

Imaginez que vous traitez un batch de 3 virements. Le premier et le troisième réussissent, mais le deuxième échoue. Avec les SAVEPOINT, vous pouvez annuler uniquement le deuxième.

On l’utilise en nommant le point de sauvegarde : SAVEPOINT mon_savepoint.

BEGIN;

-- Virement 1 : OK
UPDATE comptes SET solde = solde - 50 WHERE id = 1;

-- Sauvegarde de l'état actuel
SAVEPOINT apres_virement_1;

-- Virement 2 : 
UPDATE comptes SET solde = solde - 9999 WHERE id = 1;

-- solde négatif, on annule seulement ce virement
ROLLBACK TO SAVEPOINT apres_virement_1;

-- Virement 3 : OK
UPDATE comptes SET solde = solde - 30 WHERE id = 2;

COMMIT;
-- Seuls les virements 1 et 3 sont persistés

Utilisez RELEASE SAVEPOINT mon_savepoint pour libérer un SAVEPOINT devenu inutile et économiser de la mémoire dans les longues transactions.

Les niveaux d’isolation

Quand plusieurs sessions tournent en parallèle, des phénomènes indésirables peuvent apparaître. Le niveau d’isolation contrôle lesquels PostgreSQL laisse passer.

Prenez en compte le fait qu’au cours d’une seule et même transaction plusieurs requêtes peuvent être effectuées (les une après les autres).

Les phénomènes à éviter :

• Dirty read : lire une donnée modifiée par une transaction non encore commitée. (PostgreSQL ne l’autorise jamais, même en Read Uncommitted.)
• Non-repeatable read : relire la même ligne deux fois dans la même transaction et obtenir des valeurs différentes car une autre session a commité entre-temps.
• Phantom read : rejouer la même requête SELECT et obtenir des lignes supplémentaires ou en moins car une autre session a inséré/supprimé des lignes.
• Serialization anomaly : deux transactions exécutées en parallèle produisent un résultat impossible à reproduire en les exécutant l’une après l’autre.

Pour gérer ces phénomènes, on définit le niveau d’isolation lors du démarrage de la transaction :

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Le niveau peut être choisit parmi les suivants :

• READ UNCOMMITTED : équivalent à READ COMMITTED sous PostgreSQL.
• READ COMMITTED : défaut – une requête ne peut « voir » que les lignes commitées avant le début de celle-ci.
• REPEATABLE READ : une requête ne peut « voir » que les lignes commitées avant le début de la première requête de la transaction.
• SERIALIZABLE : une requête ne peut « voir » que les lignes commitées avant le début de la première requête de la transaction. Si deux transactions modifient parallèle les mêmes lignes, alors une erreur est retournée.

Voici un tableau récapitulatif des phénomènes possibles selon le niveau d’isolation :

*PostgreSQL protège contre les phantom reads dès Repeatable Read grâce à son implémentation MVCC.

Voici un exemple en mode READ COMMITTED. Les transactions s’effectuent en parallèle, les actions de l’une sont affichées sous forme de commentaires dans l’autre pour mieux visualiser l’enchainement.

BEGIN;
SELECT solde FROM comptes WHERE id = 1;
-- solde → 500

--B: UPDATE comptes SET solde = 999 WHERE id = 1; COMMIT;

SELECT solde FROM comptes WHERE id = 1;
-- solde → 999  ← valeur différente ! Non-repeatable read.
COMMIT;

--A:BEGIN;
--A:SELECT solde FROM comptes WHERE id = 1;
--A: solde → 500

UPDATE comptes SET solde = 999 WHERE id = 1; COMMIT;

--A:SELECT solde FROM comptes WHERE id = 1;
--A: solde → 999  ← valeur différente ! Non-repeatable read.
--A:COMMIT;

Voici un exemple en mode REPEATABLE READ.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT solde FROM comptes WHERE id = 1;
-- solde → 500

--B: UPDATE comptes SET solde = 999 WHERE id = 1; COMMIT;

SELECT solde FROM comptes WHERE id = 1;
-- solde → 500  ← snapshot figé au début de la transaction
COMMIT;

--A:BEGIN;
--A:SELECT solde FROM comptes WHERE id = 1;
--A: solde → 500

UPDATE comptes SET solde = 999 WHERE id = 1; COMMIT;

--A:SELECT solde FROM comptes WHERE id = 1;
--A: solde → 500  ← snapshot figé au début de la transaction
--A:COMMIT;

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQL ACIDBEGINCOMMITTransaction