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