BEGIN et COMMIT je pense que vous connaissez, mais ACID vous connaissez? Et sauriez-vous expliquer la problématique des LOCKS dans les transactions? Quand on manipule un SGBDR il y a de fortes chances que l'on soit confronté à des transactions SQL, et les chances sont encore plus grandes de ne plus se rappeler des principes de base et des bonnes pratiques. Essayons de refaire le point de façon rapide, claire et simple.

1- ACID ?

Un système de gestion de bases de données qui est transactionnel se doit de respecter un ensemble de règles de que l'on baptise A.C.I.D, pour Atomicity, Coherence, Isolation et Durability.

Parmi ces quatre mots un seul est réellement complexe; Et nous le verrons en dernier.

2- Atomicité

L'atomicité est un principe simple, quelque chose est atomique s'il ne peut être divisé. C'est donc le principe du tout ou rien.

C'est le principe le plus simple des transactions, que l'on retrouve avec les mots clefs BEGIN, COMMIT et ROLLBACK. Une fois que j'ai commencé avec BEGIN, je sortirai soit avec tout (COMMIT), soit avec rien (ROLLBACK), mais il n'y aura pas de demi résultat où une partie de mon travail serait sauvegardé alors que le reste serait en échec.

Attention, cependant, le ROLLBACK peut être lancé par le serveur. Sur ce qui ressemblerait à des exceptions du côté du langage que vous utilisez pour manipuler la base, vous n'avez très souvent pas besoin de lancer un rollback, il est souvent déjà lancé par le serveur (sur un deadlock par exemple).

3- Cohérence

La cohérence est aussi assez facile à comprendre. La base de donnée a mis en place des règles, par exemple des contraintes d'unicité, des contraintes de clefs étrangères entre les tables, etc.

Avant que votre transaction ne commence, ces règles étaient vérifiées. Une fois votre transaction validée ces règles seront toujours validées (la base sera cohérente). Au milieu de votre transaction vous aurez plus ou moins de latitude (suivant les moteurs) pour travailler dans des états non cohérents (vérifications retardées).

4- Durabilité

La durabilité est un principe très simple pour l'utilisateur, un peu plus complexe pour le moteur. Quand ma commande COMMIT est acceptée par le serveur cela signifie que les données retirées, ajoutées ou modifiées, devront rester en place, même si la commande suivante est en erreur ou si quelqu'un débranche la prise du serveur juste après.

Le serveur est donc censé s'assurer que les données sont présentes "quelque part", sur un disque physique, et non pas uniquement dans un cache mémoire de disque, et si ce n'est pas présent sur le dossier de stockage physique de la base c'est au moins présent dans un log binaire qui peut rétablir ces données dans le bon état.

5- Isolation (ouch)

C'est ici que les choses se durcissent. Dans un monde idéal il n'y aurait pas d'écritures en parallèle. Certains "systèmes de bases de données" règlent le problème de cette façon, en interdisant l'utilisation de la base par plusieurs utilisateurs (ou plusieurs connexions). Mais restons sérieux. dans un vrai système de gestion de base de données il peut y avoir des écritures en parallèle, plus précisément il peut y avoir des transactions en parallèle (avec des durées très variables).

5.1- Pour penser transaction, pensez traitements parallèles

L'isolation signifie qu'une transaction devrait être isolée des autres. Mais il n'y a pas d'isolation absolue. Prenez cet exemple qui représente 3 transactions parallèles dans le temps :

|       B               C
|       +---------------+ T3
|  B            C
|  +------------+ T1
|    B                               C
|    +--------------------------------+ T2
|
+---------+-----+-------+-----------------------------> temps
t         t0    t1      t2 

Ici nous avons 3 transactions.

Prenez par exemple le moment t2, la transaction T3 vient de se terminer. La transaction T2 tourne encore, hors T2 a commencé avant T3, que deviennent les objets impactés par T3 dans l'environnement de T2?

Si vous réglez votre serveur pour essayer de limiter au maximum les problèmes d'isolation (niveau SERIALIZABLE), ce qui va arriver est très simple, il risque de lancer un rollback automatique sur toutes les transactions qui tournent encore et qui touchent aux mêmes objets. Si aucun objet commun n'est pas impacté alors les choses se passeront bien. Le moteur essaye en fait de "sérialiser" dans le temps les transactions, du point de vue de la gestion des données l'idéal est une série de transactions en série, lissées dans le temps, et surtout pas en fonctionnement parallèle :

|                                   B               C
|                                   +---------------+ T3
|  B        C
|  +--------+ T1
|           B                       C
|           +-----------------------+ T2
|
+-----------------------------------------------------> temps théorique

Mais là encore on restreint drastiquement les performance du système (en se permettant en plus de rejeter un très grand nombre de transactions si elles touchent aux même objets).

Il va donc y avoir plusieurs niveaux d'isolation des transactions, qui permettront d'équilibrer les besoins entre performance, intégrité des données et risques de conflits.

Mais avant cela un rappel important :

5.2- Quand on utilise des données dans une transaction il faut avoir lu ces données dans cette transaction.

Mettez-vous debout, sur un jambe, le bras droit derrière la tête et la main gauche sur les yeux, et répétez trois fois cette phrase.

Quand on utilise des données dans une transaction il faut avoir lu ces données dans cette transaction. Et dans certains cas il faut spécifier en plus 'FOR UPDATE' pour dire que vous avez l'intention d'y toucher.

Si vous lisez des données AVANT de faire le BEGIN, il se peut que ces données soient modifiées par une autre transaction (qui tournait déjà et qui vient de se terminer, ou qui vient de tourner et de se terminer, ou qui va se terminer au milieu de votre transaction). RIEN ne garantit que ces données soient encore valides. Et si vous ne faites pas votre lecture DANS votre transaction, le moteur de base de donnée ne pourra pas vous aider, il ne pourra pas détecter que quelqu'un manipule des choses importantes pour vous, il ne pourra pas bloquer cette écriture, ou tout autre opération. Et c'est bien dommage parce qu'il est prévu pour ça le moteur du SGBD, il y a des lignes et des lignes de code qui sont là pour ça. Mais vous, ingrats, vous faites confiance à des gars qui écrivent des CMS en code spaghetti pour gérer vos transactions, et vous commencez à vous dire qu'il y a peut-être des choses très bizarres en fait (parce que la lecture des données après le BEGIN on ne la voit pas souvent quand même), sans parler de la gestion de données que vous avez affichées sur une page web à l'utilisateur il y a 10 minutes et qu'il vient de vous renvoyer… je m'égare. Mais voilà, l'isolation des écritures c'est un problème complexe qui se double en fait du problème de la gestion de la validité des modifications que vous voulez mettre en œuvre, si vous voulez une application parfaite vous pouvez y passer un temps infini.

Mais commençons par le commencement, et reprenons donc les bases de l'isolation des transactions.

5.3- Niveaux d'isolation

Si nous reprenons le schéma des transactions qui tournent au même moment, des gens très intelligents ont définis les 3 types de problèmes d'isolation que l'on peut rencontrer (parce que d'autres transactions tournent et se terminent pendant la vôtre) :

• lecture sale : on peut lire des données altérées par une autre transaction qui n'est même pas commitée
• lecture non reproductible : en relisant plusieurs fois les mêmes données dans une même transaction on peut voir qu'elles ont été modifiées (par une transaction commitée entre temps)
• lecture fantôme : presque comme la lecture non reproductible, mais là c'est avec des requêtes dont les filtres ne valident pas le même nombre de résultats (dans l'autre cas c'était sur des lignes ciblées, ici plutôt sur un count(*) where prix > 50)

Et ils ont définis 4 niveaux d'isolation, en permettant ou en interdisant à chacun de ces niveaux la présence de ce type de problèmes :

• 1: Uncommited read : les 3 problèmes
• 2: Commited Read : pas de lecture sale, mais les 2 autres si
• 3: Repeatable read : uniquement lecture fantôme
• 4: Seralizable : aucun problème

Et là on se dit que le niveau SERIALIZABLE est quand même super, mais en réalité, il existe des cas complexes dans lequel les problèmes d'isolation ne sont pas suffisamment détectés par le SGBD et sur lesquels vous devriez gérer une sérialisation manuelle (en n'autorisant pas ces cas complexes en parallèle, côté applicatif). Sur les versions très récentes de PostgreSQL il semble que même ces cas aient disparus (grâce aux verrouillage prédictif), ce n'est pas le cas dans tous les moteurs (et dans ce domaine plus le moteur SQL est ancien moins il est sûr).

Mais alors la solution serait de passer tous vos SGBD à ce niveau ? Et bien pas forcément, non, car la principale méthode dont dispose le moteur pour régler ces problèmes consiste à bloquer certaines transactions (locks) pendant un certain temps, voire à les tuer. Et il faut donc disposer d'un code qui est prêt à accepter que la transaction puisse être rejetée et à la relancer le cas échéant, ou bien qui supporte le fait d'attendre quelques secondes lors de traitements d'écriture. Et là sur des applications web, on se rend compte qu'il faut disposer de solutions très élégantes pour être capable de rejouer le traitement et de gérer des timeouts ni trop longs, ni trop courts (sans quoi vous avez juste des white-screen-of-death sur 41 des 42 écrans qui ont tentés de sauver un panier d'achat dans la même seconde).

5.4- Les locks

La solution du SGBD pour gérer l'isolation passe par les locks. Il y a beaucoup de types de locks différents dans un SGBD, mais pour rester simple disons qu'il s'agit ici de bloquer des traitements d'écriture d'une même ligne de données en parallèle.

Une transaction qui touche une ligne en mise à jour (ou suppression) va par exemple poser un lock sur cette ligne. Une autre transaction, qui voudrait toucher à la même ligne, va être tout simplement bloquée, mise en attente.

Cette mise en attente s'arrête quand le lock est libéré… et donc quand la première transaction se termine (sur un ROLLBACK ou sur un COMMIT).

Vu comme ça c'est assez simple, et c'est très facile à expérimenter avec deux sessions SQL en parallèle.

On voit aussi que les principales opérations bloquantes sont les suppressions et mises à jour, les insertions sont moins complexes (avec quand même une gestion des incréments qui doit supporter le parallélisme), surtout si on s'autorise les lectures fantômes (si vous ne voyez pas pourquoi, relisez la définition d'une lecture fantôme).

En théorie on peut poser un lock en faisant aussi uniquement une lecture au sein d'une transaction. C'est le SELECT … FOR UPDATE. On indique au moteur qu'on fait une lecture afin d'apporter très bientôt des modifications à ces lignes. Un lock est posé au moment de la lecture qui va empêcher la mise à jour de cette ligne par une autre transaction. Est-ce qu'il va empêcher la lecture de ces lignes ? Bonne question, ouvrez deux sessions SQL et faites le test.

# session 1                  | #session 2
mysql> BEGIN;                |
mysql>                       | mysql> BEGIN;
mysql> SELECT * FROM users   |
    -> WHERE uid=1           |
    -> FOR UPDATE;           |
+----+--- <result>(...)      | mysql> SELECT * FROM users
                             |     -> WHERE uid=1
                             |     -> FOR UPDATE;
                             | ===> waiting several seconds until:
                             | ERROR 1205 (HY000): Lock wait timeout exceeded;
                             | try restarting transaction

Ici on a essayé de poser un lock d'écriture dans la deuxième transaction, et on n'a pas terminé la première transaction. Donc notre deuxième transaction est en échec.

Mais on va relancer une troisième transaction et essayer de simplement lire cette ligne :

# session 1                  | #session 2
# (still running from        | # note that we do not need a rollback
# previous example)          | # the ERROR was an implicit ROLLBACK
                             | mysql> BEGIN;
                             | mysql> SELECT * FROM users
                             |     -> WHERE uid=1;
                             | +----+--- <result>(...)
                             | # IT WORKS \o/

Donc, si on n'indique pas qu'on veut faire une mise à jour, on a le droit de lire cette ligne.

Avançons un peu dans l'exemple en passant au niveau SERIALIZABLE.

# session 1                  | #session 2
mysql> ROLLBACK;             |
mysql>                       | mysql> ROLLBACK;
mysql> SET SESSION           |
    -> TRANSACTION ISOLATION |
    -> LEVEL SERIALIZABLE;   |
                             | mysql> SET SESSION TRANSACTION
                             |     -> ISOLATION LEVEL SERIALIZABLE;
mysql> BEGIN;                |
mysql>                       | mysql> BEGIN;
mysql> SELECT * FROM users   |
    -> WHERE uid=1           |
    -> FOR UPDATE;           | # try a read only query...
+----+--- <result>(...)      | mysql> SELECT * FROM users
                             |     -> WHERE uid=1;
                             | ===> waiting several seconds until:
                             | ERROR 1205 (HY000): Lock wait timeout exceeded;
                             | try restarting transaction

Et là on voir à quel point le niveau d'isolation impacte les LOCKS. L'impact réel est à tester avec votre vrai SGBD, en vérifiant le niveau d'isolation par défaut et peut-être en forçant dans votre application le niveau attendu.

5.5 Les Deadlocks

Dans les exemples précédents, en attendant suffisamment longtemps on obtient une erreur de timeout, le lock n'est pas libéré assez rapidement. Si vous faites un COMMIT ou un ROLLBACK sur la première transaction il n'y a pas d'erreur.

Mais alors, qu'est-ce que le deadlock? Facile, deux transactions s'attendent mutuellement sur des locks différents, chacune attend la fin de l'autre (dans le cas le plus simple, car on pourrait aussi faire un deadlock avec 50 transactions interdépendantes pour s'amuser).

T1 | GET_LOCK_A      WAIT_FOR_LOCK_B
   | -+---------------+-----------------
   |
   |   GET_LOCK_B         WAIT_FOR_LOCK_A
T2 | ---+------------------+ <--- DEADLOCK
   |
   +----------------------------------------> time

Soit le moteur détecte facilement ces interdépendances, soit il détecte que tout le monde attend (timeout), il est alors en mesure de choisir, au hasard, de tuer (ROLLBACK) quelques transactions impliquées et de regarder si le nœud se débloque.

Le test :

mysql> ROLLBACK;             |
mysql>                       | mysql> ROLLBACK;
mysql> SET SESSION           |
    -> TRANSACTION ISOLATION |
    -> LEVEL READ UNCOMMITED;|
                             | mysql> SET SESSION TRANSACTION
                             |     -> ISOLATION LEVEL READ UNCOMMITED;
mysql> BEGIN;                |
mysql>                       | mysql> BEGIN;
mysql> SELECT * FROM users   |
    -> WHERE uid=1           |
    -> FOR UPDATE;           | 
+----+--- <result>(...)      | mysql> SELECT * FROM role
                             |     -> WHERE rid=1
                             |     -> FOR UPDATE;
                             |+----+--- <result>(...)
mysql> SELECT * FROM role    |
    -> WHERE rid=1
    -> FOR UPDATE;
# waiting                    |  mysql> SELECT * FROM users
# still waiting              |      -> WHERE uid=1
# still waiting              |      -> FOR UPDATE;
# not waiting anymore        | ERROR 1213 (40001): Deadlock found
+----+--- <result>(...)      | when trying to get lock;
                             | try restarting transaction

** - Donc le moteur peut tuer ma transaction alors que je n'ai rien fait de mal ?**

** - Oui !**

Notez qu'on peut aussi avoir un timeout dans l'attente de l'obtention d'un lock sur un élément déjà touché par une autre transaction, si cette transaction s'éternise. Elle peut s'éterniser parce qu'elle est elle-même en attente d'une libération de lock, ou bien parce qu'elle a beaucoup de choses à faire. Toutes les opérations qui posent des locks peuvent potentiellement échouer, les lecture en SELECT ... FOR UPDATE, mais aussi les UPDATE, DELETE, etc. En fait il n'y a pas qu'un seul niveau de lock en interne, mais plusieurs niveaux différents, et les différentes opérations définies par le langage SQL nécessitent des accès plus ou moins bloquants dans ces niveaux de locks, qui vont potentiellement bloquer les autres opérations effectuées en parallèle.

Autre chose, tous mes exemples, ci-dessus, utilisaient SELECT.. FOR UPDATE pour poser des locks, essayez avec des vrais UPDATE pour voir la différence.

5.6 Solutions aux deadlocks

Pour éviter d'avoir des problèmes de "DeadLock" (et autres "Lock Timeout detected" quand la détection automatique de ces cas est en échec), il y a plusieurs méthodes :

• Si vous avez la main sur l'ensemble du code qui touche à la base (il n'y a pas d'autres applications, pas de traitements batchs qui touchent à vos tables en dehors du code que vous maîtrisez, etc), vous pouvez essayer d'imposer un ordre dans la mise en place des locks (on pose les locks dans l'ordre alphabétique des tables). Mais c'est assez peu réaliste à grande échelle, et éloigné des besoins métiers de l'application, donc difficile à maintenir.
• Si vous avez la possibilité de faire des opérations atomiques faites le (comme une requête update qui fait elle même ses lectures).
• Évitez les transactions trop longues (en temps), plus la transaction dure, plus elle laisse derrière elle des blocages potentiels pour les autres opérations.

6 Trucs, astuces et digressions

6.1 En Vrac

• Relire les données importantes (celles qu'on utilise), au sein de la transaction
• Marquer les lignes qu'on veut bloquer avec SELECT … FOR UPDATE …
• Vérifiez le niveau de transaction par défaut utilisé sur votre base (niveau 2/4 pour PostgreSQL et 3/4 pour MySQL, par exemple)
• Encadrer les transactions dans des boucles de retry, avec un nombre raisonnable de retry (par rapport au temps de réponse attendu
• Régler les temps de timeout en fonction de ce temps de réponse attendu
• Pensez à utiliser le PL/PgSQl pour écrire les procédures complexes, surtout si plusieurs programmes doivent utiliser la base, pensez aussi aux triggers pour automatiser des mises à jour en cascade (mais faites attention à MySQL, cf. plus bas)
• Travaillez en priorité les données précieuses (comme les données qui parlent d'argent)
• En parlant d'argent, apprenez la comptabilité, il y a des choses géniales en compta, comme la double écriture, le journal et l'état, c'est bien pensé, et ça permet de contrôler et corriger les erreurs, c'est dommage que l'immense majorité des applis web qui font des transactions et qui gèrent de l'argent n'aient aucune idée en la matière.
• Les transactions se gèrent normalement au niveau des Controllers et pas des mappings objet vers la base (DAO). Une transaction n'est pas simplement liée à une sauvegarde d'objet, elle doit gérer la sauvegarde d'un ensemble d'objets, être capable de gérer des retry et erreurs, et peut-être même gérer des effacements sur des éléments non transactionnels (comme un enregistrement en parallèle dans un LDAP).

6.2 Mais Pourquoi, pourquoi des bugs, pourquoi c'est pas clair ?

Globalement le niveau de qualité de l'immense majorité des applications qui utilisent des transactions, sur les applis web, n'est pas terrible.

Prenons, par exemple, Drupal, un deadlock ne génère jamais de relance de la transaction. Toujours sur Drupal, si on veut éviter d'avoir trop de deadlocks dans le watchdog (et d'écrans d'erreurs pour les utilisateurs), il faut descendre le niveau d'isolation de MySQL à READ COMMITTED, il est conseillé, par exemple ici, de ne pas faire tourner Drupal Commerce avec le niveau par défaut (READ UNCOMMITED). La politique de sauvegarde des champs multilingues de Drupal passe aussi par des DELETE+INSERT au lieu de tenter un UPDATE (qui est plus complexe, mais un patch existe pour cela), et l'utilisation de ces DELETE peut générer des très forts LOCKS, surtout dans les anciennes versions de MySQL.

On trouve aussi beaucoup de programmes qui utilisent les transactions dans les DAOs, et donc hors de portée des Controllers.

La lecture des données au sein des transactions est un doux rêve… bref heureusement que ça marche dans la majorité des cas. Mais si vous avez une application avec des traitements d'écriture parallèles, sur des données partagées, vous allez sûrement devoir écrire vous même pas mal de code si vous voulez une solution plus robuste.

Alors, pourquoi ?

Déjà, vu que le principal problème des transactions c'est l'isolation, il faut bien comprendre que le seul moyen d'écrire des tests unitaires ou fonctionnels qui gèrent ces problèmes est d'avoir deux connexions différentes à la base de données ** (avec le même user, mais deux instances, parce que la transaction est propre à la connexion). Vous ne pouvez pas tester un problème d'isolation dans un programme qui n'a qu'un seul objet (global) qui le rattache à sa connexion à la base de données (sauf à faire tourner deux programmes, ce qui est encore plus dur dans un test). Pour des tests manuels ouvrez deux connexions avec les utilitaires en ligne de commande (mysql ou psql par exemple), vous verrez à quel point ce n'est en fait pas si compliqué. Donc on touche au syndrome du **chez moi ça marche, parce que le développeur expérimente rarement les problèmes de concurrence d'accès.

Le deuxième problème c'est que ça n'est pas évident. Les problèmes ne sont pas évidents à identifier et connaître et les solutions le sont encore moins. Il faut l'avoir appris (ou être un génie). La situation aujourd'hui ressemble plus à une course à la fonctionnalité qu'à une course à la robustesse (cf. MongoDB par exemple). Pour le développeur la plupart des ces problèmes sont invisibles (tests durs à paramétrer) et inconnus, ils ne sont donc tout simplement pas gérés. Si vous avez lu et compris cet article vous pourrez donc avoir la joie de jouer les Cassandres dans un projet et vous remarquerez alors à quel point ce type d'informations trouve souvent sa place sous le tapis (de toute façon on va tout gérer en NoSQL bientôt, vous ne le saviez pas ? ;)).

6.3 PostgreSQL le nec plus ultra

L'excellente documentation française de PostgreSQL sur les niveaux d'isolation, ou de façon plus large tout le chapitre sur le MVCC (Contrôle d'accès simultané) permettent d'approfondir les transactions. On trouve sur ce projet de très bons éléments de formation.

Mais on trouve surtout une robustesse dans les implémentations de PostgreSQL qui est impressionnante. Si vous remplacez dans les urls précédentes la version 9.4 par 9.1 vous pourrez observer des différences, et des exemples de cas complexes qui n'étaient alors pas encore pris en charge en terme d'isolation.

Si vous testez en profondeur les problématiques de concurrence sur des SGBD concurrents, comme MySQL, vous avez des chances de tomber sur des bugs très bizarres (notez que les versions récentes de innoDb sont certainement plus robustes aujourd'hui).

Bref, si vous voulez vraiment jouer avec les transactions, faites confiance à PostgreSQL. Vous ne serez pas déçus.

Conclusion

Si vous êtes arrivé au bout, c'est bien. Rappelez vous simplement deux choses :

• si vous cherchez une information juste et précise sur les transactions, allez dans la documentation de PostgreSQL ;
• si vous avez des problèmes de locks ce n'est pas un bug du SGBD, c'est normal et c'est un vrai problème applicatif.

Pour aller plus loin

Découvrez notre formation postgreSQL, vous apprendrez à installer et à administrer au quotidien votre serveur de base de données.