9. 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) ;
• l'état de SAVEPOINT ;
• la logique à trois valeurs (TRUE, FALSE, UNKNOWN) et ses pièges avec NULL.

9.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 §9.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.

9.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 jamais poser de verrou de ligne ni faire attendre personne.

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

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 jamais : 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.
• 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 ne produit jamais d'interblocage à proprement parler (aucune transaction n'attend jamais une autre) mais renvoie ce code chaque fois qu'une transaction est annulée par un conflit de concurrence à sa validation : 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.

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.

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). 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 11.6.2).

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.

9.3 LOCK TABLES / UNLOCK TABLES#

À la différence des transactions ci-dessus, LOCK TABLES est un verrou explicite, posé à la demande du client, et 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.

9.4 SAVEPOINT#

SAVEPOINT nom est accepté par l'analyseur syntaxique, mais sans aucun effet : il ne crée pas de point de restauration intermédiaire dans la transaction.

ROLLBACK TO SAVEPOINT et RELEASE SAVEPOINT ne sont pas pris en charge dans cette version et renvoient l'erreur :

ERROR 1235: This version of MIRAJ doesn't yet support 'SAVEPOINT'

Une transaction ne peut donc être annulée que dans son ensemble (ROLLBACK), jamais partiellement jusqu'à un point intermédiaire. Une application qui a besoin d'annuler seulement une partie de son travail doit, pour l'instant, structurer sa logique en transactions plus courtes plutôt que s'appuyer sur des points de sauvegarde intermédiaires.

9.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.