#Transactions : tout ou rienCopier le lien

Une transaction empaquette plusieurs opérations en une unité indivisible : soit toutes s'appliquent, soit aucune. Derrière ce contrat simple se cache l'acronyme ACID, quatre garanties que tout SGBD sérieux offre et que tout développeur doit savoir décrire.

#Prérequis et objectifsCopier le lien

• Prérequis : Modélisation (contraintes d'intégrité) et SELECT (le playground SQL).
• Énoncer ACID et rattacher chaque lettre à un mécanisme concret (journal, verrous, snapshots).
• Définir planification sérialisable et décider, graphe de précédence en main, si une planification l'est.
• Décrire les verrous partagés et exclusifs, le protocole V2P, et détecter un interblocage par le graphe des attentes.
• Choisir un niveau d'isolation selon les anomalies qu'on accepte ou refuse.
• Reconnaître lecture sale, lecture non répétable, fantôme et write-skew sur un scénario.

#Qu'est-ce qu'une transaction ?Copier le lien

Une transaction est une action ou une suite d'actions demandée par un seul utilisateur ou programme d'application, qui lit ou met à jour le contenu de la base de données. C'est une unité logique de travail sur la base de données.

Exemple typique du cours : deux tables Personnel(numPersonnel, prénom, nom, fonction, salaire) et PropriétéALouer(numPropriété, type, pièces, numPropriétaire, numPersonnel). Vendre une propriété, c'est créditer un salaire, changer le propriétaire, inscrire une commission : trois écritures qui n'ont de sens qu'ensemble.

Deux issues seulement, et c'est tout l'intérêt du mécanisme :

• Succès : la transaction est dite validée ou confirmée (committed) et la base atteint un nouvel état cohérent.
• Échec : la transaction est avortée (aborted). La base doit retrouver l'état cohérent qu'elle avait avant le début de la transaction ; une transaction qui subit cette réinitialisation est dite annulée (rolled back).

#ACID, lettre par lettreCopier le lien

Le sigle date de 1983 (Haerder et Reuter) et n'a pas bougé depuis. Quatre garanties qu'un SGBD sérieux offre et qu'un développeur doit savoir décrire :

• Atomicité : une transaction forme une unité indivisible, soit exécutée dans sa totalité, soit annulée dans sa totalité. Une transaction partielle n'existe pas : en cas d'erreur ou de crash au milieu, les modifications déjà effectuées sont annulées.
• Cohérence : une transaction transforme la base d'un état cohérent en un autre état cohérent ; les contraintes (clés, CHECK, FK) ne peuvent être violées durablement.
• Isolation : les transactions s'exécutent de manière indépendante les unes des autres ; le degré exact d'indépendance dépend du niveau choisi.
• Durabilité : les effets d'une transaction complètement achevée (validée par un COMMIT) sont inscrits de manière durable dans la base et ne peuvent subir de perte à la suite d'une défaillance ultérieure. C'est le journal (WAL, write-ahead log) qui l'assure : les modifications sont notées sur disque avant d'être appliquées.

Le quotidien du développeur : BEGIN, COMMIT, ROLLBACK.

1begin;2insert into users values (3, 'Charlie');3rollback; -- la ligne disparaît : rien ne s'est passé4 5begin;6insert into users values (3, 'Charlie');7commit;   -- la ligne est durable

#Animation : cycle de vie et journalCopier le lien

#Playground : ressentir l'atomicitéCopier le lien

Le playground exécute le script dans SQLite, dans votre navigateur. Exécutez une première fois : la table contient Zed après l'insert, mais le rollback final l'efface, et le second select le prouve (2 lignes, la base initiale Alice et Bob). Remplacez rollback par commit et ré-exécutez avec Nouvelle base au préalable : Zed persiste.

C'est l'atomicité en action : trois lignes de script, un état final identique à l'état initial.

#Contrôle de concurrence : le cœur du sujetCopier le lien

L'un des principaux objectifs du développement d'une base de données est de permettre à plusieurs utilisateurs d'accéder simultanément à des données partagées. Le contrôle de concurrence est le procédé qui gère ces opérations simultanées tout en interdisant les interférences entre elles.

Trois problèmes classiques, chacun avec son chiffre caractéristique :

#1. La mise à jour perdueCopier le lien

Une opération de modification apparemment achevée avec succès par un utilisateur est écrasée par un autre utilisateur. Deux transactions lisent le même solde, calculent chacune une nouvelle valeur, et la seconde écrit sans tenir compte de la première : le solde final vaut 90 au lieu de 190 — une des deux écritures a disparu.

#2. La dépendance non validée (dirty read)Copier le lien

Une transaction est autorisée à voir immédiatement les résultats d'une autre transaction, avant que celle-ci n'ait été validée. Si la première est ensuite avortée, la seconde a travaillé sur une valeur qui n'a jamais existé : le solde finit à 190 au lieu de 90.

#3. L'analyse incohérenteCopier le lien

Une transaction lit plusieurs valeurs de la base, mais une autre transaction en modifie certaines pendant l'exécution de la première. Une transaction qui additionne un solde x et un solde y peut lire x avant un transfert et y après, et obtenir 185 au lieu de 175 : elle a lu deux états différents de la base dans la même requête.

#Capacité de sérialisationCopier le lien

La capacité de sérialisation (serializability) est l'outil qui permet d'identifier les exécutions dont on peut garantir la cohérence. Elle repose sur quatre définitions emboîtées.

Planification (schedule) : une séquence d'opérations d'un ensemble de $n$ transactions $T_1, T_2, \ldots, T_n$ qui préserve l'ordre des opérations dans chacune des transactions.

Planification sérielle : une planification où les opérations de chaque transaction sont exécutées de manière consécutive, sans aucune opération interfoliée d'autres transactions.

Planification non sérielle : une planification où les opérations d'un ensemble de transactions sont exécutées de manière interfoliée.

Planification sérialisable : une planification non sérielle qui produit les mêmes résultats qu'une exécution sérielle.

Une planification sérielle est donc toujours correcte — par construction, elle revient à exécuter les transactions l'une après l'autre. Mais elle interdit tout parallélisme. Tout l'enjeu est de trouver les planifications non sérielles qui sont équivalentes à une planification sérielle : elles donnent le bon résultat tout en allant plus vite.

Le critère d'équivalence utilisé est la capacité de sérialisation des conflits, fondée sur trois observations :

• si deux transactions ne font que lire des données, elles n'entrent pas en conflit et leur ordre est sans importance ;
• si deux transactions lisent ou écrivent des données complètement différentes, leur ordre est sans importance ;
• si une transaction écrit dans des données et qu'une autre lit ou écrit ces mêmes données, alors l'ordre de leur exécution importe.

Seul le troisième cas crée un conflit. C'est ce qui rend le test praticable : on n'a besoin de comparer que les opérations en conflit.

#Le graphe de précédenceCopier le lien

Pour une planification P, on construit un graphe dirigé $G = (N, F)$ :

• créer un nœud pour chaque transaction ;
• créer une flèche $T_i \to T_j$ si $T_j$ lit la valeur d'un élément écrit par $T_i$ ;
• créer une flèche $T_i \to T_j$ si $T_j$ écrit une valeur dans un élément après qu'il a été lu par $T_i$ ;
• créer une flèche $T_i \to T_j$ si $T_j$ écrit une valeur dans un élément après qu'il a été écrit par $T_i$.

Théorème. Si une flèche $T_i \to T_j$ existe dans le graphe de précédence pour P, alors dans toute planification sérielle S équivalente à P, $T_i$ doit apparaître avant $T_j$. Si le graphe de précédence contient un cycle, la planification n'est pas sérialisable en vue de résoudre les conflits.

Le test tient donc en une phrase : construire le graphe, chercher un cycle. Graphe acyclique → planification sérialisable, et l'ordre topologique du graphe donne une planification sérielle équivalente. Cycle → planification à rejeter.

Deux réflexes qui font gagner du temps :

• deux transactions qui ne font que lire la même donnée ne créent aucune flèche (cas 1) ;
• un cycle n'exige pas qu'une donnée soit contestée par les deux transactions. Il suffit que T1 affecte T2 sur une donnée et que T2 affecte T1 sur une autre donnée : les deux flèches se croisent et forment un cycle, alors qu'aucun élément n'a été écrit deux fois. C'est le cas le plus souvent raté à l'écrit.

#Exemple guidé : deux planificationsCopier le lien

Planification a. lecture(T1, solde_x), lecture(T2, solde_x), écriture(T1, solde_x), écriture(T2, solde_x), validation(T1), validation(T2).

On écrit d'abord les temps de chaque transaction : chaque action correspond à un temps.

Deux flèches se dessinent, et il faut bien les deux :

• T1 écrit solde_x au temps t3, puis T2 écrit solde_x au temps t4 : T1 affecte T2, flèche $T_1 \to T_2$ (écriture après écriture).
• T2 lit solde_x au temps t2, puis T1 écrit solde_x au temps t3 : T2 affecte T1, flèche $T_2 \to T_1$ (écriture après lecture).

Le graphe est cyclique : la planification n'est pas sérialisable. Intuitivement, T2 a lu une valeur que T1 a ensuite écrasée — aucune exécution sérielle ne peut reproduire cet enchaînement.

Planification b. lecture(T1, solde_x), lecture(T2, solde_y), écriture(T3, solde_x), écriture(T1, solde_y), validation(T1), validation(T2), validation(T3).

• T3 écrit solde_x au temps t3, or T1 l'a lu au temps t1 : flèche $T_1 \to T_3$.
• T1 écrit solde_y au temps t4, or T2 l'a lu au temps t2 : flèche $T_2 \to T_1$.

Le graphe est acyclique : la planification est sérialisable. L'ordre topologique $T_2, T_1, T_3$ donne la planification sérielle équivalente.

Notez le piège : dans la planification b, T1 et T2 ne se disputent aucune donnée directement (l'une lit x, l'autre lit y) ; c'est T1 qui touche aux deux. Un graphe de précédence se lit sur les données, jamais sur les transactions en bloc.

#Les trois familles de techniquesCopier le lien

Il existe plusieurs techniques de contrôle de concurrence qui permettent d'exécuter des transactions en parallèle en toute sécurité, à condition d'imposer certaines contraintes :

1. les méthodes de verrouillage — on bloque l'accès aux données en cours d'utilisation ;
2. les méthodes d'estampillage — on horodate les transactions et on rejette celles qui violent l'ordre des estampilles ;
3. les méthodes optimistes — on laisse faire, puis on valide en vérifiant qu'aucun conflit n'est survenu.

Les trois sections qui suivent détaillent la première, celle que le cours développe.

#Verrous partagés et exclusifsCopier le lien

Verrouillage : une procédure employée pour contrôler les accès concurrents aux données. Lorsqu'une transaction accède à une base de données, un verrou est susceptible de bloquer l'accès à d'autres transactions pour éviter de faux résultats.

Verrou partagé : si une transaction dispose d'un verrou partagé sur une donnée, elle peut lire la donnée mais pas la modifier.

Verrou exclusif : si une transaction dispose d'un verrou exclusif sur une donnée, elle peut lire et modifier cette donnée.

Les règles d'usage :

• toute transaction devant accéder à une donnée verrouille d'abord la donnée, en demandant soit un verrouillage partagé, soit un verrouillage exclusif ;
• si la donnée n'est pas déjà verrouillée par une autre transaction, le verrou est accordé ;
• si la donnée est déjà verrouillée au moment de la demande, le SGBD détermine si la demande est compatible avec le verrou actuel. Si la transaction demande un verrou partagé alors qu'un verrou partagé est déjà posé, la requête peut être satisfaite et le verrou accordé ; dans le cas contraire, la transaction demanderesse doit attendre que le verrou se libère ;
• une transaction qui détient un verrou le conserve tant qu'elle ne le libère pas explicitement pendant l'exécution, ou implicitement lorsqu'elle se termine (par une annulation ou une validation). Ce n'est que lorsqu'un verrou exclusif est libéré que les effets de l'opération d'écriture qui a motivé le verrou deviennent visibles aux autres transactions.

D'où la table de compatibilité, qu'il faut savoir reconstruire :

Deux lecteurs cohabitent ; dès qu'un écrivain est présent, tout le monde attend.

#Verrouillage en deux phases (V2P)Copier le lien

Une transaction suit le protocole de verrouillage en deux phases si toutes les opérations de verrouillage précèdent la première opération de déverrouillage dans la transaction.

Toute transaction est alors divisible en deux phases :

• une phase de croissance, où elle acquiert tous les verrous mais ne peut en libérer aucun ;
• une phase de résorption, au cours de laquelle elle libère tous les verrous et ne peut plus en obtenir aucun.

Formulé en deux règles : une transaction doit acquérir un verrou sur un élément avant de faire quoi que ce soit sur celui-ci ; une fois qu'elle libère un verrou, elle ne peut plus demander d'autres verrous.

La V2P garantit la sérialisabilité des conflits. Et elle est nécessaire : le cours donne l'exemple d'une planification de verrouillage incorrecte pour le montrer.

1P = { verrou-écriture(T9, soldex), lecture(T9, soldex), écriture(T9, soldex), déverrouillage(T9, soldex),2      verrou-écriture(T10, soldex), lecture(T10, soldex), écriture(T10, soldex), déverrouillage(T10, soldex),3      verrou-écriture(T10, soldey), lecture(T10, soldey), écriture(T10, soldey), déverrouillage(T10, soldey), validation(T10),4      verrou-écriture(T9, soldey), lecture(T9, soldey), écriture(T9, soldey), déverrouillage(T9, soldey), validation(T9) }

Avec, préalablement à l'exécution, soldex = 100 et soldey = 400 :

• si T9 s'exécute d'abord : soldex = 220 et soldey = 330 ;
• si T10 s'exécute d'abord : soldex = 210 et soldey = 340 ;
• résultat de P : soldex = 220 et soldey = 340.

Le résultat de P ne correspond à aucune des deux exécutions sérielles : T9 a déverrouillé soldex avant sa phase de croissance terminée, ce qui a permis à T10 de s'y insérer. Le verrouillage seul ne suffit donc pas — c'est la discipline en deux phases qui garantit quelque chose.

#Blocage indéfini (interblocage)Copier le lien

Blocage indéfini (deadlock) : l'impasse générée par deux transactions (ou plus) qui attendent, l'une, que des verrous se libèrent, alors qu'ils sont détenus par l'autre.

Trois techniques permettent de gérer les verrous indéfinis :

1. imposer un délai imparti (timeout) : au-delà d'une durée d'attente, la transaction est avortée ;
2. prévenir les verrous indéfinis : imposer un ordre global d'acquisition des verrous, ou une estampille ;
3. détecter les verrous et les récupérer : c'est la technique fondée sur le graphe des attentes.

Graphe des attentes : un graphe dirigé $G = (N, F)$ où l'on crée un nœud par transaction et une flèche $T_i \to T_j$ quand $T_i$ attend de verrouiller un élément actuellement verrouillé par $T_j$.

Théorème (Holt, 1972). Un verrou indéfini existe si et seulement si le graphe des attentes contient un cycle.

La détection ne résout rien à elle seule : il faut choisir une victime et l'annuler pour casser le cycle. Quatre critères de choix, dans l'ordre où le cours les présente :

• il est préférable d'annuler une transaction qui vient juste de démarrer plutôt qu'une autre qui s'exécute déjà depuis un moment ;
• il est préférable d'annuler une transaction qui n'a encore apporté que quelques modifications, plutôt qu'une autre qui a déjà effectué un travail important sur la base ;
• il est préférable d'annuler une transaction qui a encore beaucoup de modifications à apporter, plutôt qu'une autre qui n'en a plus que quelques-unes à effectuer ;
• il faut éviter la famine (starvation) : une même transaction ne doit pas être choisie comme victime indéfiniment. Le remède usuel est de compter les annulations et de vieillir la priorité d'une transaction à chaque reprise.

#Niveaux d'isolation et anomaliesCopier le lien

Plus l'isolation monte, plus les anomalies disparaissent, plus le coût en concurrence (attentes, rejets) augmente. Le standard SQL définit quatre niveaux :

Définition des anomalies, chacune avec son mini-scénario sur une table comptes(id, solde) :

• Lecture sale : la transaction A lit une écriture non validée de B. Si B fait rollback, A a lu une valeur qui n'a jamais existé.
• Lecture non répétable : A relit la même ligne et trouve une valeur différente, parce que B l'a modifiée et validée entre-temps.
• Fantômes : A réexécute la même requête d'ensemble et obtient plus (ou moins) de lignes, parce que B a inséré ou supprimé des lignes correspondant au prédicat.
• Write-skew : deux transactions vérifient chacune un invariant, décident individuellement de façon sûre, mais leurs validations conjointes brisent l'invariant. L'exemple canonique suit.

#Cas d'école : le write-skewCopier le lien

Invariant métier : « au moins une personne de garde ». Deux agents A et B constatent chacun que deux personnes sont de garde, décident chacun de se retirer, et valident tous les deux : plus personne de garde.

Table et scénario avec deux sessions :

1create table garde(personne text primary key, de_garde integer not null);2insert into garde values ('A', 1), ('B', 1);

Session 1 puis Session 2, en parallèle (chacune dans sa transaction) :

1-- Session 12begin;3select count(*) from garde where de_garde = 1; -- voit 2, ok4update garde set de_garde = 0 where personne = 'A';5commit;  -- plus tard6 7-- Session 2, pendant ce temps8begin;9select count(*) from garde where de_garde = 1; -- voit encore 2 (snapshot)10update garde set de_garde = 0 where personne = 'B';11commit;

Résultat : zéro personne de garde, invariant brisé, aucune erreur levée. Chaque transaction, isolément, était prudente.

Deux parades, à choisir selon le contexte :

• Verrou explicite : select ... for update verrouille les lignes participant à l'invariant, forçant la session 2 à attendre puis à relire l'état réel (syntaxe PostgreSQL/MySQL ; SQLite verrouille la base entière en écriture, le problème ne se pose pas).
• Serializable : le moteur détecte la dépendance conflictuelle et rejette l'une des deux transactions (SQLITE_BUSY côté SQLite, erreur de sérialisation côté PostgreSQL) ; l'application doit rejouer.

1begin;2select * from garde where de_garde = 1 for update; -- verrou posé3select count(*) from garde where de_garde = 1;     -- relit l'état réel4update garde set de_garde = 0 where personne = 'A';5commit;

#Exercice : provoquer une lecture non répétableCopier le lien

Décrivez puis simulez le scénario sur comptes(id, solde) avec deux sessions A et B dans deux onglets sqlite3 (ou deux clients PostgreSQL).

1. Session A : begin; select solde from comptes where id = 1;
2. Session B : begin; update comptes set solde = solde + 10 where id = 1; commit;
3. Session A relit : select solde from comptes where id = 1;
4. Session A : commit; (ou rollback;).

#Bonnes pratiquesCopier le lien

• Transactions courtes : pas d'appel réseau, pas de calcul lourd entre BEGIN et COMMIT ; la concurrence s'étouffe dans les transactions longues.
• Retries bornés : en Serializable, un rejet est un événement normal ; rejouez la transaction complète avec un nombre d'essais limité et un délai croissant.
• Idempotence : des clés uniques et insert ... on conflict do nothing rendent une transaction rejouée sans effet double.
• Outbox : quand un message doit partir (email, événement) après une écriture, écrire le message dans une table outbox dans la même transaction, puis le publier séparément ; sinon le crash entre les deux casse l'alignement base et monde extérieur.

#QuizCopier le lien