13. Transactions et concurrence

MIRAJ traite chaque instruction sous une transaction. Quand aucune n'a été ouverte explicitement, chaque instruction forme sa propre transaction, validée automatiquement dès qu'elle réussit (mode autocommit, actif par défaut). Une transaction explicite regroupe plusieurs instructions qui doivent réussir ou échouer ensemble.

Ce chapitre décrit :

le contrôle des transactions (START TRANSACTION, COMMIT, ROLLBACK, autocommit) ;
le modèle de concurrence de MIRAJ, une isolation sérialisable sans verrou de ligne ni attente, et ce qu'une application doit en faire ;
les verrous de table explicites (LOCK TABLES / UNLOCK TABLES) ;
les points de sauvegarde (SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT) ;
la logique à trois valeurs (TRUE, FALSE, UNKNOWN) et ses pièges avec NULL.

13.1 Autocommit et transactions explicites#

Autocommit#

Par défaut, autocommit vaut 1 (activé) pour chaque nouvelle session : toute instruction qui modifie des données (INSERT, UPDATE, DELETE, DDL) est validée seule, immédiatement après son exécution. C'est le mode habituel d'une application qui n'a pas besoin de regrouper plusieurs écritures.

SET autocommit = 0;   -- chaque instruction ouvre ou prolonge une transaction, jusqu'au
                       -- prochain COMMIT ou ROLLBACK explicite
SET autocommit = 1;   -- retour au mode par défaut

START TRANSACTION / BEGIN#

START TRANSACTION;
-- équivalent :
BEGIN [WORK];

Options de START TRANSACTION :

START TRANSACTION READ ONLY;              -- transaction déclarée en lecture seule
START TRANSACTION READ WRITE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

READ ONLY déclare l'intention ; une transaction ouverte par START TRANSACTION ou par autocommit = 0 reste, dans tous les cas, soumise aux mêmes règles de concurrence décrites au §13.2.

COMMIT et ROLLBACK#

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE];
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE];

COMMIT valide toutes les écritures de la transaction : elles deviennent visibles aux autres sessions et durables (écrites dans le journal avant d'être publiées).
ROLLBACK annule toutes les écritures de la transaction : chaque ligne modifiée reprend sa valeur d'avant la transaction.
AND CHAIN ouvre immédiatement une nouvelle transaction avec les mêmes caractéristiques (READ ONLY/READ WRITE) que celle qui vient de se terminer.
RELEASE termine aussi la session après la validation ou l'annulation.

START TRANSACTION;
INSERT INTO commandes (client_id, montant) VALUES (42, 199.90);
UPDATE clients SET solde = solde - 199.90 WHERE id = 42;
COMMIT;

Si une erreur survient au milieu d'une transaction (contrainte violée, conflit de concurrence, etc.), aucune des instructions déjà exécutées n'est annulée automatiquement par MIRAJ : c'est à l'application de décider, selon le code d'erreur, si elle continue, corrige et retente l'instruction en cause, ou annule (ROLLBACK) toute la transaction. Le cas du conflit de concurrence (erreur 1213) est décrit ci-dessous et demande systématiquement un ROLLBACK suivi d'une nouvelle tentative.

13.2 Modèle de concurrence#

MIRAJ isole les transactions au niveau le plus strict de la norme SQL, SERIALIZABLE : tout se passe comme si les transactions s'exécutaient l'une après l'autre, jamais entremêlées, quel que soit le nombre de sessions actives en même temps. Ce niveau est habituellement obtenu au prix de verrous de ligne qui font attendre les transactions les unes derrière les autres. MIRAJ obtient le même résultat autrement, par contrôle multiversion optimiste : chaque transaction travaille sur son propre instantané des données, sans poser de verrou de ligne. Seule une ligne que le client demande explicitement à tenir (SELECT … FOR UPDATE, voir plus bas) fait attendre les autres.

Ce qu'il faut retenir, du point de vue d'une application cliente :

Situation	Comportement
Un `SELECT` pendant qu'une autre session écrit	ne bloque jamais, ne voit jamais un état à moitié écrit, et n'est jamais annulé à cause de cette écriture
Deux sessions modifient la même ligne en même temps	la première à valider gagne ; la seconde est abandonnée tout de suite (aucune attente)
`SELECT … FOR UPDATE` sur une ligne qu'une autre transaction tient ou modifie	attend la fin de cette transaction, puis lit la valeur validée
Deux transactions qui, prises ensemble, ne pourraient être rejouées dans aucun ordre valide (écritures croisées, `INSERT` qui contredit une lecture par intervalle d'une autre transaction, etc.)	l'une des deux est abandonnée à sa validation
Interblocage (deux `SELECT … FOR UPDATE` qui s'attendraient mutuellement)	détecté aussitôt : celle qui fermerait le cycle reçoit 1213 et est annulée ; l'autre continue

Concrètement :

Les lecteurs ne sont jamais bloqués et ne sont jamais annulés. Un SELECT lit toujours un instantané cohérent, pris à un instant précis, sans se soucier des écritures en cours ailleurs.
Un conflit d'écriture n'attend pas : dès qu'il est détecté, la transaction perdante est abandonnée immédiatement plutôt que mise en file derrière l'autre — sauf si la ligne est tenue par un SELECT … FOR UPDATE : l'écriture attend alors, elle aussi, la fin du détenteur.
Une instruction isolée (mode autocommit) qui entre en conflit est rejouée automatiquement par le moteur, jusqu'à trois fois, avec une attente qui augmente à chaque tentative (50 µs, 200 µs puis 800 µs). L'application ne voit rien dans l'immense majorité des cas.
Une transaction explicite (ouverte par START TRANSACTION ou par autocommit = 0) ne peut pas être rejouée par le moteur : celui-ci n'a pas mémorisé la suite des instructions déjà envoyées par le client. Si elle entre en conflit — la sienne ou celles de ses tentatives d'auto-rejeu épuisées — elle est annulée en entier et le client reçoit l'erreur suivante.

L'erreur 1213#

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Ce code (ER_LOCK_DEADLOCK, SQLSTATE 40001) est le même que celui qu'un serveur transactionnel habituel renvoie pour un interblocage — les pilotes existants le reconnaissent déjà et le signalent comme « rejouez la transaction ». MIRAJ le renvoie pour un vrai interblocage entre lectures verrouillantes, et chaque fois qu'une transaction est annulée par un conflit de concurrence : c'est le signal qu'aucune donnée n'a été perdue ni corrompue, seulement que cette transaction précise doit être recommencée depuis le début.

Ce que doit faire une application qui reçoit 1213 : relancer la transaction entière depuis START TRANSACTION (ou son équivalent applicatif), pas seulement la dernière instruction. Un pseudo-code habituel :

-- Pseudo-code applicatif
tentative := 0
répéter
    tentative := tentative + 1
    essayer
        START TRANSACTION;
        UPDATE comptes SET solde = solde - 100 WHERE id = 1;
        UPDATE comptes SET solde = solde + 100 WHERE id = 2;
        COMMIT;
        sortir de la boucle          -- succès
    intercepter erreur 1213
        ROLLBACK;                    -- par précaution ; la transaction est déjà annulée côté serveur
        si tentative >= 5 alors relancer l'erreur au niveau supérieur
        sinon recommencer la boucle (idéalement après une courte pause aléatoire)

Un virement entre deux comptes est un bon exemple : si deux virements concurrents touchent le même compte, l'un des deux peut recevoir 1213 même si, pris séparément, chacun est correct. Le recommencer suffit, il n'y a rien à corriger dans la logique métier.

Niveau d'isolation. Il n'y en a qu'un, SERIALIZABLE : SET TRANSACTION ISOLATION LEVEL … et la variable transaction_isolation sont acceptés (les outils clients les envoient à la connexion) mais restent sans effet.

Modèle de concurrence du serveur. Ce comportement est celui du modèle par défaut, mvocc (contrôle multiversion optimiste). miraj-server --concurrency pessimistic (ou la clé concurrency de miraj_config.xml) rétablit l'ancien modèle à verrous de ligne, où un écrivain attend le précédent puis échoue en 1205 ; c'est une option temporaire, le temps de comparer les deux modèles, et tout ce chapitre décrit mvocc. information_schema.MIRAJ_CONCURRENCY indique le modèle en service (colonne MODEL).

docs/concurrency.md (interne au dépôt) documente le détail du mécanisme pour qui souhaite l'approfondir : instantanés, validation par ensembles de lecture et de balayage, filigrane de purge des versions.

Ce qui sort de la transaction. Une séquence (NEXTVAL, chapitre 6) n'en dépend pas : une valeur servie n'est jamais rendue par ROLLBACK, et deux transactions qui l'appellent en même temps ne se gênent pas. Les verrous nommés (GET_LOCK, chapitre 9) non plus : ni COMMIT ni ROLLBACK ne les rendent, seule la fin de la session le fait. Les tables disque (ENGINE = Aria, MyISAM, DISK) et les tables à versionnement système (chapitre 11) obéissent aux mêmes règles que les autres tables ; l'historique d'une table versionnée est écrit dans la transaction de la modification et suit son sort.

Mise à jour différée#

Un compteur ou le stock d'un article vendu par toutes les caisses est une ligne que beaucoup de transactions modifient en même temps : avec la seule règle « le premier qui écrit gagne », toutes sauf une recevraient 1213. MIRAJ traite donc à part les UPDATE qui s'y prêtent : un tel UPDATE ne prend pas la ligne ; il est recalculé au COMMIT sur la dernière valeur validée, puis écrit. Deux transactions qui font UPDATE stocks SET qte = qte - 1 WHERE id = 7 valident alors toutes les deux, et le résultat est celui de leur exécution l'une après l'autre, dans l'ordre des COMMIT.

-- qte vaut 100
-- Session A                                   -- Session B
START TRANSACTION;                              START TRANSACTION;
UPDATE stocks SET qte = qte - 1 WHERE id = 7;
                                                UPDATE stocks SET qte = qte - 2 WHERE id = 7;
                                                COMMIT;   -- qte = 98
COMMIT;   -- qte = 97, aucune erreur

Un UPDATE est différé quand il vise une ligne désignée par sa clé (égalités sur toutes les colonnes de la clé primaire ou d'un index UNIQUE), qu'il ne modifie ni une clé, ni une clé étrangère, ni une colonne AUTO_INCREMENT ou BLOB, qu'il n'appelle ni sous-requête ni fonction non déterministe (RAND(), UUID()… ; NOW() et CURDATE() sont admis), et que les déclencheurs de la table s'y prêtent (déclencheurs BEFORE UPDATE qui ne font que calculer NEW.col, déclencheurs AFTER UPDATE qui ne lisent pas les colonnes modifiées). Une table à versionnement système (chapitre 11) n'est jamais concernée. Sinon, il prend la ligne comme décrit plus haut ; le résultat est le même, seule la concurrence change. ROW_COUNT() vaut 1, la transaction relit sa propre valeur, et les déclencheurs AFTER UPDATE s'exécutent tout de suite.

Ce qui change pour l'application :

Erreurs au COMMIT. Si la ligne a été supprimée entre-temps, si la condition du WHERE ne tient plus, ou si une autre transaction tient encore la ligne, le COMMIT échoue en 1213 : la transaction est à rejouer, comme toute autre. Si le recalcul lui-même échoue (valeur hors limites, 1264 ; colonne NOT NULL, 1048 ; contrainte CHECK, 4025…), le COMMIT échoue avec ce code ; dans les deux cas, toute la transaction est annulée.
Lire avant d'écrire. Une transaction qui lit la ligne (par exemple pour contrôler un stock disponible) avant de la mettre à jour reste exposée au 1213 si une autre transaction modifie cette ligne avant son COMMIT : ce qu'elle a lu n'est plus vrai. Un UPDATE qui porte lui-même sa condition (… WHERE id = 7 AND qte >= 1) n'échoue, lui, que si la condition ne tient plus au COMMIT.
Réglage. SET deferred_update = OFF (session), SET GLOBAL deferred_update = OFF (sessions suivantes) ou miraj-server --deferred-update OFF rétablissent la prise immédiate de la ligne ; ON est la valeur par défaut. information_schema.MIRAJ_DEFERRED_UPDATE compte les UPDATE différés, les échecs au COMMIT par cause et les UPDATE qui n'ont pas pu l'être (voir 15.6.2).

Lectures verrouillantes : `SELECT … FOR UPDATE`#

SELECT … FOR UPDATE (et FOR SHARE, LOCK IN SHARE MODE) lit les lignes et les tient jusqu'à la fin de la transaction : aucune autre transaction ne peut les écrire ni les tenir à son tour (FOR SHARE : les autres lecteurs FOR SHARE restent admis). C'est le moyen d'empêcher la mise à jour perdue d'un « lire puis écrire » :

START TRANSACTION;
SELECT solde FROM comptes WHERE id = 1 FOR UPDATE;   -- la ligne est tenue
-- ... contrôle applicatif du solde ...
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;                                               -- la ligne est rendue

Une ligne déjà tenue est attendue, comme chez le serveur de référence : un autre SELECT … FOR UPDATE, ou un UPDATE / DELETE de cette ligne, attend la fin de la transaction qui la tient, puis s'exécute sur la valeur qu'elle a validée. Deux transactions qui prennent toujours leurs lignes dans le même ordre ne reçoivent donc aucune erreur.
Interblocage : si l'attente fermait un cycle (A tient 1 et attend 2, B tient 2 et demande 1), la transaction qui le fermerait reçoit tout de suite 1213 et est annulée ; l'autre continue.
Délai : au-delà de innodb_lock_wait_timeout secondes (50 par défaut ; une seule valeur avec lock_wait_timeout), l'instruction échoue en 1205 ; seule l'instruction échoue, la transaction reste ouverte avec ce qu'elle tenait. SET SESSION innodb_lock_wait_timeout = 5 change ce délai.
NOWAIT : 1205 tout de suite si une ligne est tenue. WAIT n : attente bornée à n secondes. SKIP LOCKED : les lignes tenues sont écartées du résultat (le motif « file de travaux »).
KILL QUERY pendant l'attente : 1317 ; KILL CONNECTION : 1927. SHOW PROCESSLIST montre la session dans l'état Waiting for row lock.
Une ligne modifiée et validée par une autre transaction depuis le début de la sienne n'est pas une erreur : MIRAJ vérifie que rien de ce que la transaction a déjà lu n'a changé, puis la fait continuer comme si elle avait commencé à cet instant, sur la valeur validée. Si une ligne qu'elle avait déjà lue a changé, c'est un vrai conflit : 1213.
Dans une procédure stockée (SELECT … INTO … FOR UPDATE, curseurs), seule l'instruction du corps attend : ce que la procédure a déjà fait n'est pas refait.

DDL et verrous de table face à une transaction ouverte#

Une transaction explicite qui a lu, écrit ou tenu une table protège la définition de cette table jusqu'à sa fin. Un DDL sur cette table (ALTER TABLE, TRUNCATE, DROP TABLE, RENAME TABLE, CREATE OR REPLACE TABLE, DROP DATABASE) lancé par une autre session pendant ce temps est refusé tout de suite (après rejeu automatique côté moteur : erreur 1213 au bout du compte) plutôt que mis en attente ; la transaction ouverte n'est pas touchée. De même, LOCK TABLES t WRITE (ou READ selon le cas) est refusé immédiatement en 1213 si une transaction ouverte tient déjà t de façon incompatible. Aucune de ces situations ne fait attendre qui que ce soit : c'est le premier arrivé qui gagne.

13.3 LOCK TABLES / UNLOCK TABLES#

À la différence des transactions ci-dessus, LOCK TABLES est un verrou explicite, posé à la demande du client ; avec SELECT … FOR UPDATE, c'est la seule situation où une instruction attend réellement une autre session (jusqu'à lock_wait_timeout secondes, 50 par défaut, puis erreur 1205).

LOCK TABLES stocks WRITE, commandes READ;
-- ... instructions qui utilisent stocks (lecture/écriture) et commandes (lecture seule) ...
UNLOCK TABLES;

WRITE réserve la table à la session qui la verrouille (lecture et écriture) ; les autres sessions qui en ont besoin attendent jusqu'à lock_wait_timeout, puis échouent en 1205 (ER_LOCK_WAIT_TIMEOUT) — seule leur instruction échoue, leur transaction reste ouverte.
READ autorise les lectures concurrentes des autres sessions mais bloque leurs écritures de la même façon.
UNLOCK TABLES rend les tables verrouillées par la session ; une fermeture de session ou une déconnexion les rend aussi.
KILL QUERY sur une session en attente d'un verrou de table donne 1317 ; KILL CONNECTION, 1927.

Cas d'usage typique : un traitement par lots qui doit voir une ou plusieurs tables figées pendant toute sa durée sans passer par une transaction explicite complète — par exemple un export cohérent de plusieurs tables liées, ou une opération de maintenance applicative qui doit exclure toute autre écriture le temps de son passage.

SET [SESSION] lock_wait_timeout = n; change le délai d'attente de la session (DEFAULT revient à la valeur du serveur, réglée par --lock-wait-timeout) ; SET GLOBAL lock_wait_timeout = n; change celui des sessions suivantes.

13.4 SAVEPOINT#

Les points de sauvegarde se comportent comme dans MariaDB :

SAVEPOINT nom pose un point de restauration dans la transaction ouverte ; un point de même nom (sans distinction de casse) est remplacé. Avec autocommit = 0, il ouvre la transaction implicite. En auto-validation, hors transaction, il est sans effet : il ne survit qu'à l'instruction.
ROLLBACK [WORK] TO [SAVEPOINT] nom défait tout ce que la transaction a écrit depuis la pose du point (lignes, UPDATE différés, notifications), retire les points posés ensuite, et garde ce point ainsi que la transaction ouverte. Les verrous pris depuis restent tenus jusqu'à la fin de la transaction, comme InnoDB ; le compteur AUTO_INCREMENT n'est pas restauré.
RELEASE SAVEPOINT nom retire le point et ceux posés après lui, sans rien défaire.
COMMIT et ROLLBACK effacent tous les points de la transaction.

Un nom inconnu renvoie :

ERROR 1305 (42000): SAVEPOINT nom does not exist

Édition Cluster : SAVEPOINT et ROLLBACK TO SAVEPOINT sont refusés (9041) dans une transaction qui écrit les partitions d'un autre nœud.

13.5 Logique à trois valeurs (NULL dans les conditions)#

MIRAJ suit la norme SQL : une condition ne vaut pas seulement TRUE ou FALSE, mais peut aussi valoir UNKNOWN dès qu'elle compare une valeur NULL. Une clause WHERE, HAVING, ON ou une condition de CASE ne retient une ligne que si sa condition vaut TRUE — UNKNOWN est traité comme FALSE pour le filtrage, mais reste distinct de FALSE dans le résultat d'une expression logique.

Piège n° 1 : comparer avec NULL#

SELECT * FROM clients WHERE email = NULL;      -- ne renvoie JAMAIS de ligne, même si email est NULL
SELECT * FROM clients WHERE email IS NULL;     -- la forme correcte
SELECT * FROM clients WHERE email IS NOT NULL; -- l'inverse

colonne = NULL vaut toujours UNKNOWN, jamais TRUE, quelle que soit la valeur de colonne : NULL représente une valeur inconnue, et on ne peut pas savoir si elle est égale à une autre valeur inconnue.

Piège n° 2 : NOT sur une condition UNKNOWN#

SELECT * FROM produits WHERE NOT (prix_promo > 10);

Si prix_promo est NULL pour une ligne, prix_promo > 10 vaut UNKNOWN, et NOT UNKNOWN vaut encore UNKNOWN (pas TRUE) : la ligne n'est pas renvoyée, alors qu'on pourrait s'attendre à ce que « l'inverse d'une condition qui échoue » ramène la ligne.

Piège n° 3 : NULL dans IN / NOT IN#

SELECT * FROM commandes WHERE client_id NOT IN (1, 2, NULL);

Dès qu'un NULL figure dans la liste d'un NOT IN, le résultat est UNKNOWN pour toute ligne (y compris celles dont client_id ne vaut ni 1 ni 2) : la requête ne renvoie jamais aucune ligne. IN avec un NULL dans la liste peut, lui, encore renvoyer TRUE pour une valeur qui correspond réellement à l'un des autres éléments, mais rend UNKNOWN (et non FALSE) pour les autres. Préférer filtrer les NULL de la liste, ou ajouter AND client_id IS NOT NULL selon l'intention voulue.

Piège n° 4 : agrégats et NULL#

SELECT COUNT(*), COUNT(remise) FROM ventes;

COUNT(*) compte toutes les lignes ; COUNT(colonne) ne compte que les lignes où colonne n'est pas NULL. Les fonctions SUM, AVG, MIN, MAX ignorent silencieusement les valeurs NULL (elles ne les traitent ni comme zéro ni comme une erreur) ; SUM sur une colonne entièrement NULL (ou sans ligne) rend NULL, pas 0.

Piège n° 5 : jointure et NULL#

Une clause ON t1.a = t2.a ne rapproche jamais deux lignes dont a vaut NULL de part et d'autre — comme pour = en général, NULL = NULL vaut UNKNOWN. C'est voulu : deux valeurs inconnues ne sont pas réputées égales.

Voir aussi#

docs/concurrency.md (dépôt) pour le détail interne du contrôle multiversion optimiste.
Chapitre 10, « Comptes et privilèges », pour l'authentification et les droits d'un compte.
Chapitre 11, « Administration du serveur », pour --lock-wait-timeout et les autres options de démarrage.