Les transactions et les propriétés ACID

Le problème, en deux ordres

Voici un virement écrit sans précaution. La table porte une contrainte qui interdit un solde négatif, et c'est déjà une protection, mais elle ne protège que chaque ordre pris isolément.

Le résultat est juste. Mais il l'est par chance : entre les deux UPDATE, il existe un instant où trente euros n'appartiennent à personne. Si la machine s'arrête à cet instant précis, la somme des soldes a diminué de trente, et rien dans la base ne permet de savoir ce qui s'est passé.

Ce danger vient d'un réglage que MySQL et SQLite ont par défaut, l'autocommit : hors transaction, chaque ordre est validé dès qu'il réussit, comme s'il formait à lui seul une transaction. Le premier UPDATE est donc déjà définitif quand le second commence, et plus rien ne peut le défaire. Sous MySQL, un ROLLBACK envoyé à cet instant passe sans effet.

Tout ou rien

Trois mots suffisent. BEGIN ouvre la transaction et suspend l'autocommit jusqu'à sa fin, COMMIT la valide, ROLLBACK l'annule. MySQL accepte aussi START TRANSACTION, la forme de la norme, qu'il recommande ; la page, elle, ne connaît que BEGIN. Le bloc ci-dessous fait le virement, regarde les soldes à l'intérieur de la transaction, puis annule tout et regarde de nouveau.

Deux tableaux, deux réponses différentes à la même question. C'est le point à retenir : à l'intérieur d'une transaction, une lecture voit son propre travail, et personne d'autre ne le voit. Tant que COMMIT n'a pas été écrit, rien n'existe pour les autres, au niveau d'isolation ordinaire.

Le même virement, validé cette fois.

ACID, quatre garanties et quatre accidents

Le sigle se récite souvent sans qu'on dise contre quoi chaque lettre protège. Ce sont quatre accidents différents.

Le C d'ACID n'est pas le C du théorème CAP, que le parcours Big data traite dans le chapitre sur la cohérence et la disponibilité. Ici, la cohérence est le respect des règles par chaque transaction ; là-bas, c'est l'accord entre les copies d'une même donnée, qui doivent répondre la même chose.

Une erreur n'annule rien toute seule

Voici un virement dont le second ordre viole la contrainte. Le bloc s'arrête là, et c'est ce qu'il faut observer.

C'est la règle de MySQL et de SQLite. PostgreSQL est plus strict : après une erreur, il refuse tout ordre jusqu'à la fin de la transaction, et change le COMMIT en annulation. Sous MySQL, une erreur fait exception : l'interblocage, présenté plus bas, annule la transaction entière.

Quand deux transactions se croisent

Le reste du chapitre porte sur l'isolation, et il faut d'abord voir pourquoi elle est nécessaire.

Une anomalie de concurrence a une propriété désagréable : en production, elle ne se produit qu'à un entrelacement précis, une fois sur mille, au hasard de l'ordonnanceur. Pour la voir, il faut choisir soi-même l'ordre des ordres : avec deux consoles MySQL ouvertes côte à côte, on tape les instructions dans l'ordre voulu, et l'anomalie se reproduit à chaque essai. Cette page n'a qu'une connexion : les figures qui suivent tiennent lieu de seconde console.

Le mécanisme est exactement celui de la condition de course : deux acteurs lisent la même valeur avant que l'un d'eux n'écrive, et la seconde écriture écrase la première. Ce qu'une base ajoute, c'est de savoir le faire à grande échelle sans demander au programmeur de poser lui-même les verrous, et de laisser choisir combien de garanties on veut payer.

Dans les figures qui suivent, c'est le lecteur qui décide quelle transaction avance, par les boutons « Avancer T1 » et « Avancer T2 ». Avancer en alternance stricte, c'est les presser à tour de rôle, en commençant par T1. Le niveau d'isolation se change en haut de la figure, qui reprend alors au début ; une seconde rangée, « comportement », ne joue qu'au niveau sérialisable, présenté plus loin. Chaque guichet montre ce qu'il a lu, la base ce qui est validé, ce qui ne l'est pas encore et qui tient un verrou. Le journal numérote les pas, et ce sont ses numéros que le texte cite.

Deux guichets retirent de l'argent du même compte, trente euros pour l'un, vingt pour l'autre. Chacun lit le solde, calcule, puis écrit. Dans la figure, écrire Ada = lu - 30 écrit la dernière valeur que la transaction a lue, moins trente, et le calcul se fait au moment de l'écriture, comme dans un programme. Toute écriture pose sur Ada un verrou tenu jusqu'à la validation : la norme interdit, à tous les niveaux, d'écrire par-dessus une écriture qui n'est pas encore validée, et un second guichet qui veut écrire attend son tour.

Quatre-vingts. Le premier retrait a disparu sans laisser de trace, parce que les deux transactions ont lu cent avant que l'une d'elles n'écrive. Chacune a calculé juste, à partir d'une valeur qui n'était déjà plus la bonne.

L'attente du sixième pas ne sauve rien : T2 avait déjà lu cent. Le verrou d'écriture empêche d'écrire en même temps ; il n'empêche pas d'avoir lu trop tôt. Avancée entièrement avant T2, T1 laisse au contraire T2 lire soixante-dix, et le solde tombe à cinquante, le bon compte. C'est la leçon centrale du chapitre : l'ordre en série est toujours juste, et c'est l'entrelacement qui abîme.

Trois autres façons de se tromper

Lire ce qui n'existera jamais

Ici, T1 écrit puis se ravise et annule. Le niveau est réglé au plus bas.

T2 a lu soixante-dix, une valeur qui n'a jamais été validée et qui n'existera jamais. Si T2 avait imprimé un relevé, ce relevé serait faux pour toujours. En lecture validée, l'anomalie disparaît, parce qu'à ce niveau une lecture ne voit que ce qui a été validé.

Lire deux fois et obtenir deux réponses

T2 n'a rien fait de mal : elle a lu deux fois le même compte, et la base lui a donné deux valeurs différentes. Pour un rapport qui additionne des colonnes en plusieurs passes, cela suffit à produire un total qui ne tombe pas juste. La lecture répétable l'en protège, parce que ce niveau lui donne une photographie de la base prise à sa première lecture.

Compter deux fois et trouver une ligne de plus

La quatrième anomalie classique est le lecteur fantôme : une transaction compte les lignes qui remplissent une condition, une autre en insère une qui la remplit aussi et valide, et le second comptage trouve une ligne de plus. Aucune ligne déjà lue n'a changé, et pourtant la réponse change.

Les figures de ce chapitre ne peuvent pas la produire, et c'est assumé : elles manipulent des objets nommés un par un, alors que le fantôme suppose une lecture par condition, « toutes les lignes où le solde dépasse mille ». C'est aussi ce qui le rend plus coûteux à empêcher : il faut verrouiller non pas des lignes, mais l'intervalle où une ligne pourrait apparaître.

Il existe d'autres anomalies, plus rares, dont l'écriture biaisée : deux transactions vérifient chacune une condition qui porte sur les deux, puis modifient chacune une ligne différente. Aucune n'écrase l'autre, et pourtant le résultat viole la condition. Deux médecins de garde vérifient chacun que l'autre reste de garde, puis se retirent tous les deux, et il n'y a plus personne. Parmi les niveaux, sous MySQL comme sous PostgreSQL, seul le sérialisable l'empêche ; une lecture qui verrouille les lignes vérifiées, SELECT ... FOR UPDATE, présentée plus loin, l'empêche aussi au niveau par défaut.

Les quatre niveaux

Chaque niveau se définit, dans la norme SQL, par les anomalies qu'il interdit, et non par la façon dont il s'y prend : deux bases obtiennent le même niveau par des moyens différents, verrous d'un côté, versions de l'autre. La norme en retient trois : la lecture sale, la lecture non répétable et le lecteur fantôme.

La forme en escalier dit que chaque niveau interdit tout ce qu'interdisait le précédent, et une anomalie de plus ; c'est le fantôme qui sépare la lecture répétable du sérialisable. Le sérialisable promet même davantage que sa ligne du tableau : un résultat identique à celui d'une exécution en série, l'une après l'autre. Les deux premières colonnes se contrôlent sur les figures ci-dessus, en changeant le niveau, et au besoin l'ordre ; la troisième leur échappe, comme on l'a vu.

La mise à jour perdue n'est pas dans le tableau de la norme, et c'est là que les bases divergent. En lecture répétable, PostgreSQL refuse la seconde écriture, et l'empêche donc ; MySQL la laisse passer. La figure du double retrait se comporte ici comme MySQL : elle laisse passer la mise à jour perdue à tous les niveaux sauf le sérialisable.

Ce que fait MySQL

Le moteur que ce cours fait installer, InnoDB dans MySQL, se règle par les quatre mêmes niveaux (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ et SERIALIZABLE). Trois points le distinguent, et chaque case du tableau ci-dessous se reproduit avec deux consoles MySQL ouvertes côte à côte.

• Le niveau par défaut est la lecture répétable, et non la lecture validée que retiennent PostgreSQL, Oracle et SQL Server. SELECT @@transaction_isolation; le confirme en rendant REPEATABLE-READ. Ce niveau ne met pourtant pas à l'abri de la mise à jour perdue : joué sous MySQL dans l'ordre de la première figure, le double retrait finit à quatre-vingts.
• Le fantôme est empêché le plus souvent dès la lecture répétable. Une lecture ordinaire y travaille sur une photographie prise à la première lecture, et ne voit pas la ligne ajoutée ; une lecture qui verrouille, écrite avec FOR SHARE ou FOR UPDATE, pose un verrou sur l'intervalle, et l'insertion attend. Le fantôme ne revient que dans une transaction qui mêle les deux : une lecture qui verrouille voit la dernière version validée, ligne ajoutée comprise.
• Le sérialisable fait attendre plutôt que refuser. Chaque lecture y pose un verrou partagé, qui fait attendre les écritures des autres. Deux transactions qui ont lu la même ligne avant de l'écrire s'attendent l'une l'autre, et le conflit se règle par un interblocage, présenté plus bas, et non par un refus à la validation. Deux écritures qui calculent elles-mêmes, sans lecture préalable, ne font qu'attendre leur tour.

SQLite, le moteur qui exécute les blocs de cette page, n'a pas ces nuances : il n'admet qu'un écrivain à la fois, et ses transactions sont sérialisables.

Le niveau sérialisable, dans la figure

La norme SQL ne prescrit aucun moyen : elle exige seulement que des transactions sérialisables produisent le même effet qu'une exécution l'une après l'autre, et permet au moteur de faire échouer une transaction pour y parvenir. Le comportement « refus à la validation », choisi par défaut dans la figure, la fait échouer à sa validation ; PostgreSQL, sur ce double retrait, dès sa seconde écriture ; MySQL, par un interblocage, que le comportement « de MySQL » fait jouer.

T2 ne perd pas silencieusement : sa validation est refusée, parce que la valeur sur laquelle elle avait décidé a changé entre-temps. Le travail est perdu quand même, mais le programme l'apprend, et il peut recommencer sur la valeur à jour : relancée, T2 lit soixante-dix et écrit cinquante. C'est toute la différence entre une base qui se trompe en silence et une base qui dit non.

Ce que l'isolation coûte

Un niveau élevé se paie de deux façons, et le choix se fait entre les deux.

En attente. Une transaction qui doit garantir de ne pas voir bouger ses données fait patienter celles qui voudraient les modifier. Plus le niveau est élevé, plus les transactions s'attendent.

En refus. Un moteur qui préfère ne pas faire attendre refuse les validations en conflit, comme le comportement « refus à la validation » de la figure précédente ; un moteur à verrous, comme MySQL, fait attendre, puis annule l'une des transactions quand l'attente devient circulaire. Dans les deux cas, le programme doit gérer l'échec et recommencer, ce qui est du travail supplémentaire à écrire.

C'est pour cela que PostgreSQL, Oracle et SQL Server se règlent par défaut sur la lecture validée, et non sur le niveau le plus sûr : c'est le premier niveau qui interdit de lire ce qui n'existe pas, et, dans les bases qui lisent par versions, comme PostgreSQL et Oracle, une lecture n'y attend jamais une écriture. MySQL monte d'un cran, à la lecture répétable, sans pour autant empêcher la mise à jour perdue. On ne monte au sérialisable que pour les traitements où une incohérence coûte plus cher que la lenteur, un arrêté comptable par exemple. Un réglage par défaut se vérifie, il ne se suppose pas.

Ce qu'on écrit en pratique

Monter le niveau d'isolation n'est pas le premier réflexe. Deux écritures empêchent la mise à jour perdue au niveau par défaut de MySQL, et toutes deux se vérifient avec deux consoles côte à côte, dans l'ordre de la première figure.

Calculer dans l'ordre lui-même. UPDATE comptes SET solde = solde - 30 WHERE id = 1 ne fait pas lire le solde par le programme : la base lit et écrit la ligne en une seule opération, et garde un verrou sur elle jusqu'à la validation. Le second guichet, qui envoie solde - 20 pendant ce temps, attend la validation du premier, puis part de soixante-dix : le solde tombe à cinquante. C'est pour cela que le virement du début de ce chapitre ne perd rien.

Ici, écrire Ada = Ada - 30 est le UPDATE qui calcule lui-même : la valeur de départ est celle qui est en place au moment d'écrire, et non une lecture faite plus tôt.

Verrouiller la lecture quand le calcul doit se faire dans le programme. Un programme doit parfois lire avant de décider, pour vérifier qu'un solde suffit par exemple. Il lit alors en verrouillant : SELECT ... FOR UPDATE réserve la ligne jusqu'au COMMIT. Un second guichet qui fait la même lecture attend, puis lit la valeur déjà débitée.

FOR UPDATE existe sous MySQL, PostgreSQL et Oracle. SQLite, qui exécute les blocs de cette page, ne le connaît pas, ses écritures étant déjà sérialisées : ce bloc ne s'exécute pas ici, il se tape dans une console MySQL. La figure, elle, le connaît.

C'est l'ordre exact de la première figure, aux lectures près. Le calcul se fait toujours dans le programme, lu - 20, mais sur une valeur que personne ne peut plus changer avant la validation : cinquante, là où des lectures ordinaires donnaient quatre-vingts.

Monter le niveau en dernier. Le niveau sérialisable règle aussi le problème, mais pour toutes les lectures de la transaction, et à son prix.

L'interblocage, à tous les niveaux

Le sérialisable de MySQL a fait voir un interblocage, mais rester au niveau par défaut n'en protège pas. L'interblocage le plus courant y naît de deux transactions qui écrivent les mêmes lignes dans des ordres opposés : les verrous d'écriture se prennent à tous les niveaux, et chacune peut en tenir un que l'autre attend. Les deux virements de la figure calculent dans l'ordre lui-même, comme la section précédente le recommande : aucune lecture ne précède, et la mise à jour perdue n'est pas en jeu.

Rejoués sur deux sessions MySQL, en lecture répétable comme en lecture validée, les virements croisés se terminent de même : la seconde session reçoit « ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction », et les comptes finissent à 70 et 80. Le manuel de MySQL tient ces interblocages pour ordinaires sur un serveur chargé : un programme qui écrit dans une base doit savoir recommencer une transaction annulée. Le code 40001 est celui d'un échec de sérialisation, que PostgreSQL emploie aussi.

Le remède ne passe pas par le niveau. Il suffit que les deux transactions prennent leurs verrous dans le même ordre.

Deux transactions qui prennent leurs verrous dans le même ordre ne peuvent pas s'attendre en cercle. C'est la règle du chapitre sur l'interblocage, appliquée aux lignes d'une table : un programme qui modifie plusieurs comptes les met à jour dans un ordre fixé, par identifiant croissant par exemple, quel que soit le sens du virement. Rejoué sur MySQL, le second virement attend, puis passe : 90 et 60.

Une attente n'est pas toujours un interblocage. Quand une transaction garde ses verrous sans valider, parce que son programme l'a laissée ouverte, celle qui attend reçoit l'erreur 1205, « Lock wait timeout exceeded », au bout de cinquante secondes, la valeur d'origine du réglage innodb_lock_wait_timeout. Cette erreur-là n'annule que l'ordre qui attendait, comme une erreur de contrainte : ce que la transaction avait fait avant reste en attente, et c'est encore au programme d'envoyer ROLLBACK.

Les erreurs d'un débutant

Deux raisonnements qui paraissent tenir. Dans chacun, une seule étape est fausse : la trouver, puis donner une valeur qui la met en défaut.

Le premier fait confiance au verrou d'écriture.

Le second croit qu'une transaction se défait d'elle-même au premier ordre refusé.

Mettre en pratique

Écrire le virement correctement. Le premier bloc de ce chapitre faisait le virement sans précaution. L'exercice suivant le fait écrire en transaction, et juge le script sur plusieurs états de départ, dont certains où l'un des deux ordres échoue : c'est là qu'un script sans transaction se trahit.

Le programme qui annule. Dans un programme, c'est le code qui doit attraper l'erreur et envoyer ROLLBACK. Le module sqlite3 de Python, qui pilote une base SQLite depuis un programme, le fait voir en quelques lignes. L'exercice suppose le try et le except du chapitre sur les conditions et les boucles du parcours Python ; il est facultatif.

Compter les ordres. La première figure ne joue qu'une partie des façons d'entrelacer les deux retraits. Combien y en a-t-il en tout, et combien tombent juste ?

Trouver l'ordre qui sauve la mise à jour. Sur la première figure de transactions, réglée sur « lecture validée », trouver un ordre d'avance qui donne cinquante plutôt que quatre-vingts. Il en existe plusieurs, et ils ont tous la même propriété.

Nommer l'anomalie, et écrire d'autres transactions. Le thème d'exercices Les transactions, en fin de chapitre, fait reconnaître les cinq anomalies du chapitre sur cinq incidents, et écrire, corrigés sur plusieurs états de la base, un virement avec ses frais, un paiement qui touche deux tables, deux virements qui ne doivent pas dépendre l'un de l'autre, et un retrait qui vérifie lui-même le solde.