FAQ MySQLConsultez toutes les FAQ

Une transaction permet de rendre atomique un processus constitué de plusieurs requêtes, c'est-à-dire qu'il est exécuté intégralement ou pas du tout, mais jamais partiellement.
L'exemple classique est un virement bancaire : si une panne système intervient entre le moment où le débiteur est débité et celui où le créditeur est crédité, c'est un problème majeur !
Une transaction permet de s'assurer que les deux opérations sont bien effectuées, ou alors qu'aucune des deux ne soit prise en compte.

Oui, ce support est assuré par les moteurs de stockage BDB et InnoDB (ce dernier étant compatible ACID), ainsi que le moteur Maria. Avec les tables MyISAM, un système partiellement comparable peut être mis en place avec des verrous manuels (LOCK TABLE).

Les tables d'un moteur transactionnel comme InnoDB sont capables d'accepter plusieurs versions d'une même ligne, l'une officielle et les autres en tant que brouillons liés à une session.
L'utilisateur travaille ainsi sur son brouillon, jusqu'au moment où il décide de le valider (COMMIT) ou de l'annuler (ROLLBACK). S'il valide, son brouillon devient alors la version officielle. Si au contraire sa session est interrompue avant la validation (par exemple en raison d'une panne de courant), son brouillon non validé sera automatiquement annulé.

En mode autocommit, chaque requête est considérée comme une transaction à elle seule, et elle est donc automatiquement validée en fin d'exécution. Voici les commandes permettant de manipuler ce mode :

1
2
3
4
5
6
7
8
9
10
-- pour savoir si vous êtes (1) en mode autocommit ou non (0) : 
SELECT @@autocommit ; 
  
-- pour enlever l'autocommit (deux possibilités) : 
SET @@autocommit = 0 ;   
SET @@autocommit = off ;    
  
-- pour le remettre : 
SET @@autocommit = 1 ;   
SET @@autocommit = ON ;

Si vous êtes en mode autocommit, il faut utiliser l'ordre START TRANSACTION. L'autocommit sera alors ingoré jusqu'à la fin de la transaction. Si vous n'êtes pas en mode autocommit, la première requête passée ouvre une transaction. Vous pouvez également utiliser START TRANSACTION... qui présente toutefois l'inconvénient de faire un COMMIT implicite de toute transaction précédemment ouverte.

Outre évidemment les ordres explicites COMMIT et ROLLBACK, une transaction peut se terminer implicitement.

Les actions et événements suivants provoquent un COMMIT implicite :

• un ordre DDL (CREATE, ALTER, DROP ou TRUNCATE), à l'exception de CREATE TEMPORARY TABLE ;
• la mise en route du mode autocommit ;
• l'instruction START TRANSACTION ou son synonyme BEGIN WORK (MySQL n'imbrique donc pas les transactions) ;
• les ordres LOCK et UNLOCK
  

Non, la requête n'est simplement pas exécutée (pour une requête modifiant plusieurs lignes de données, il suffit d'une erreur sur une seule ligne pour que la requête entière échoue - mais pas le reste de la transaction). La transaction continue ensuite jusqu'au prochain COMMIT ou ROLLBACK.

Il est souvent préférable de gérer ces erreurs dans le cadre de procédures stockées, avec des HANDLER indiquant s'il convient de poursuivre, de refaire un essai, de tout annuler, etc.

En paramétrage standard, seule la session qui a ouvert la transaction voit les modifications non validées - en d'autres termes, la "lecture sale" (dirty read) n'est pas possible.

Toutefois, il est possible de modifier le niveau d'isolation des transactions jusqu'à autoriser la "lecture sale".

MySQL met en place les quatre niveaux prévus par la norme SQL ; du moins isolé au plus isolé :

• 
  niveau 0 : READ-UNCOMMITTED ; autorise la lecture sale
• 
  niveau 1 : READ-COMMITTED ; interdit la lecture sale
• 
  niveau 2 : REPEATABLE-READ (niveau par défaut) ; idem, et rend la session aveugle aux modifications (mêmes validées) faites par les autres sessions ("lecture cohérente" ou consistent read)
• 
  niveau 3 : SERIALIZABLE : idem, et fait comme si les transactions étaient validées les unes après les autres et non en même temps
  
  

SELECT @@tx_isolation ;

1
2
3
4
5
SET tx_isolation = 'READ-COMMITTED' ; -- avec tiret et apostrophes 
SET tx_isolation = 1 ; 
SET @@tx_isolation = 1 ; 
SET GLOBAL tx_isolation = 'READ-COMMITTED' ; 
SET GLOBAL tx_isolation = 1 ;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ; -- sans tirets ni apostrophes

Il est possible que deux transactions se bloquent mutuellement, chacune attendant des données verrouillées par l'autre. C'est ce qu'on appelle un interblocage (deadlock).

MySQL détecte automatiquement les interblocages, et annule (ROLLBACK) l'une des deux transactions (celle qui a modifié le moins de lignes).

Pour plus de détails, voir la doc officielle.

Le format par défaut des tables créées sous EasyPHP ne supporte pas les transactions (MyISAM). Il faut utiliser un format comme InnoDb qui n'est pas activé par défaut sous MySQL 3.23.xx, par exemple le serveur MySQL fourni avec EasyPHP 1.6 et en général pour les versions inférieures à la 4.1.

Avant toute chose, il convient de connaître les moteurs de stockage supportés par votre serveur. Utilisez la commande

SHOW STATUS