D4-C04 : Optimisation et sécurisation des bases de données avec MariaDB : Les transactions

1. Comprendre ce qu’est une transaction.
2. Connaître les propriétés ACID.
3. Savoir utiliser les commandes SQL liées aux transactions :START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.
4. Comprendre les niveaux d’isolation.

Les transactions sont une réponse générale aux problèmes de fiabilité et d'accès concurrents dans les BD, et en particulier dans les BD en mode client-serveur. Elles sont le fondement de toute implémentation robuste d'une BD. Un SGBDR ne fonctionne nativement qu'en mode transactionnel.

Les transactions sont une fonctionnalité absolument indispensable, permettant de sécuriser une application utilisant une base de données. Sans transactions, certaines opérations risqueraient d'être à moitié réalisées, et la moindre erreur, la moindre interruption(défaillance, concurrence) pourraient avoir des conséquences énormes sur la cohérence des données.

Les transactions permettent de regrouper des requêtes dans des blocs, et de faire en sorte que tout le bloc soit exécuté en une seule fois, cela afin de préserver l'intégrité des données de la base.

Les transactions ont été implémentées assez tard dans MySQL et ne sont pas utilisables pour tous les types de tables.

Une transaction, c'est un ensemble de requêtes qui sont exécutées en un seul bloc(unité logique de travail). Ainsi, si une des requêtes du bloc échoue, on peut décider d'annuler tout le bloc de requêtes (ou de quand même valider les requêtes qui ont réussi).

L'exécution d’une transaction assure le passage de la BD d'un état cohérent à un autre état cohérent.

Comment se déroule une transaction ?

1. On démarre une transaction.
2. On exécute les requêtes désirées une à une.
3. Si une des requêtes échoue, on annule toutes les requêtes, et on termine la transaction.
4. Par contre, si à la fin des requêtes, tout s'est bien passé, on valide tous les changements, et on termine la transaction.
5. Si le traitement est interrompu (entre deux requêtes par exemple), les changements ne sont jamais validés, et donc les données de la base restent les mêmes qu'avant la transaction.

Tant qu'une transaction n'a pas été terminée correctement, elle doit être assimilée à une tentative ou une mise à jour virtuelle, elle reste incertaine. Une fois terminée correctement la transaction ne peut plus être annulée par aucun moyen.

Imaginons un virement bancaire :

• Compte A est débité de 100 €
• Compte B est crédité de 100 €

Si la première opération réussit mais pas la seconde → incohérence !

Avec une transaction :

• Si les deux requêtes réussissent → COMMIT (valider)
• Si une échoue → ROLLBACK (annuler)

Une transaction doit respecter quatre propriétés fondamentales :

Les transactions constituent l'unité logique de travail, toute la transaction est exécutée ou bien rien du tout, mais jamais une partie seulement de la transaction = tout ou rien (exemple : un virement ne peut pas être fait à moitié).

Les transactions préservent la cohérence de la BD, c'est à dire qu'elle transforme la BD d'un état cohérent à un autre (sans nécessairement que les états intermédiaires internes de la BD au cours de l'exécution de la transaction respectent cette cohérence) = les règles de la base sont respectées (exemple : pas de solde négatif si interdit).

Les transactions sont isolées les unes des autres, c'est à dire que leur exécution est indépendante des autres transactions en cours. Elles accèdent donc à la BD comme si elles étaient seules à s'exécuter, avec comme corollaire que les résultats intermédiaires d'une transaction ne sont jamais accessibles aux autres transactions = une transaction ne voit pas les résultats temporaires des autres, deux utilisateurs ne se gênent pas

Les transactions assurent que les modifications qu'elles induisent perdurent, même en cas de défaillance du système = une fois validée, la transaction est enregistrée définitivement.

Le journal des transactions(appelé aussi journal des logs) est un fichier système qui constitue un espace de stockage redondant avec la BD. Il répertorie l'ensemble des mises à jour faites sur la BD (en particulier les valeurs des enregistrements avant et après mise à jour). Le journal est donc un historique persistant (donc en mémoire secondaire) qui mémorise tout ce qui se passe sur la BD.

Le journal est indispensable pour la validation (COMMIT), l'annulation (ROLLBACK) et la reprise après panne de transactions.

Dans phpMyadmin → Journal binaire

Il n'est pas possible d'utiliser les transactions sur n'importe quelle table. Pour les supporter, une table doit être transactionnelle, ce qui, avec MySQL, est défini par le moteur de stockage utilisé pour la table. Il existe différents moteurs de stockage possibles avec MySQL, dont les plus connus sont MyISAM et InnoDB. Nous n’avons utilisé que des tables créées avec le moteur InnoDB, ce qui tombe plutôt bien car en effet :

• les tables MyISAM sont non transactionnelles, donc ne supportent pas les transactions
• les tables InnoDB sont transactionnelles, donc supportent les transactions.

Lorsque l'on valide les requêtes d'une transaction, on dit aussi que l'on commite les changements. À l'inverse, l'annulation des requêtes s'appelle un rollback.

Par défaut, MySQL ne travaille pas avec les transactions. Chaque requête effectuée est directement commitée (validée). On ne peut pas revenir en arrière. On peut donc considérer que chaque requête constitue une transaction qui est automatiquement commitée. Par défaut, MySQL est donc en mode “autocommit”. Pour quitter ce mode, il suffit de lancer la requête suivante :

SET autocommit=0; 

Une fois que vous n'êtes plus en autocommit, chaque modification de donnée devra être commitée pour prendre effet.

Tant que vos modifications ne sont pas validées, vous pouvez à tout moment les annuler (faire un rollback).

A05