Transactions

• Sécuriser et automatiser ses actions
• Sommaire
• Verrous

Pour commencer cette partie, nous allons voir ce que sont les transactions, à quoi elles servent exactement, et comment les utiliser avec MySQL.

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 pourrait avoir des conséquences énormes. En effet, 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 qui plus est, elles ne sont pas utilisables pour tous les types de tables. C'est d'ailleurs un des principaux arguments des détracteurs de MySQL.

Etat actuel de la base de données

Note : les tables de test ne sont pas reprises.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

SET NAMES utf8;


DROP TABLE IF EXISTS Animal;
DROP TABLE IF EXISTS Race;
DROP TABLE IF EXISTS Espece;


CREATE TABLE Espece (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom_courant varchar(40) NOT NULL,
  nom_latin varchar(40) NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY nom_latin (nom_latin)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

LOCK TABLES Espece WRITE;
INSERT INTO Espece VALUES (1,'Chien','Canis canis','Bestiole à quatre pattes qui aime les caresses et tire souvent la langue',200.00),(2,'Chat','Felis silvestris','Bestiole à quatre pattes qui saute très haut et grimpe aux arbres',150.00),(3,'Tortue d''Hermann','Testudo hermanni','Bestiole avec une carapace très dure',140.00),
(4,'Perroquet amazone','Alipiopsitta xanthops','Joli oiseau parleur vert et jaune',700.00),(5,'Rat brun','Rattus norvegicus','Petite bestiole avec de longues moustaches et une longue queue sans poils',10.00);
UNLOCK TABLES;


CREATE TABLE Race (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom varchar(40) NOT NULL,
  espece_id smallint(6) unsigned NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

LOCK TABLES Race WRITE;
INSERT INTO Race VALUES (1,'Berger allemand',1,'Chien sportif et élégant au pelage dense, noir-marron-fauve, noir ou gris.',485.00),(2,'Berger blanc suisse',1,'Petit chien au corps compact, avec des pattes courtes mais bien proportionnées et au pelage tricolore ou bicolore.',935.00),(3,'Singapura',2,'Chat de petite taille aux grands yeux en amandes.',985.00),
(4,'Bleu russe',2,'Chat aux yeux verts et à la robe épaisse et argentée.',835.00),(5,'Maine coon',2,'Chat de grande taille, à poils mi-longs.',735.00),(7,'Sphynx',2,'Chat sans poils.',1235.00),
(8,'Nebelung',2,'Chat bleu russe, mais avec des poils longs...',985.00),(9,'Rottweiller',1,'Chien d''apparence solide, bien musclé, à la robe noire avec des taches feu bien délimitées.',600.00);
UNLOCK TABLES;


CREATE TABLE Animal (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  sexe char(1) DEFAULT NULL,
  date_naissance datetime NOT NULL,
  nom varchar(30) DEFAULT NULL,
  commentaires text,
  espece_id smallint(6) unsigned NOT NULL,
  race_id smallint(6) unsigned DEFAULT NULL,
  mere_id smallint(6) unsigned DEFAULT NULL,
  pere_id smallint(6) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ind_uni_nom_espece_id (nom,espece_id)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

LOCK TABLES Animal WRITE;
INSERT INTO Animal VALUES (1,'M','2010-04-05 13:43:00','Rox','Mordille beaucoup',1,1,18,22),(2,NULL,'2010-03-24 02:23:00','Roucky',NULL,2,NULL,40,30),(3,'F','2010-09-13 15:02:00','Schtroumpfette',NULL,2,4,41,31),
(4,'F','2009-08-03 05:12:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(5,NULL,'2010-10-03 16:44:00','Choupi','Né sans oreille gauche',2,NULL,NULL,NULL),(6,'F','2009-06-13 08:17:00','Bobosse','Carapace bizarre',3,NULL,NULL,NULL),
(7,'F','2008-12-06 05:18:00','Caroline',NULL,1,2,NULL,NULL),(8,'M','2008-09-11 15:38:00','Bagherra',NULL,2,5,NULL,NULL),(9,NULL,'2010-08-23 05:18:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL),
(10,'M','2010-07-21 15:41:00','Bobo',NULL,1,NULL,7,21),(11,'F','2008-02-20 15:45:00','Canaille',NULL,1,NULL,NULL,NULL),(12,'F','2009-05-26 08:54:00','Cali',NULL,1,2,NULL,NULL),
(13,'F','2007-04-24 12:54:00','Rouquine',NULL,1,1,NULL,NULL),(14,'F','2009-05-26 08:56:00','Fila',NULL,1,2,NULL,NULL),(15,'F','2008-02-20 15:47:00','Anya',NULL,1,NULL,NULL,NULL),
(16,'F','2009-05-26 08:50:00','Louya',NULL,1,NULL,NULL,NULL),(17,'F','2008-03-10 13:45:00','Welva',NULL,1,NULL,NULL,NULL),(18,'F','2007-04-24 12:59:00','Zira',NULL,1,1,NULL,NULL),
(19,'F','2009-05-26 09:02:00','Java',NULL,1,2,NULL,NULL),(20,'M','2007-04-24 12:45:00','Balou',NULL,1,1,NULL,NULL),(21,'F','2008-03-10 13:43:00','Pataude',NULL,1,NULL,NULL,NULL),
(22,'M','2007-04-24 12:42:00','Bouli',NULL,1,1,NULL,NULL),(24,'M','2007-04-12 05:23:00','Cartouche',NULL,1,NULL,NULL,NULL),(25,'M','2006-05-14 15:50:00','Zambo',NULL,1,1,NULL,NULL),
(26,'M','2006-05-14 15:48:00','Samba',NULL,1,1,NULL,NULL),(27,'M','2008-03-10 13:40:00','Moka',NULL,1,NULL,NULL,NULL),(28,'M','2006-05-14 15:40:00','Pilou',NULL,1,1,NULL,NULL),
(29,'M','2009-05-14 06:30:00','Fiero',NULL,2,3,NULL,NULL),(30,'M','2007-03-12 12:05:00','Zonko',NULL,2,5,NULL,NULL),(31,'M','2008-02-20 15:45:00','Filou',NULL,2,4,NULL,NULL),
(32,'M','2009-07-26 11:52:00','Spoutnik',NULL,3,NULL,52,NULL),(33,'M','2006-05-19 16:17:00','Caribou',NULL,2,4,NULL,NULL),(34,'M','2008-04-20 03:22:00','Capou',NULL,2,5,NULL,NULL),
(35,'M','2006-05-19 16:56:00','Raccou','Pas de queue depuis la naissance',2,4,NULL,NULL),(36,'M','2009-05-14 06:42:00','Boucan',NULL,2,3,NULL,NULL),(37,'F','2006-05-19 16:06:00','Callune',NULL,2,8,NULL,NULL),
(38,'F','2009-05-14 06:45:00','Boule',NULL,2,3,NULL,NULL),(39,'F','2008-04-20 03:26:00','Zara',NULL,2,5,NULL,NULL),(40,'F','2007-03-12 12:00:00','Milla',NULL,2,5,NULL,NULL),
(41,'F','2006-05-19 15:59:00','Feta',NULL,2,4,NULL,NULL),(42,'F','2008-04-20 03:20:00','Bilba','Sourde de l''oreille droite à 80%',2,5,NULL,NULL),(43,'F','2007-03-12 11:54:00','Cracotte',NULL,2,5,NULL,NULL),
(44,'F','2006-05-19 16:16:00','Cawette',NULL,2,8,NULL,NULL),(45,'F','2007-04-01 18:17:00','Nikki','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(46,'F','2009-03-24 08:23:00','Tortilla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),
(47,'F','2009-03-26 01:24:00','Scroupy','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(48,'F','2006-03-15 14:56:00','Lulla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(49,'F','2008-03-15 12:02:00','Dana','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),
(50,'F','2009-05-25 19:57:00','Cheli','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(51,'F','2007-04-01 03:54:00','Chicaca','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(52,'F','2006-03-15 14:26:00','Redbul','Insomniaque',3,NULL,NULL,NULL),
(54,'M','2008-03-16 08:20:00','Bubulle','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),(55,'M','2008-03-15 18:45:00','Relou','Surpoids',3,NULL,NULL,NULL),(56,'M','2009-05-25 18:54:00','Bulbizard','Bestiole avec une carapace très dure',3,NULL,NULL,NULL),
(57,'M','2007-03-04 19:36:00','Safran','Coco veut un gâteau !',4,NULL,NULL,NULL),(58,'M','2008-02-20 02:50:00','Gingko','Coco veut un gâteau !',4,NULL,NULL,NULL),(59,'M','2009-03-26 08:28:00','Bavard','Coco veut un gâteau !',4,NULL,NULL,NULL),
(60,'F','2009-03-26 07:55:00','Parlotte','Coco veut un gâteau !',4,NULL,NULL,NULL),(61,'M','2010-11-09 00:00:00','Yoda',NULL,2,5,NULL,NULL),(62,'M','2010-11-05 00:00:00','Pipo',NULL,1,9,NULL,NULL);
UNLOCK TABLES;


ALTER TABLE Race ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id) ON DELETE CASCADE;

ALTER TABLE Animal ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race (id) ON DELETE SET NULL;
ALTER TABLE Animal ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);
ALTER TABLE Animal ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal (id) ON DELETE SET NULL;
ALTER TABLE Animal ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal (id) ON DELETE SET NULL;

• Principe
• Syntaxe et utilisation
• Validation implicite et commandes non-annulables
• ACID

Principe

Une transaction, c'est un ensemble de requêtes qui sont exécutées en un seul bloc. 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).

Imaginez que Monsieur Durant fasse un virement de 300 euros à Monsieur Dupont via sa banque en ligne. Il remplit toutes les petites cases du virement, puis valide. L'application de la banque commence à traiter le virement quand soudain, une violente panne de courant provoque l'arrêt des serveurs de la banque.

Deux jours plus tard, Monsieur Durant reçoit un coup de fil de Monsieur Dupont, très énervé, qui lui demande pourquoi le paiement convenu n'a toujours pas été fait. Intrigué, Monsieur Durant va vérifier son compte, et constate qu'il a bien été débité de 300 euros.

Normalement, le traitement d'un virement est plutôt simple, deux étapes suffisent :

• étape 1 : on retire le montant du virement du compte du donneur d'ordre ;
• étape 2 : on ajoute le montant du virement au compte du bénéficiaire.

Seulement voilà, pas de chance pour Monsieur Durant, la panne de courant qui a éteint les serveurs est survenue pile entre l'étape 1 et l'étape 2. Du coup, son compte a été débité, mais le compte de Monsieur Dupont n'a jamais été crédité.

La banque de Monsieur Durant n'utilisait pas les transactions. Si c'était le cas, la seconde requête du traitement n'ayant jamais été exécutée, la première requête n'aurait jamais été validée.

Comment se déroule une transaction ?

Voici un schéma qui devrait vous éclairer sur le principe des transactions.

• On démarre une transaction.
• On exécute les requêtes désirées une à une.
• Si une des requêtes échoue, on annule toutes les requêtes, et on termine la transaction.
• Par contre, si à la fin des requêtes, tout s'est bien passé, on valide tous les changements, et on termine la transaction.
• 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.

Support des transactions

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.

Rappelez-vous, nous avons vu dans le chapitre sur la création des tables qu'il existait différents moteurs de stockage possibles avec MySQL, dont les plus connus sont MyISAM et InnoDB.

MyISAM ne supportant pas les contraintes de clés étrangères, nos tables ont été créées avec le moteur InnoDB, ce qui tombe plutôt bien pour la suite de ce chapitre. En effet :

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

Syntaxe et utilisation

Vocabulaire

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.

Comportement par défaut

Vous l'aurez compris, 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 :

1

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).

Valider/annuler les changements

Les commandes pour commiter et faire un rollback sont relativement faciles à retenir :

1
2

COMMIT;   -- pour valider les requêtes
ROLLBACK; -- pour annuler les requêtes

Exemples de transactions en mode non-autocommit

Si ce n'est pas déjà fait, changez le mode par défaut de MySQL grâce à la commande que nous venons de voir.

Première expérience : annulation des requêtes.

Exécutez ces quelques requêtes :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Baba', 5, '2012-02-13 15:45:00', 'F'); 
INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Bibo', 5, '2012-02-13 15:48:00', 'M');
INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Buba', 5, '2012-02-13 18:32:00', 'F'); -- Insertion de 3 rats bruns

UPDATE Espece
SET prix = 20
WHERE id = 5;  -- Les rats bruns coûtent maintenant 20 euros au lieu de 10

Faites maintenant un SELECT sur les tables Espece et Animal.

1
2
3
4
5
6
7

SELECT * 
FROM Animal
WHERE espece_id = 5;

SELECT * 
FROM Espece 
WHERE id = 5;

Les changements faits sont bien visibles. Les rats bruns valent maintenant 20 euros, et nos trois nouvelles bestioles ont bien été insérées. Cependant, un simple rollback va annuler ces changements.

1

ROLLBACK;

Nos rats coûtent à nouveau 10 euros et Baba, Bibo et Buba ont disparu.

Deuxième expérience : Interruption de la transaction.

Exécutez à nouveau les trois requêtes INSERT et la requête UPDATE. Ensuite, quittez votre client MySQL (fermez simplement la fenêtre, ou tapez quit ou exit).

Reconnectez-vous et vérifiez vos données : les rats valent 10 euros, et Baba, Bibo et Buba n'existent pas. Les changements n'ont pas été commités, c'est comme s'il ne s'était rien passé !

Troisième expérience : validation et annulation.

Exécutez la séquence de requêtes suivante :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Baba', 5, '2012-02-13 15:45:00', 'F'); 
INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Bibo', 5, '2012-02-13 15:48:00', 'M');
INSERT INTO Animal (nom, espece_id, date_naissance, sexe) 
VALUES ('Buba', 5, '2012-02-13 18:32:00', 'F'); -- Insertion de 3 rats bruns

COMMIT;

UPDATE Espece
SET prix = 20
WHERE id = 5;  -- Les rats valent 20 euros

ROLLBACK;

Si vous n'avez pas oublié de réactiver le mode non-autocommit, vous avez maintenant trois nouveaux rats bruns (les requêtes d'insertion ayant été validées), et ils ne valent toujours que 10 euros chacun (la modification de l'espèce ayant été annulée).

Quatrième expérience : visibilité des changements non-commités.

Exécutez la requête suivante :

1
2
3

UPDATE Animal 
SET commentaires = 'Queue coupée'
WHERE nom = 'Bibo' AND espece_id = 5;

Ensuite, tout en laissant ce client MySQL ouvert, ouvrez-en un deuxième. Connectez-vous comme d'habitude à la base de données elevage. Vous avez maintenant deux sessions ouvertes, connectées à votre base de données. Sélectionnez les rats bruns.

1
2
3

SELECT id, sexe, nom, commentaires, espece_id, race_id 
FROM Animal
WHERE espece_id = 5;

Les commentaires de Bibo sont toujours vides. Les changements non-commités ne sont donc pas visibles à l'extérieur de la transaction qui les a faits. En particulier, une autre session n'a pas accès à ces changements.

Annulez la modification de Bibo dans la première session avec un ROLLBACK. Vous pouvez fermer la seconde session.

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 :

1

SET autocommit=1;

Pour démarrer une transaction, il suffit de lancer la commande suivante :

1

START TRANSACTION;

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 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 celle-ci
ROLLBACK;

-- La nouvelle rate est la mère de Bibo
UPDATE Animal 
SET mere_id = LAST_INSERT_ID()
WHERE espece_id = 5
AND 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;

Si vous avez bien suivi, vous devriez savoir les changements qui ont été faits.

1
2
3

SELECT id, nom, espece_id, mere_id 
FROM Animal
WHERE espece_id = 5;

Jalon de 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 :

1
2
3
4
5

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

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.

1
2
3

SELECT id, sexe, date_naissance, nom, espece_id, mere_id, pere_id
FROM Animal
WHERE espece_id = 5;

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

Toutes les commandes qui créent, modifient, suppriment des objets dans la base de données valident implicitement les transactions.

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.

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 (voir partie 7) 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 (voir chapitre suivant).

Chargements de données

Enfin, le chargement de données avec LOAD DATA provoque également une validation implicite.

ACID

Derrière ce titre mystérieux se cache un concept très important !

Il a été défini que ces critères sont au nombre de quatre : Atomicité, Cohérence, Isolation et Durabilité. Soit, si on prend la première lettre de chaque critère : ACID. Voyons donc en détail ces quatre critères.

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.

Si l'on reprend l'exemple du virement bancaire, en utilisant les transactions, les deux étapes (débit du compte donneur d'ordre, crédit du compte bénéficiaire) ne peuvent exister indépendamment l'une de l'autre. Si l'une est exécutée, l'autre doit l'être également. Il s'agit d'un tout.

Oui. Si une transaction en cours est interrompue, aucune des requêtes exécutées ne sera validée. De même, en cas d'erreur, il suffit de faire un ROLLBACK pour annuler toute la transaction. Et si tout se passe bien, un COMMIT validera l'intégralité de la transaction en une fois.

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. Un virement dont seule l'étape de débit du donneur d'ordre est exécutée produit des données incohérentes (la disparition de 300 euros jamais arrivés chez le bénéficiaire). Avec une transaction, cette incohérence n'apparaît jamais. Tant que la totalité des étapes n'a pas été réalisée avec succès, les données restent dans leur état initial.

Oui, les changements de données ne sont validés qu'une fois que toutes les étapes ont été exécutées. De l'extérieur de la transaction, le moment entre les deux étapes d'un virement n'est jamais visible.

I pour Isolation

Chaque transaction doit être isolée, donc ne pas interagir avec une autre transaction.

Test

Dans votre client MySQL, exécutez les requêtes suivantes (ne commitez pas) pour modifier le pere_id du rat Bibo :

1
2
3
4
5
6
7
8
9

START TRANSACTION; -- On ouvre une transaction

UPDATE Animal     -- On modifie Bibo
SET pere_id = 73
WHERE espece_id = 5 AND nom = 'Bibo';

SELECT id, nom, commentaires, pere_id, mere_id
FROM Animal
WHERE espece_id = 5;

À nouveau, ouvrez une deuxième session, tout en laissant la première ouverte (démarrez un deuxième client SQL et connectez-vous à votre base de données). Exécutez les requêtes suivantes, pour modifier les commentaires de Bibo.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

START TRANSACTION;   -- On ouvre une transaction

SELECT id, nom, commentaires, pere_id, mere_id
FROM Animal
WHERE espece_id = 5;

UPDATE Animal       -- On modifie la perruche Bibo
SET commentaires = 'Agressif'
WHERE espece_id = 5 AND nom = 'Bibo';

SELECT id, nom, commentaires, pere_id, mere_id
FROM Animal
WHERE espece_id = 5;

Le résultat n'est pas du tout le même dans les deux sessions. En effet, dans la première, on a la confirmation que la requête UPDATE a été effectuée :

1
2

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Et le SELECT renvoie bien les données modifiées (pere_id n'est plus NULL pour Bibo) :

Par contre, dans la deuxième session, on a d'abord fait un SELECT, et Bibo n'a toujours pas de père (puisque ça n'a pas été commité dans la première session). Donc on s'attendrait à ce que la requête UPDATE laisse pere_id à NULL et modifie commentaires.

Seulement voilà, la requête UPDATE ne fait rien ! La session semble bloquée : pas de message de confirmation après la requête UPDATE, et le second SELECT n'a pas été effectué.

1
2
3
4
5

mysql>
mysql> UPDATE Animal       -- On modifie Bibo
    -> SET commentaires = 'Agressif'
    -> WHERE espece_id = 5 AND nom = 'Bibo';
_

Commitez maintenant les changements dans la première session (celle qui n'est pas bloquée). Retournez voir dans la seconde session : elle s'est débloquée et indique maintenant un message de confirmation aussi :

1
2

Query OK, 1 row affected (5.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Qui plus est, le SELECT a été exécuté (vous devrez peut-être appuyer sur Entrée pour que ce soit envoyé au serveur) et les modifications ayant été faites par la session 1 ont été prises en compte : commentaires vaut 'Agressif' et pere_id vaut 73 !

Il n'y a plus qu'à commiter les changements faits par la deuxième session, et c'est terminé ! Si vous ne commitez pas, commentaires restera NULL. Par contre, pere_id vaudra toujours 73 puisque ce changement-là a été commité par la première session.

Conclusion

La deuxième session n'a pas interagi avec les changements faits par la première session, chaque transaction est bien isolée.

Pas dans le cadre des critères ACID. Oui, la première session provoque un retard dans l'exécution des requêtes de la deuxième session, mais les critères de fiabilité que nous examinons ici concernent les données impactées par les transactions, et non le déroulement de celles-ci (qui importe peu finalement). Ce blocage a pour effet d'empêcher la deuxième session d'écraser un changement fait par la première. Donc, ce blocage a bien pour effet l'isolation des transactions.

Verrous

Le blocage de la deuxième session vient en fait de ce que la première session, en faisant sa requête UPDATE, a automatiquement posé un verrou sur la ligne contenant Bobi le rat, empêchant toute modification tant que la transaction était en cours. Les verrous faisant l'objet du prochain chapitre, je n'en dis pas plus pour l'instant.

Utilité

Je vous l'accorde, vous n'allez pas vous amuser tous les jours à ouvrir deux sessions MySQL. Par contre, pour une application pouvant être utilisée par plusieurs personnes en même temps (qui toutes travaillent sur la même base de données), il est impératif que ce critère soit respecté. Prenons l'exemple simple d'un jeu par navigateur : de nombreux joueurs peuvent être connectés en même temps, et effectuer des opérations différentes. Si les transactions ne sont pas isolées, une partie des actions des joueurs risquerait de se voir annulées. On isole donc les transactions grâce aux verrous (qui sont ici automatiquement posés mais ce n'est pas toujours le cas).

D pour Durabilité

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.

Oui, une fois les changements commités, ils sont stockés définitivement (jusqu'à modification par une nouvelle transaction).

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é.

• Sécuriser et automatiser ses actions
• Sommaire
• Verrous