Déplacer 300 roupies d’Asha vers Ben nécessite deux mises à jour : soustraire d’un compte et ajouter à l’autre. Si le système échoue entre eux, l’argent disparaît. Les transactions SQL existent pour rendre cela impossible, en traitant les deux étapes comme une seule unité.

Dans ce guide, nous passons en revue les validations, les restaurations et les échecs sur une petite table de comptes. Nous avons exécuté ces exemples dans SQLite et les soldes affichés proviennent de cette exécution. Les commandes de base sont similaires dans toutes les bases de données, mais le comportement de verrouillage et d’isolation varie beaucoup, c’est pourquoi nous vous indiquons où consulter la documentation de votre propre base de données.

Qu’est-ce qu’une transaction ?

Une transaction est une séquence d’une ou plusieurs instructions SQL que la base de données traite comme une opération logique unique. Voici notre tableau :

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

La contrainte CHECK est une règle selon laquelle un solde ne peut jamais descendre en dessous de zéro, que nous utiliserons plus tard. Notez que la syntaxe pour commencer une transaction varie : SQLite et PostgreSQL acceptent BEGIN, MySQL a également START TRANSACTION et SQL Server utilise BEGIN TRANSACTION.

En dehors d’une transaction explicite, la plupart des bases de données utilisent autocommit, où chaque instruction est enregistrée dès qu’elle se termine. Oracle est une exception notable, car une session y reste dans une transaction jusqu’à ce que vous vous engagez.

COMMIT : le rendre permanent

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Jusqu’à l’exécution de COMMIT, les modifications sont provisoires. Une fois le retour réussi, la base de données garantit qu’ils sont enregistrés. Le total des deux comptes est resté à 1 200, ce qui est tout l’intérêt de l’exercice.

ROLLBACK : tout annuler

ROLLBACK annule tout depuis le début de la transaction :

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Une restauration se produit également automatiquement si la connexion est interrompue ou si le serveur tombe en panne avant votre validation, de sorte qu’une transaction à moitié terminée ne survit pas.

Lorsqu’une instruction échoue

Voici un cas plus réaliste. Asha essaie d’en envoyer 5 000, que le compte ne détient pas. Le crédit à Ben s’exécute en premier, puis le débit enfreint la règle CHECK :

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Sans la transaction, Ben aurait conservé les 5 000 et les comptes seraient erronés. Soyez conscient d’un détail important : une instruction défaillante n’annule pas toujours la transaction par elle-même. Dans SQLite et MySQL, par exemple, les instructions antérieures peuvent rester en attente, votre code doit donc émettre la restauration. PostgreSQL marque à la place l’ensemble de la transaction comme abandonnée et refuse toute autre instruction jusqu’à ce que vous annuliez.

Dans le code de l’application, associez toujours la transaction à la gestion des erreurs. Voici l’idée en Python, et notre guide sur la gestion des exceptions Python explique la syntaxe :

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Points de sauvegarde

Un point de sauvegarde marque une position dans une transaction, vous ne pouvez donc annuler qu’une partie du travail :

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Le bonus de 999 a été annulé, tandis que le reste de la transaction s’est poursuivi et engagé. Ceci est utile pour les étapes facultatives d’un long processus.

Propriétés ACIDE expliquées

La cohérence est souvent mal comprise. La base de données applique uniquement les règles que vous déclarez, telles que les contraintes et les clés étrangères. Cela n’empêchera pas votre code de transférer le mauvais montant. La durabilité est généralement obtenue grâce à un journal à écriture anticipée, dans lequel les modifications sont enregistrées en toute sécurité sur le disque avant que la validation ne soit confirmée.

Que se passe-t-il avec les transactions simultanées ?

Nous avons ouvert deux connexions à un fichier SQLite. Le premier a démarré une transaction et mis à jour une ligne de 1 à 2, sans encore s’engager. Voici ce que la deuxième connexion a vu :

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite n’autorise qu’un seul rédacteur à la fois, ce qui est plus simple que les bases de données serveur, où de nombreuses transactions peuvent écrire différentes lignes simultanément. Mais le principe est le même : les autres sessions ne devraient pas voir les modifications non validées dans des paramètres normaux, et les écritures conflictuelles doivent attendre. Lors de notre test, nous avons configuré la deuxième connexion pour qu’elle abandonne immédiatement plutôt que d’attendre, de sorte que l’erreur est apparue immédiatement.

Lire les anomalies

Lorsque les transactions se chevauchent, certains problèmes peuvent survenir. Le standard SQL les définit en termes de ce qu’une transaction peut observer :

Un autre problème est la mise à jour perdue. Deux sessions lisent un solde de 100, chacune ajoute 10 et chacune écrit 110. Une mise à jour a effectivement disparu, alors que la réponse devrait être 120.

Niveaux d’isolement SQL

Les niveaux d’isolement vous permettent d’échanger la rigueur contre la concurrence. Voici ce que le standard SQL autorise à chaque niveau :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

La syntaxe et le comportement exacts dépendent de votre base de données. Les valeurs par défaut diffèrent également : PostgreSQL et SQL Server utilisent la lecture validée, tandis que InnoDB de MySQL utilise la lecture répétable. Certaines bases de données offrent des garanties plus strictes que celles exigées par la norme ou traitent un niveau comme un autre. Dans PostgreSQL, par exemple, la lecture non validée se comporte comme la lecture validée. Lisez toujours la documentation de votre base de données avant de vous fier à un comportement spécifique.

Impasses

Un blocage se produit lorsque deux transactions détiennent chacune un verrou dont l’autre a besoin. La transaction A verrouille la ligne 1 et veut la ligne 2, tandis que la transaction B verrouille la ligne 2 et veut la ligne 1. Aucune des deux ne peut continuer.

Les bases de données telles que PostgreSQL, MySQL et SQL Server le détectent et annulent une transaction avec une erreur. Il s’agit d’un comportement normal et votre application doit y être préparée. Pour réduire les blocages, mettez toujours à jour les lignes dans un ordre cohérent, par exemple en commençant par l’ID de compte le plus bas, gardez les transactions courtes et réessayez lorsque l’une d’entre elles est choisie comme victime.

Meilleures pratiques

• Gardez les transactions courtes. Les transactions longues détiennent des verrous et bloquent les autres utilisateurs.
• N’attendez jamais un utilisateur ou une API lente dans une transaction. Effectuez ce travail avant de commencer ou après.
• Toujours gérer les échecs. Chaque chemin doit se terminer par une validation ou une restauration.
• Ajouter des contraintes. Ils donnent à la base de données de vraies règles à appliquer. Une bonne conception de table est également utile.
• Utilisez de bons index. Des recherches plus rapides signifient que les verrous sont maintenus moins longtemps.
• Soyez prudent avec les nouvelles tentatives. Réessayez en cas de blocage, mais assurez-vous qu’une opération ne peut pas s’appliquer deux fois.
• Testez volontairement la simultanéité. Beaucoup de ces bugs n’apparaissent que sous charge, donc les tests d’intégration de base à eux seuls peuvent ne pas les révéler.

Pour conclure

Les transactions transforment plusieurs étapes risquées en une seule opération sûre, et ACID décrit les garanties qui rendent cette opération digne de confiance. Chaque fois qu’une action commerciale touche plus d’une ligne ou un tableau, demandez-vous ce qui se passerait si elle s’arrêtait à mi-chemin. Si la réponse vous inquiète, enveloppez-la dans une transaction. Pour vous entraîner, ouvrez deux sessions dans une base de données locale et essayez de produire vous-même les anomalies.

Questions fréquentes

Continuez à apprendre