7.5.8 Modèle transactionnel de InnoDB

Le modèle transactionnel d'InnoDB a pour but de combiner les avantages des bases de données multi-version aux verrouillages traditionnels en deux phases. InnoDB fait un verrouillage de ligne, et exécute les requêtes par défaut avec des lectures cohérentes non bloquante, de la même façon qu'Oracle. Les verrous InnoDB sont stockés de manière efficace, pour que l'escalade de transaction ne soit pas nécessaire : typiquement, plusieurs utilisateurs sont autorisés à verrouiller toutes les lignes dans une base, ou un sous ensemble aléatoire de ligne, sans que InnoDB ne soit à court de mémoire.

Avec InnoDB, toutes les activités des utilisateurs sont placées dans des transactions. Si le mode de validation automatique de MySQL est utilisé, chaque requête SQL représente une transaction. MySQL démarre toujours une nouvelle connexion avec de mode activé.

Si la connexion est en mode AUTOCOMMIT = 1 , alors l'utilisateur peut réaliser une transaction à requêtes multiples en commençant par la commande BEGIN et en terminant la transaction par COMMIT ou ROLLBACK .

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Vous pouvez connaître les valeurs de niveaux d'isolations global et courant avec la commande :

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Voici une description plus détaillée des différents niveaux d'isolation des transactions de InnoDB :

• READ UNCOMMITTED , qui est aussi appelé la 'lecture incohérente' : les commandes SELECT non bloquantes sont exécutées sans vérifier la présence de versions antérieures de la ligne : par conséquent, ces lectures ne sont pas cohérentes avec ce niveau d'isolation. Sinon, ce niveau fonctionne exactement comme READ COMMITTED .
• READ COMMITTED Le niveau d'isolation qui se rapproche de celui 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 espaces qui les précèdent, et de cette manière, ils autorisent les insertions entre deux lignes verrouillées. Les commandes UPDATE et DELETE qui utilisent un index unique avec une condition de recherche, ne vont verrouiller que les index trouvés, et non pas les espaces précédent ces verrous. Mais avec les commandes UPDATE et DELETE en mode intervalle, InnoDB va poser des verrous de clé suivante, ou verrous d'espacement, et bloquer les insertions d'autres utilisateurs dans ces espaces. Ceci est nécessaire pour éviter la présence de lignes 'fantômes', qui sont néfastes pour la réplication. Les lectures cohérentes fonctionnent comme sous Oracle : chaque lecture cohérente, même durant une transaction, fixe et lit une version récente de données.
• REPEATABLE READ est le niveau d'isolation par défaut d'InnoDB. SELECT ... FOR UPDATE , SELECT ... LOCK IN SHARE MODE , UPDATE , et DELETE qui utilisent un index unique comme condition de recherche, ne vont verrouiller que la ligne d'index trouvée, et non pas l'espace qui la prépare. Sinon, ces opérations vont employer le verrouillage de clés suivante, pour bloquer les insertions parasites. En lectures cohérentes il y a une différence importante avec le niveau d'isolation précédent : avec ce niveau, toutes les lectures faites dans une même transaction sont faîtes avec la version établit lors de la première lecture. Cette convention signifie que si vous faites plusieurs lectures SELECT dans une même transaction, ces SELECT seront cohérents entre eux.
• SERIALIZABLE Ce niveau ressemble au précédent, mais toutes les lectures SELECT s sont implicitement converties en SELECT ... LOCK IN SHARE MODE .

Sommaire :

• Lecture cohérente
• Verrous de lecture
• Verrou de clé suivante : éviter le problème des lignes fantômes
• Les verrous posés par différentes requêtes SQL avec InnoDB
• Détection des blocages et annulation
• Un exemple de lecture cohérente avec InnoDB
• Comment gérer les blocages de verrous?
• Conseils sur l'amélioration des performances InnoDB
• Le moniteur InnoDB