d4:a05 [SIO Wiki]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
===== D4-A05 : Application des transactions sur la base de données Biblio =====
 
==== 1) Validation/annulation de requêtes ====
 
Exécuter les requêtes suivantes dans une même fenêtre d’exécution :
<code>SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test0'); 
</code>
<bootnote question> Le genre « test0 » a-t-il été ajouté à la table genre ? Pourquoi ?  </bootnote> 

Maintenant exécuter les 3 requêtes suivantes dans une même fenêtre d’exécution :
<code>SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test1'); 
Commit ; 
</code>
<bootnote question>Le genre « test1 » a-t-il été ajouté à la table genre ? Pourquoi ?</bootnote>
 
Exécuter les requêtes suivantes dans une même fenêtre d’exécution :
<code>INSERT INTO genre (nomGenre) VALUES('test2');
SELECT * FROM genre; 
</code> 
<bootnote question>Le genre « test2 » a-t-il été ajouté à la table genre ? Pourquoi ?</bootnote>
 
<bootnote>Le fait de faire SET autocommit = 0; n'est valable que pour la session courante. Or, en ouvrant une nouvelle connexion ou une nouvelle fenêtre d’exécution, vous créez une nouvelle session et c'est le mode autocommit par défaut (ici activé) qui est utilisé.</bootnote> 
 
==== 2) Visibilité des changements non commités. ====
 
Exécuter les requêtes suivantes dans une même fenêtre d’exécution : 
<code>SET autocommit=0; 
INSERT INTO genre (nomGenre) VALUES('test3'); 
SELECT * FROM genre ;
</code>  
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.
 
<bootnote question>Allez voir le contenu de la table genre. Comparez les 2 tables genre. 
  - Que constatez-vous ? 
  - Expliquez ce résultat.
</bootnote> 
 
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. 
<bootnote question>
  - Quelle commande aurait-il fallu écrire pour que le genre "test3" soit visible dans la seconde fenêtre
</bootnote> 
==== 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. 

Nous sommes toujours en mode autocommit activé, pour démarrer une transaction, il suffit de lancer la commande suivante : 
<code> START TRANSACTION; </code> 

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 (à exécuter en 1 fois) : ===
<code>START TRANSACTION; 
INSERT INTO genre (nomGenre) VALUES('testTransaction'); 
SELECT * FROM genre; 
ROLLBACK; 
SELECT * FROM genre; 
</code>  

<bootnote question>
  - Comparez les 2 résultats des « SELECT * ». Que constatez-vous ? Expliquez ce résultat. 
  - En utilisant la transaction, comment faire pour que le genre testTransaction soit effectivement inséré dans la table genre ?
</bootnote> 
 
==== 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, voici la **SYNTAXE** : 
<code>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) 
</code> 

__Exemple :__ exécutez les requêtes suivantes en 1 fois. 
<code>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; 
</code> 

<bootnote question>Quels genres ont été ajoutés, pourquoi ?</bootnote> 
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.
==== Exercices d'application ====
=== Objectifs ===
  - Mettre en œuvre les transactions dans des cas simples.
  - Manipuler COMMIT et ROLLBACK.
=== Exercice 1 : gestion de comptes bancaires ===
  - Créer une table compte(id, nom, solde).
  - Insérer quelques comptes.
  - Ecrire les commandes SQL pour effectuer un transfert de 200 € du compte A vers le compte B avec une transaction -> Si une mise à jour échoue, tout doit être annulé.
== Utilisation de SAVEPOINT ==
  - Effectuer des transferts multiples avec un rollback partiel.
== Concurrence ==
  - Simuler deux sessions MariaDB (deux terminaux).
  - Montrer l’effet d’un COMMIT tardif sur les lectures concurrentes.



==== Commandes essentielles ====
<bootnote learn>
  * START TRANSACTION; → démarre une transaction
  * COMMIT; → valide les modifications
  * ROLLBACK; → annule les modifications
  * SAVEPOINT nom; → crée un point intermédiaire
  * ROLLBACK TO nom; → annule seulement jusqu’au point donné
</bootnote>

==== A retenir ====
<bootnote learn>
  * 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é.
</bootnote>