Qu’est-ce qu’une transaction de base de données ?

• Une transaction est une structure qui permet d’exécuter plusieurs opérations dans une base de données comme une unité atomique, incluant lecture, écriture, mise à jour et suppression
• MySQL et Postgres contrôlent les transactions avec begin; et commit;, et annulent les modifications avec rollback; en cas d’échec ou d’erreur
• Les deux bases de données garantissent des lectures cohérentes (consistent read), mais Postgres utilise le stockage multiversion des lignes (MVCC), tandis que MySQL s’appuie sur l’undo log
• Le niveau d’isolation (isolation level) contrôle les interférences de données entre transactions et se divise en quatre niveaux, de Serializable à Read Uncommitted
• Postgres et MySQL gèrent les conflits d’écriture concurrents de manière différente : Postgres utilise une validation optimiste, tandis que MySQL emploie le verrouillage au niveau des lignes (row-level locking)

---

Notions de base sur les transactions

• Une transaction est une structure qui regroupe plusieurs opérations SQL dans une unité d’exécution atomique au sein d’une base de données
  • Elle commence avec begin; et se termine avec commit;, avec la possibilité d’exécuter plusieurs requêtes entre les deux
  • Au moment du commit;, toutes les modifications sont appliquées en une seule fois
• En cas de panne imprévue (coupure d’alimentation, erreur disque, etc.) ou d’annulation volontaire, rollback; permet de revenir sur les modifications
  • Postgres prend en charge la reprise grâce au WAL (Write-Ahead Log)
• Les données modifiées pendant une transaction sont isolées et ne sont pas visibles depuis d’autres sessions
  • Lors d’un rollback;, toutes les modifications sont annulées et la base de données revient à son état d’origine

Lectures cohérentes (Consistent Reads)

• Une transaction doit conserver une vue cohérente des données qui n’est pas affectée par des modifications externes pendant son exécution
• MySQL et Postgres prennent cela en charge à partir du mode REPEATABLE READ, mais leur implémentation diffère
  • Postgres : gestion des versions de chaque ligne via le stockage multiversion des lignes (MVCC)
  • MySQL : reconstitution des versions passées à l’aide de l’undo log

Le stockage multiversion des lignes dans Postgres

• À chaque mise à jour d’une ligne, une nouvelle version est créée ; l’ancienne enregistre l’ID de transaction dans xmax, la nouvelle dans xmin
• Tant que la transaction n’est pas validée, les autres sessions ne peuvent pas voir les modifications
• Après le commit, la nouvelle version est reflétée dans l’ensemble de la base de données
• En cas de rollback;, les modifications sont abandonnées et les données d’origine sont conservées
• Les anciennes versions de lignes sont nettoyées avec la commande VACUUM FULL, ce qui permet de récupérer de l’espace de stockage

L’Undo Log de MySQL

• MySQL écrase directement les lignes, mais enregistre les anciennes valeurs dans l’undo log, ce qui permet de les restaurer si nécessaire
• Chaque ligne possède comme métadonnées xid (ID de la dernière transaction modificatrice) et ptr (pointeur vers l’undo log)
• Lorsque plusieurs transactions s’exécutent simultanément, chaque transaction sélectionne la version dont elle a besoin via l’undo log
• Plusieurs enregistrements d’undo log peuvent exister pour une même ligne, et la version appropriée est choisie en fonction de l’ID de transaction

Niveaux d’isolation (Isolation Levels)

• Il s’agit d’un réglage qui contrôle les interférences entre transactions ; l’isolation est progressivement relâchée dans l’ordre Serializable → Repeatable Read → Read Committed → Read Uncommitted
• Serializable : toutes les transactions se comportent comme si elles étaient exécutées séquentiellement
• Repeatable Read : la réexécution de la même requête produit le même résultat, mais des phantom reads restent possibles
• Read Committed : il est possible de lire les modifications déjà validées par d’autres transactions
• Read Uncommitted : autorise les dirty reads ; c’est le niveau de protection le plus faible, mais les performances sont plus élevées

Écritures concurrentes (Concurrent Writes)

• La manière de traiter deux transactions qui modifient simultanément la même ligne varie selon la base de données

MySQL : verrouillage au niveau des lignes (Row-level Locking)

• Un verrou partagé (S lock) permet à plusieurs transactions de lire en même temps
• Un verrou exclusif (X lock) permet à une seule transaction de modifier une ligne
• En mode SERIALIZABLE, chaque mise à jour doit acquérir un X lock, ce qui peut provoquer un deadlock en cas de conflit
• MySQL détecte les deadlocks et met fin à l’une des transactions

Postgres : Serializable Snapshot Isolation

• Postgres utilise des predicate locks pour suivre les accès au niveau d’un ensemble de lignes
  • Exemple : verrou sur la condition WHERE id BETWEEN 10 AND 20
• Il ne bloque pas directement les accès réels, mais détecte les conflits et interrompt la transaction en cas de violation
• Cette résolution optimiste des conflits (optimistic conflict resolution) permet d’éviter les deadlocks
• Comme avec MySQL, une transaction est interrompue en cas de conflit et l’application doit implémenter une logique de nouvelle tentative

Conclusion

• Les transactions sont un composant central des bases de données et garantissent l’atomicité, la cohérence, l’isolation et la durabilité (ACID)
• Postgres et MySQL atteignent le même objectif à l’aide de structures internes différentes
• Comprendre les quatre niveaux d’isolation et le fonctionnement des transactions permet d’exploiter une base de données de manière plus fiable

[GN⁺]

Réactions sur Hacker News

• J’ai trouvé que cet article manquait un peu de matière
  Au lieu d’expliquer les niveaux d’isolation à partir des phénomènes (phenomena) définis par le standard SQL, il me semblerait plus intuitif de partir du concept de sérialisabilité (serializability)
  On peut voir la sérialisabilité comme une généralisation de la thread safety, et sa perte introduit des bugs où le résultat varie selon l’ordre d’exécution
  Les différents niveaux d’isolation des bases de données ne sont que des formes relâchées de cette garantie, et l’utilisateur doit alors assurer les garanties autrement
  Les phénomènes ne sont qu’un outil pour visualiser des situations non sérialisables, sans lien direct avec la sérialisabilité
  Par exemple, même un cluster Kubernetes peut se comporter de manière sérialisable avec des contrôleurs bien conçus

  • C’est l’auteur. Merci pour ce bon retour
    Traiter d’un coup les transactions, les niveaux d’isolation et le MVCC, jusqu’à comparer plusieurs SGBD, représente un travail énorme
    J’ai essayé de trouver un équilibre entre profondeur technique, accessibilité et longueur de l’article
  • Partage du lien Jepsen: analyse de MariaDB Galera Cluster
    Selon ce commentaire, il serait bien d’avoir davantage de notations et de citations
  • La plupart des SGBDR proposent une isolation sérialisable si nécessaire
    Mais l’utiliser sans besoin augmente le coût de coordination entre transactions, ce qui réduit la concurrence et le débit
  • Réponse suggérant alors de proposer une meilleure explication

• On peut aussi voir les transactions comme les snapshots d’un filesystem copy-on-write (btrfs, zfs), mais la métaphore des branches Git me paraît plus intuitive
  BEGIN correspond à la création d’une branche, UPDATE à un commit, ROLLBACK à la suppression de la branche, et COMMIT à un git merge
  En cas de conflit, la base tente une fusion au niveau des lignes et, si elle échoue, annule ou force la fusion selon la configuration
  READ UNCOMMITTED privilégie une fusion rapide, SERIALIZABLE la précision
  Ce genre d’analogie peut provoquer le déclic qui aide quelqu’un à comprendre les transactions

  • (Commentaire bref) Réaction laissant entendre la notion de concurrence

• Ce qui surprend beaucoup de gens, c’est que Postgres et MySQL ne sont pas en mode sérialisable par défaut, mais en read committed
  La différence de performance n’est pas “légère” : en pratique, elle est bien plus importante
  Avec read committed, il faut faire attention à la gestion des verrous (locks), et même les contraintes UNIQUE sont nécessaires pour éviter les conditions de concurrence
  Malgré cela, cette approche reste préférable aux pertes de performance et aux problèmes de retry du mode sérialisable
  Référence : documentation officielle PostgreSQL

  • Les versions récentes de MySQL et MariaDB (InnoDB) utilisent par défaut repeatable read
    Voir la documentation MySQL et la documentation MariaDB
    MyISAM n’est quasiment plus utilisé aujourd’hui
  • Le problème de SERIALIZABLE, ce n’est pas seulement la performance : les transactions peuvent aussi échouer à cause de conflits, deadlocks et timeouts
    L’application doit être capable de les détecter et de mettre en place une stratégie de retry
  • Oracle et SQL Server ont eux aussi read committed comme valeur par défaut
    Le mode sérialisable est séduisant dans les manuels, mais en pratique il est rarement utilisé

• Beaucoup d’outils de base de données privilégient aujourd’hui le partage de mises à jour en temps réel plutôt qu’ACID
  Par exemple, dans Airtable, une modification de champ apparaît immédiatement sur l’écran des collègues, mais l’absence de transactions crée un risque d’incohérence des données
  Voir aussi cet article du blog VisualDB

  • Réaction estimant que cela ressemble à une promo produit déguisée en critique d’un concurrent

• C’est vraiment un plaisir de lire le blog de PlanetScale
  Je me demande quels outils ont été utilisés pour les visualisations

  • C’est l’auteur. Merci !
    Les visualisations ont été réalisées en js + gsap(https://gsap.com)

• Si le sujet vous intéresse, je recommande vivement « Designing Data-Intensive Applications »
  Le livre couvre non seulement les différents niveaux d’isolation, mais aussi l’ambiguïté de la définition d’ACID
  J’ai entendu dire que la 2e édition sortait bientôt

  • La 2e édition est déjà publiée : lien O’Reilly

• Dans les systèmes MVCC comme Postgres, les transactions ressemblent aux snapshots d’un filesystem copy-on-write
  Au moment du BEGIN, un snapshot des données est créé, et les UPDATE ne s’appliquent qu’à une copie privée
  En cas de ROLLBACK, cette copie est jetée, et en cas de COMMIT, le nouveau snapshot devient la version officielle
  Cette analogie peut aider quelqu’un à clarifier sa compréhension des transactions
  P.S. On peut aussi utiliser l’analogie des branches Git

  • Ce n’est pas totalement exact. Les SGBD utilisent à la fois le branching et le locking
    Dans des cas comme SELECT puis UPDATE, un thread peut se retrouver bloqué
    Je vais tester aujourd’hui s’il est possible de transformer cela en une seule requête dans MySQL

• Avant, on posait souvent des questions sur les transactions en entretien backend
  Tout le monde en a déjà utilisé, mais le niveau de compréhension varie selon l’expérience
  Même sans connaître par cœur tous les niveaux d’isolation, savoir qu’ils se comportent différemment permet déjà de mesurer la curiosité et la compréhension des systèmes

  • Même avec le même nom, les niveaux d’isolation peuvent se comporter différemment selon la base ; il faut donc vérifier les détails au cas par cas

• L’explication de “phantom read” peut prêter à confusion
  En repeatable read, les valeurs des lignes existantes ne changent pas, mais de nouvelles lignes peuvent être ajoutées
  Comme les lignes existantes ne sont ni modifiées ni supprimées, il faudrait le préciser clairement

• La phrase disant que “cela n’a rien à voir avec xmin/xmax” paraît incomplète
  Le fait que la visualisation pointe l’en-tête de la table au moment du commit semble aussi étrange
  En réalité, xmax/xmin n’est-il pas le mécanisme central pour déterminer l’état de commit ?
  Si l’on prend en compte les sous-transactions, cela devient encore plus complexe
  Cela dit, j’ai globalement beaucoup apprécié les visualisations et les explications

  • Moi aussi, j’ai trouvé dommage que la notion de xmax/xmin soit absente
    C’est essentiel pour comprendre les niveaux d’isolation, au point qu’on a l’impression qu’une section manque