InnoDB et AUTOCOMMIT
<<<		 InnoDB et SET ... TRANSACTION ISOLATION LEVEL ... Lecture cohérente non-bloquante
>>>
16.11 Modèle de transactions et verrouillage InnoDB
16 Tables InnoDB
 Manuel de Référence MySQL 4.1 : Version Française
. InnoDB et AUTOCOMMIT
->InnoDB et SET ... TRANSACTION ISOLATION LEVEL ...
. Lecture cohérente non-bloquante
. Verrous de lecture SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE
. Verrou de clé suivante : éviter le problème des lignes fantômes
. Un exemple de lecture cohérente avec InnoDB
. Les verrous posés par différentes requêtes SQL avec InnoDB
. Quand est-ce que MySQL valide ou annule implicitement une transaction?
. Détection des blocages et annulation
. Comment gérer les blocages de verrous?

16.11.2 InnoDB et SET ... TRANSACTION ISOLATION LEVEL ...

En terme de niveau d'isolation des transactions SQL-92, le comportement par défaut de InnoDB est REPEATABLE READ . Depuis la version 4.0.5, InnoDB offre 4 niveaux différents d'isolation de transactions, tels que décrit dans la norme SQL-92. Vous pouvez configurer le niveau d'isolation par défaut dans le groupe [mysqld] du fichier my.cnf : 


transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}
Un utilisateur peut changer le niveau d'isolation d'une session ou des nouvelles connexion avec la commande SET TRANSACTION . La syntaxe est la suivante : 

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}
Notez qu'il n'y a pas de tiret dans les noms de niveaux de la syntaxe SQL.Le comportement par défaut est de configurer le niveau d'isolation de la prochaine transaction non démarrée. Si vous utilisez le mot clé GLOBAL , la commande configure le niveau d'isolation globalement, pour toutes les nouvelles connexions, mais pas pour les connexions existantes. Vous devez avoir les droits de SUPER pour faire cela. En utilisant le mot clé SESSION , vous allez configurer le niveau d'isolation des prochaines transactions de la session courante. Tous les clients sont autorisés à changer le niveau d'isolation de la session, même au milieu d'une transaction, ainsi que le niveau d'isolation de la prochaine transaction. Dans les versions 3.23.50 et plus anciennes, SET TRANSACTION n'avait pas d'effet sur les tables InnoDB . Dans les versions < 4.0.5, seules REPEATABLE READ et SERIALIZABLE étaient disponibles.

Vous pouvez connaître les niveaux d'isolation de transaction de session et global avec ces commandes : 


SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
Pour le verrouillage de ligne, InnoDB utilise le système dit de verrouillage de la prochaine clé. Cela signifie qu'en plus des lignes d'index, InnoDB peut aussi verrouiller le ``trou'' après une ligne d'index pour éviter les insertions des autres utilisateurs. Un verrou de prochaine clé représente un verrou qui bloque la ligne d'index et le trou qui le précède. Un verrou de trou représente un verrou qui ne bloque que le trou avant la ligne.

Voici une description détaillée de chaque niveau d'isolation de InnoDB :

  • READ UNCOMMITTED This is also called ``dirty read'' : Les commandes SELECT non-verrouillantes sont effectuées sans rechercher de versions plus récente de la ligne. Elles sont donc non-cohérentes, sous ce niveau d'isolation. SInon, ce niveau fonctionne comme le niveau READ COMMITTED .
  • READ COMMITTED Proche du niveau d'isolation d'Oracle. Toutes les commandes SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE ne verrouille que les lignes d'index, et non pas les trous entre elles, ce qui permet l'insertion de nouvelles lignes, adjacentes aux lignes verrouillées. UPDATE et DELETE qui utilisent un seul index avec une condition de recherche unique ne verrouille que la ligne d'index trouvée, par le trou qui la précède. Mais, pour un intervalle de lignes traitées par UPDATE et DELETE , InnoDB doit méler des verrous de prochaine clé ou des verrous de trous et bloquer les insertions des autres utilisateurs dans les espaces couvert par l'intervalle. Ceci est nécessaire car les ``lignes fantômes'' doivent être bloquées pour la réplication MySQL et la restauration. La lecture cohérente se comporte comme avec Oracle : chaque lecture cohérente, même dans une transaction, lit et modifie son propre contexte. Lecture cohérente .
  • REPEATABLE READ C'est le niveau d'isolation par défaut d' InnoDB . Les commandes SELECT ... FOR UPDATE , SELECT ... LOCK IN SHARE MODE , UPDATE et DELETE qui utilisent un index unique dans une condition de recherche, ne verrouille que la ligne d'index trouvée, et non pas le trou précédent. SInon, ces opérations utilisent le verrouillage de prochaine clé, en verrouillant l'intervalle utilisé, et bloque les insertions des utilisateurs. Avec les lectures cohérentes il y a une importante différence avec le niveau d'isolation précédent : dans ce mode, toutes les lectures cohérentes d'une même transaction liront les mêmes données, établies par la première lecture. Cette convention signifie que si vous émettez plusieurs commandes SELECT dans la même transaction, ces SELECT seront cohérents les uns avec les autres. Lecture cohérente .
  • SERIALIZABLE Ce niveau est identique au précédent, mais toutes les lectures SELECT sont implicitement converties en SELECT ... LOCK IN SHARE MODE .

<< InnoDB et SET ... TRANSACTION ISOLATION LEVEL ... >>
InnoDB et AUTOCOMMIT Modèle de transactions et verrouillage InnoDB Lecture cohérente non-bloquante