Exécuter les requêtes suivantes dans une même fenêtre d’exécution :

SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test0'); 

Maintenant exécuter les 3 requêtes suivantes dans une même fenêtre d’exécution :

SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test1'); 
Commit ; 

Exécuter les requêtes suivantes dans une même fenêtre d’exécution :

INSERT INTO genre (nomGenre) VALUES('test2');
SELECT * FROM genre; 

Exécuter les requêtes suivantes dans une même fenêtre d’exécution :

SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test3'); 
SELECT * FROM genre ;

Ensuite, tout en laissant ce client MySQL ouvert, ouvrez-en un deuxième. Connectez-vous comme d'habitude à la base de données biblio. Vous avez maintenant deux sessions ouvertes.

La table genre n’a pas changé. 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.

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.

Nous sommes toujours en mode autocommit activé, pour démarrer une transaction, il suffit de lancer la commande suivante :

 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.

START TRANSACTION; 
INSERT INTO genre (nomGenre) VALUES('testTransaction'); 
SELECT * FROM genre; 
ROLLBACK; 
SELECT * FROM genre; 

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.

Trois nouvelles commandes suffisent pour pouvoir utiliser pleinement les jalons, voici la SYNTAXE :

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 en 1 fois.

START TRANSACTION; 
insert into genre (nomGenre) values('testJalon1'); 
SAVEPOINT jalon1; 
insert into genre (nomGenre) values('testJalon2'); 
ROLLBACK TO SAVEPOINT jalon1; 
insert into genre (nomGenre) values('testJalon3'); 
COMMIT; 

On n'utilise qu'une seule transaction, on valide à la fin, et pourtant, la seconde insertion n'a pas été faite finalement, puisqu'elle a été annulée grâce au jalon. Seuls les genres « testjalon1 » et « testJalon3 » existent.

Ces exercices permettent de manipuler les transactions dans des cas concrets.

• Exercice 1 : Insérer un nouveau genre de livre

Table : genre(idGenre, nomGenre)

1. 1. SET autocommit=0;
2. 2. INSERT INTO genre (nomGenre) VALUES('Science-fiction');
3. 3. Vérifiez le contenu de la table avant COMMIT puis après COMMIT.

Correction Exercice 1

SET autocommit=0;
INSERT INTO genre (nomGenre) VALUES('Science-fiction');
SELECT * FROM genre; -- L’enregistrement n’est pas encore visible après fermeture de session
COMMIT; -- Rend le changement définitif

• Exercice 2 : Transfert bancaire

1. Créer une table compte(id, nom, solde).
2. Effectuer un transfert de 200 € du compte A vers le compte B avec une transaction.
3. Si une mise à jour échoue, tout doit être annulé.

Correction Exercice 2

START TRANSACTION;
UPDATE compte SET solde = solde - 200 WHERE id = 1;
UPDATE compte SET solde = solde + 200 WHERE id = 2;
COMMIT;
-- Si problème : ROLLBACK;

• Exercice 3 : SAVEPOINT

1. Créer plusieurs insertions dans une table avec un SAVEPOINT.
2. Annuler seulement une partie des requêtes.

Correction Exercice 3

START TRANSACTION;
INSERT INTO genre (nomGenre) VALUES('Test1');
SAVEPOINT s1;
INSERT INTO genre (nomGenre) VALUES('Test2');
ROLLBACK TO s1;
INSERT INTO genre (nomGenre) VALUES('Test3');
COMMIT;

• Exercice 4 : Concurrence

Ouvrir deux sessions MariaDB :

• - Dans la première, insérer un enregistrement sans COMMIT.
• - Dans la deuxième, vérifier que l’insertion n’est pas visible.

=== 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 de la clôturer, mais également de 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. Ces commandes forment ce que l'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. * 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 === Il n’est 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 partie suivante). === Chargement de données === Enfin, le chargement de données avec LOAD DATA provoque également une validation implicite.