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
.
|