Transaction
1. Problèmes majeurs lors d’exécution des requête
Un programme SQL ne s’exécute pas indépendamment des autres : grandes bases de données
peuvent gérer des centaines, voire des milliers d’accès concurrents qui peuvent interagir les
uns sur les autres.
Un programme ne s’exécute pas sans erreur et non plus intégralement : pour des raisons
innombrables : plantage de l’application, pb réseau, problème du serveur, panne électrique,
etc. L’interruption peut laisser la base dans un état incohérent.
2. Définitions
Une transaction est une séquence d’opérations de lecture ou d’écriture, se terminant par commit ou
rollback.
Le commit est une instruction qui valide toutes les mises à jour.
Le rollback est une instruction qui annule toutes les mises à jour.
Les opérations d’une transaction sont solidaires : elles sont toutes validées, ou pas du tout (atomicité).
Une transaction est le produit d’un échange entre un processus client et un processus serveur
(SGBD).
On peut effectuer une ou plusieurs transactions successives dans un même processus : elles sont dites
sérielles.
En revanche, deux processus distincts engendrent des transactions concurrentes.
Une transaction, c'est un ensemble de requêtes qui sont exécutées en un seul bloc.
Si une des requêtes du bloc échoue, on peut prendre une décision d'annuler tout le bloc de
requêtes ou valider les requêtes qui ont réussi (validation partielle).
Déroulement d'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.
i
Début de la transaction
Exécution de la requête suivante
NON Requête
OK?
OUI
Dernière NON
requête?
OUI
NON
Toutes
est OK?
OUI
Validation des requêtes
Annulation de la
transaction
Fin de la transaction
ii
Support des transactions
Une table ne supporte pas forcement la transaction ; pour qu’elle supporte la transaction, il
faut qu’elle soit transactionnelle ; c’est-à-dire le moteur de base de données ou moteur de
stockage qu’elle utilise doit supporter la transaction.
Un moteur de base de données (anglais database engine ou storage engine) est
un composant logiciel qui contrôle, lit, enregistre et trie des informations dans une ou
plusieurs bases de données
Pour le cas de MySQL, il existe des différents moteurs de stockage possibles, dont les plus
connus sont MyISAM et InnoDB.
MyISAM ne supportant pas les contraintes de clés étrangères, en effet : les tables MyISAM sont
non-transactionnelles, donc ne supportent pas les transactions ; les tables InnoDB sont
transactionnelles, donc supportent les transactions.
Pour la suite de ce cours, nous allons utiliser le moteur InnoDB,
Les moteurs de tables ou moteurs de stockage
Pour Mysql, les deux moteurs les plus connus sont MyISAMet InnoDB.
MyISAM
C'est le moteur par défaut. Les commandes d'insertion et sélection de données sont
particulièrement rapides sur les tables utilisant ce moteur. Cependant, il ne gère pas
certaines fonctionnalités importantes comme les clés étrangères, qui permettent de vérifier
l'intégrité d'une référence d'une table à une autre table ou les transactions, qui permettent
de réaliser des séries de modifications "en bloc" ou au contraire d'annuler ces modifications.
InnoDB
Plus lent et plus gourmand en ressources que MyISAM, ce moteur gère les clés étrangères et
les transactions.
De plus, en cas de crash du serveur, il possède un système de récupération automatique des
données.
Préciser un moteur lors de la création d’une table
Pour qu'une table utilise le moteur de notre choix, il suffit d'ajouter la syntaxe suivante à la
fin de la commande de création :
iii
Code SQL
ENGINE = moteur;
En remplaçant bien sûr "moteur" par le nom du moteur que nous voulons utiliser, dans notre
cas c’est InnoDB :
Code SQL
ENGINE = INNODB;
Syntaxe et utilisation
Vocabulaire
Commit: validation des requêtes d'une transaction (commutation des changements)
Rollback: l'annulation des requêtes
Comportement par défaut
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 en fait
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 :
Code SQL
SET autocommit=0;
Une fois que vous n'êtes plus en mode 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).
Valider/annuler les changements
Les commandes pour commiter et faire un rollback sont relativement faciles à retenir :
Code SQL
iv
COMMIT; -- pour valider les requêtes
ROLLBACK; -- pour annuler les requêtes
Démarrer explicitement une transaction
En désactivant le mode autocommit, en réalité, on démarre une transaction. Et chaque fois
que l'on fait un rollback ou un commit (ce qui met fin à la transaction), une nouvelle
transaction est créée automatiquement, et ce tant que la session est ouverte.
Il est également possible de démarrer explicitement une transaction, auquel cas on peut
laisser le mode autocommit activé, et décider au cas par cas des requêtes qui doivent être
faites dans une transaction.
Repassons donc en mode autocommit :
Code SQL
SET autocommit=1;
Pour démarrer une transaction, il suffit de lancer la commande suivante :
Code SQL
START TRANSACTION;
Avec MySQL, il est également possible de démarrer une transaction avec BEGIN ou BEGIN
WORK. Cependant, il est conseillé d'utiliser plutôt START TRANSACTION, car il s'agit de la
commande SQL standard.
Une fois la transaction ouverte, les requêtes devront être validées pour prendre effet.
Attention au fait qu'un COMMIT ou un ROLLBACK met fin automatiquement à la transaction,
donc les commandes suivantes seront à nouveau commitées automatiquement si une nouvelle
transaction n'est pas ouverte.
Exemples de transactions en mode autocommit
Code : SQL
-- Insertion d'un nouveau rat brun, plus vieux
INSERT INTO Animal (nom, espece_id, date_naissance, sexe)
VALUES ('Momy', 5, '2008-02-01 02:25:00', 'F');
-- Ouverture d'une transaction
START TRANSACTION;
-- La nouvelle rate est la mère de Buba et Baba
UPDATE Animal SET mere_id= LAST_INSERT_ID() WHERE espece_id= 5
AND nom IN ('Baba', 'Buba');
-- On annule les requêtes de la transaction, ce qui termine celleci
v
ROLLBACK;
-- La nouvelle rate est la mère de Bibo
UPDATE Animal
SET mere_id= LAST_INSERT_ID()
WHERE espece_id= 5AND nom = 'Bibo';
-- Nouvelle transaction
START TRANSACTION;
-- Suppression de Buba
DELETE FROM Animal
WHERE espece_id= 5
AND nom = 'Buba';
-- On valide les requêtes de la transaction, ce qui termine celle-ci
COMMIT;
Jalon de transaction (points de repère de la transaction)
Lorsque l'on travaille dans une transaction, et que l'on constate que certaines requêtes posent
problème, on n'a pas toujours envie de faire un rollback depuis le début de la transaction,
annulant toutes les requêtes alors qu'une partie aurait pu être validée.
Il n'est pas possible de démarrer une transaction à l'intérieur d'une transaction. Par
contre, on peut poser des jalons de transaction. Il s'agit de points de repère, qui permettent
d'annuler toutes les requêtes exécutées depuis ce jalon, et non toutes les requêtes de la
transaction.
Syntaxe
Trois nouvelles commandes suffisent pour pouvoir utiliser pleinement les jalons :
Code : SQL
SAVEPOINT nom_jalon; -- Crée un jalon avec comme nom "nom_jalon"
ROLLBACK [WORK] TO [SAVEPOINT] nom_jalon; -- Annule les requêtes exécutées depuis le
jalon "nom_jalon", WORK et SAVEPOINT ne sont pas obligatoires
RELEASE SAVEPOINT nom_jalon; -- Retire le jalon "nom_jalon" (sans annuler, ni valider les
requêtes faites depuis)
Exemple : exécutez les requêtes suivantes.
vi
Code : SQL
START TRANSACTION;
INSERT INTO Animal (nom, espece_id, date_naissance, sexe)
VALUES ('Popi', 5, '2007-03-11 12:45:00', 'M');
SAVEPOINT jalon1;
INSERT INTO Animal (nom, espece_id, date_naissance, sexe)
VALUES ('Momo', 5, '2007-03-12 05:23:00', 'M');
ROLLBACK TO SAVEPOINT jalon1;
INSERT INTO Animal (nom, espece_id, date_naissance, sexe)
VALUES ('Mimi', 5, '2007-03-12 22:03:00', 'F');
COMMIT;
On n'utilise qu'une seule transaction, on valide à la fin, et pourtant la seconde insertion n'a
pas été faite au final, puisqu'elle a été annulée grâce au jalon. Seuls Popi et Mimi existent.
Validation implicite et commandes non-annulables
Vous savez déjà que pour terminer une transaction, il faut utiliser les commandes COMMIT ou
ROLLBACK, selon que l'on veut valider les requêtes ou les annuler.
Ça, c'est la manière classique et recommandée. Mais il faut savoir qu'un certain nombre
d'autres commandes auront aussi pour effet de clôturer une transaction. Et pas seulement la
clôturer, mais également valider toutes les requêtes qui ont été faites dans cette
transaction. Exactement comme si vous utilisiez COMMIT.
Par ailleurs, ces commandes ne peuvent pas être annulées par un ROLLBACK.
Commandes DDL (LDD)
Toutes les commandes qui créent, modifient, suppriment des objets dans la base de données
valident implicitement les transactions.
Ces commandes forment ce qu'on appelle les requêtes DDL, pour Data Definition Langage.
Cela comprend donc :
la création et suppression de bases de données : CREATE DATABASE, DROP DATABASE
;
la création, modification, suppression de tables : CREATE TABLE, ALTER TABLE,
RENAME TABLE, DROP TABLE ;
la création, modification, suppression d'index : CREATE INDEX, DROP INDEX ;
la création d'objets comme les procédures stockées, les vues, etc., dont nous parlerons
plus tard.
vii
De manière générale, tout ce qui influe sur la structure de la base de données, et non sur
les données elles-mêmes.
Utilisateurs
La création, la modification et la suppression d'utilisateurs provoquent aussi une validation
implicite.
Transactions et verrous
Je vous ai signalé qu'il n'était pas possible d'imbriquer des transactions, donc d'avoir une
transaction à l'intérieur d'une transaction. En fait, la commande START TRANSACTION
provoque également une validation implicite si elle est exécutée à l'intérieur d'une
transaction.
Le fait d'activer le mode autocommit(s'il n'était pas déjà activé) a le même effet.
La création et suppression de verrous de table clôturent aussi une transaction en la validant
implicitement.
Chargements de données
Enfin, le chargement de données avec LOAD DATA provoque également une validation
implicite.
La commande LOAD DATA INFILE lit les lignes dans un fichier texte et les insère à très grande
vitesse.
Pour être fiable les systèmes qui utilisent la transaction doit respecter les critères ACID:
Atomicité, Cohérence, Isolation et Durabilité.
A pour Atomicité
Atome signifie étymologiquement "qui ne peut être divisé".
Une transaction doit être atomique, c'est-à-dire qu'elle doit former une entité complète et
indivisible. Chaque élément de la transaction, chaque requête effectuée, ne peut exister que
dans la transaction.
C pour cohérence
Les données doivent rester cohérentes dans tous les cas : que la transaction se termine sans
encombre, qu'une erreur survienne, ou que la transaction soit interrompue
I pour Isolation
Chaque transaction doit être isolée, donc ne pas interagir avec une autre transaction.
D pour Durabilité
viii
Une fois la transaction terminée, les données résultant de cette transaction doivent être
stockées de manière durable, et pouvoir être récupérées, en cas de crash du serveur par
exemple.
A = Atomicité. Une transaction est validée complètement ou pas du tout.
C = Cohérence. Une transaction mène d’un état cohérent à un autre état cohérent.
I = Isolation. Une transaction s’exécute comme si elle était seule.
D = Durabilité. Quand le commit s’exécute, ses résultats sont définitifs.
En résumé
Les transactions permettent de grouper plusieurs requêtes, lesquelles seront validées
(COMMIT) ou annulées (ROLLBACK) toutes en même temps.
Tous les changements de données (insertion, suppression, modification) faits par les requêtes
à l'intérieur d'une transaction sont invisibles pour les autres sessions tant que la transaction
n'est pas validée.
Les transactions permettent d'exécuter un traitement nécessitant plusieurs requêtes en
une seule fois, ou de l'annuler complètement si une des requêtes pose problème ou si la
transaction est interrompue.
Certaines commandes SQL provoquent une validation implicite des transactions, notamment
toutes les commandes DDL, c'est-à-dire les commandes qui créent, modifient ou suppriment
des objets dans la base de données (tables, index,…).
Les critères ACID sont les critères qu'un système appliquant les transactions doit respecter
pour être fiable : Atomicité, Cohérence, Isolation, Durabilité.
ix