Leçon 8.4 · Temps de lecture : ~10 min

Une transaction SQL est un groupe d'instructions que la base de données exécute comme une seule unité : soit toutes prennent effet, soit aucune. Dans cette leçon, vous verrez START TRANSACTION, COMMIT et ROLLBACK, comment SAVEPOINT annule une partie d'une transaction, ce qui se passe en cas d'erreur et contre quels problèmes protègent les niveaux d'isolation quand plusieurs utilisateurs modifient les mêmes données.

Transactions SQL : COMMIT, ROLLBACK et niveaux d'isolation

Dans les leçons précédentes, vous avez modifié des données avec INSERT, UPDATE et DELETE, une instruction à la fois. Les opérations réelles en demandent généralement plusieurs : louer un film dans Sakila, c'est ajouter une ligne dans rental et une ligne dans payment. Si la seconde instruction échoue, la première ne doit pas rester seule.

Les bases d'exemple de SQLtest sont en lecture seule : ces instructions y renvoient une erreur de permission. Les exemples utilisent le schéma Sakila ; ils ont été vérifiés sur MySQL 8.0 et PostgreSQL 17.

Qu'est-ce qu'une transaction ?

Une transaction transforme plusieurs modifications en une seule opération. La base de données garantit les quatre propriétés ACID, vues dans la leçon 1.3 :

• Atomicité : toutes les instructions de la transaction prennent effet, ou aucune.
• Cohérence : après la transaction, toutes les contraintes sont toujours respectées.
• Isolation : les transactions concurrentes ne voient pas les modifications non terminées des autres.
• Durabilité : une fois validées, les modifications survivent à une panne ou à une coupure de courant.

L'exemple classique est le virement : débiter un compte et créditer l'autre. Sans transaction, une panne entre les deux étapes fait disparaître de l'argent.

START TRANSACTION, COMMIT et ROLLBACK

START TRANSACTION;

-- statements that change data

COMMIT;    -- save all changes
-- or
ROLLBACK;  -- undo all changes

• START TRANSACTION (ou BEGIN) ouvre une transaction.
• COMMIT rend toutes ses modifications permanentes et visibles par les autres utilisateurs.
• ROLLBACK annule toutes les modifications faites depuis le début de la transaction.

Les mots-clés varient un peu selon les bases de données :

Exemple : louer un film et encaisser le paiement

START TRANSACTION;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES ('2005-08-24 10:00:00', 1, 1, 1);

INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, LAST_INSERT_ID(), 0.99, '2005-08-24 10:00:00');

COMMIT;

Résultat : les deux lignes sont enregistrées ensemble. Dans MySQL, LAST_INSERT_ID() renvoie le rental_id qui vient d'être généré ; PostgreSQL utilise INSERT ... RETURNING rental_id pour cela.

Exemple : annuler une erreur

START TRANSACTION;

UPDATE film
SET rental_rate = rental_rate * 2;

SELECT SUM(rental_rate) FROM film;  -- the new prices, visible inside the transaction

ROLLBACK;

Résultat : après ROLLBACK, tous les prix redeviennent ce qu'ils étaient. Avant COMMIT, les autres utilisateurs ne voient pas les prix doublés (sauf au niveau READ UNCOMMITTED décrit plus bas).

Autocommit : chaque instruction est une transaction

Si vous n'ouvrez pas de transaction, la base de données en utilise quand même une : chaque instruction s'exécute dans sa propre transaction et est validée automatiquement. Ce mode s'appelle l'autocommit ; il est activé par défaut dans MySQL, PostgreSQL et SQL Server.

SELECT @@autocommit;  -- MySQL: 1 means autocommit is on

Un UPDATE isolé sans START TRANSACTION ne peut donc pas être annulé par ROLLBACK : il a été validé dès sa fin. START TRANSACTION suspend l'autocommit jusqu'au COMMIT ou au ROLLBACK.

SAVEPOINT : annuler une partie d'une transaction

Un point de sauvegarde (savepoint) marque un endroit dans une transaction. ROLLBACK TO SAVEPOINT n'annule que les modifications faites après lui ; la transaction reste ouverte.

START TRANSACTION;

UPDATE film SET rental_rate = 1.99 WHERE film_id = 1;

SAVEPOINT before_second;

UPDATE film SET rental_rate = 9.99 WHERE film_id = 2;

ROLLBACK TO SAVEPOINT before_second;

COMMIT;

Résultat : le film 1 reçoit le nouveau prix 1.99 ; la modification du film 2 est annulée et il garde son ancien prix.

Les points de sauvegarde sont utiles dans les longues opérations où une étape peut échouer et où l'on veut la relancer sans perdre le travail déjà fait.

Que se passe-t-il quand une instruction échoue ?

C'est là que les bases de données se comportent différemment, et c'est une source fréquente de bugs.

MySQL et MariaDB n'annulent que l'instruction en échec. La transaction reste ouverte, et COMMIT enregistre tout ce qui a réussi :

START TRANSACTION;

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (1, 1, 2.99, '2005-08-24 10:00:00');   -- succeeds

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (999, 1, 4.99, '2005-08-24 10:00:00'); -- fails: customer 999 does not exist

COMMIT;

Résultat dans MySQL : la seconde insertion échoue sur une erreur de clé étrangère, mais COMMIT enregistre quand même le premier paiement. Pour obtenir le « tout ou rien », l'application doit vérifier les erreurs et exécuter ROLLBACK elle-même.

PostgreSQL marque toute la transaction comme échouée après une erreur. Chaque instruction suivante est rejetée avec « current transaction is aborted », et COMMIT effectue en réalité une annulation : rien n'est enregistré.

Les instructions qui valident implicitement

Dans MySQL, MariaDB et Oracle, les instructions DDL comme CREATE TABLE, ALTER TABLE et DROP TABLE valident la transaction en cours avant de s'exécuter. Un ROLLBACK ultérieur ne peut pas annuler les modifications faites avant elles :

START TRANSACTION;

UPDATE film SET rental_rate = 5.99 WHERE film_id = 1;

CREATE TABLE film_backup (film_id INT);  -- implicit COMMIT here

ROLLBACK;  -- too late: the UPDATE is already saved

Résultat dans MySQL : le nouveau prix 5.99 reste. PostgreSQL et SQL Server exécutent le DDL dans la transaction : là, ROLLBACK annule à la fois l'UPDATE et le CREATE TABLE.

Problèmes de concurrence : lectures sales, non répétables et fantômes

Quand plusieurs transactions travaillent en même temps sur les mêmes lignes, une transaction peut voir des données dans un état où elle ne devrait pas les voir. La norme SQL nomme trois anomalies de lecture :

Une lecture sale étape par étape :

Les niveaux d'isolation des transactions

Le niveau d'isolation détermine contre quelles anomalies une transaction est protégée. Plus l'isolation est forte, moins il y a de surprises, mais plus les transactions s'attendent mutuellement.

Le niveau par défaut dépend de la base de données :

• MySQL et MariaDB (InnoDB) : REPEATABLE READ. Un SELECT simple lit un instantané pris lors de la première lecture de la transaction ; en pratique, il ne voit donc pas non plus de fantômes.
• PostgreSQL, SQL Server, Oracle : READ COMMITTED. PostgreSQL traite READ UNCOMMITTED comme READ COMMITTED, si bien que les lectures sales n'y arrivent jamais ; Oracle ne prend en charge que READ COMMITTED et SERIALIZABLE.

Pour changer de niveau :

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- for all following transactions of this connection (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Dans PostgreSQL, SET TRANSACTION ISOLATION LEVEL ... s'écrit juste après BEGIN. Au niveau SERIALIZABLE, la base peut annuler l'une de deux transactions en conflit avec une erreur de sérialisation ; l'application doit alors la relancer.

Règles pratiques

• Gardez les transactions courtes : tant qu'une transaction est ouverte, elle détient des verrous que d'autres utilisateurs attendent. N'attendez jamais une saisie de l'utilisateur dans une transaction.
• Vérifiez le résultat de chaque instruction et exécutez ROLLBACK en cas d'erreur, surtout dans MySQL, qui ne le fait pas à votre place.
• Ne mélangez pas DDL et transactions sur les données dans MySQL : le DDL valide implicitement.
• Deux transactions qui verrouillent les mêmes lignes dans l'ordre inverse peuvent se bloquer mutuellement : c'est un interblocage (deadlock). La base de données annule l'une d'elles, et l'application doit la relancer.

Points clés :

• Une transaction regroupe des instructions en une seule unité : COMMIT les enregistre toutes, ROLLBACK les annule toutes.
• Sans transaction explicite, chaque instruction est validée immédiatement (autocommit).
• SAVEPOINT et ROLLBACK TO SAVEPOINT n'annulent qu'une partie d'une transaction.
• Après une erreur, MySQL garde la transaction ouverte, alors que PostgreSQL l'interrompt ; dans MySQL, exécutez ROLLBACK vous-même.
• Dans MySQL, les instructions DDL valident implicitement la transaction en cours.
• Les niveaux d'isolation protègent contre les lectures sales, non répétables et fantômes ; le défaut est REPEATABLE READ dans MySQL et READ COMMITTED dans PostgreSQL, SQL Server et Oracle.

Questions fréquentes

Qu'est-ce qu'une transaction en SQL ?

Une transaction est un groupe d'instructions SQL que la base de données exécute comme une seule unité : soit toutes les modifications sont enregistrées avec COMMIT, soit toutes sont annulées avec ROLLBACK. Les transactions gardent les données cohérentes quand une opération demande plusieurs instructions ou quand plusieurs utilisateurs modifient les données en même temps.

Quelle est la différence entre COMMIT et ROLLBACK ?

COMMIT termine la transaction et rend ses modifications permanentes et visibles par les autres utilisateurs. ROLLBACK termine la transaction et annule toutes ses modifications, en ramenant les données à l'état d'avant START TRANSACTION.

Peut-on annuler après un COMMIT ?

Non. Après COMMIT, les modifications sont permanentes ; pour revenir en arrière, il faut une nouvelle instruction qui rétablit les données, ou une sauvegarde. Il en va de même pour les instructions exécutées en mode autocommit.

Quel est le niveau d'isolation par défaut ?

REPEATABLE READ dans MySQL et MariaDB avec InnoDB ; READ COMMITTED dans PostgreSQL, SQL Server et Oracle.

Questions d'entretien

Que signifie ACID pour une transaction ?

Atomicité : tout ou rien. Cohérence : les contraintes sont respectées avant et après. Isolation : les transactions concurrentes ne voient pas les modifications non validées des autres. Durabilité : les données validées survivent aux pannes.

Qu'est-ce qu'une lecture sale, et quel niveau d'isolation l'empêche ?

Une lecture sale consiste à lire une modification non validée d'une autre transaction, qui peut ensuite être annulée. READ COMMITTED et tous les niveaux plus forts l'empêchent.

Quelle est la différence entre une lecture non répétable et une lecture fantôme ?

Une lecture non répétable est une valeur modifiée dans une ligne déjà lue ; une lecture fantôme est une nouvelle ligne qui apparaît dans le résultat d'une requête répétée avec la même condition.

Que se passe-t-il dans MySQL si une instruction échoue dans une transaction ?

Seule cette instruction est annulée ; la transaction reste ouverte et COMMIT enregistre les instructions réussies. Pour que toute l'opération soit atomique, l'application doit intercepter l'erreur et exécuter ROLLBACK.