Dans une session, démarrons une transaction et sélectionnons toutes les adoptions faites par les clients dont l'id dépasse 13,
avec un verrou exclusif.
Session 1 :
Code : SQL
START TRANSACTION;
SELECT * FROM Adoption WHERE client_id > 13 FOR UPDATE; -- ne pas
oublier le FOR UPDATE pour poser le verrou
La requête va poser un verrou exclusif sur toutes les lignes dont client_id vaut 14 ou plus.
client_id animal_id date_reservation date_adoption prix paye
14
58
2012-02-25
2012-02-25
700.00 1
15
30
2008-08-17
2008-08-17
735.00 1
Imaginons maintenant qu'une seconde session démarre une transaction à ce moment-là, insère et commite une ligne dans
Adoption pour le client 15.
Si, par la suite, la première session refait la même requête de sélection avec verrou exclusif, elle va faire apparaître une troisième
ligne de résultat : l'adoption nouvellement insérée (étant donné que pour poser le verrou, la session va aller chercher les données
les plus à jour, prenant en compte le commit de la seconde session).
Cette ligne nouvellement apparue malgré les verrous est une "ligne fantôme".
Pour pallier ce problème, qui est contraire au principe d'isolation, les verrous posés par des requêtes de lecture, de modification
et de suppression sont des verrous dits "de clé suivante" ; ils empêchent l'insertion d'une ligne dans les espaces entre les lignes
verrouillées, ainsi que dans l'espace juste après les lignes verrouillées.
L'espace entre ? L'espace juste après ?
Nous avons vu que les verrous se basent sur les index pour verrouiller uniquement les lignes nécessaires. Voici un petit schéma
qui vous expliquera ce qu'est cet "index de clé suivante".
On peut représenter l'index sur client_id de la table Adoption de la manière suivante (je ne mets que les client_id < 10) :
Représentation de l'index sur
client_id
Si l'on insère une adoption avec 4 pour client_id , l'index va être réorganisé de la manière suivante :
Insertion d'une nouvelle
valeur dans l'index
Mais, si l'on pose un verrou de clé suivante sur l'index, sur les lignes dont client_id vaut 4, on va alors verrouiller les lignes, les
espaces entre les lignes et les espaces juste après. Ceci va bloquer l'insertion de la nouvelle ligne :
Partie 5 : Sécuriser et automatiser ses actions
249/414
www.openclassrooms.com
avec un verrou exclusif.
Session 1 :
Code : SQL
START TRANSACTION;
SELECT * FROM Adoption WHERE client_id > 13 FOR UPDATE; -- ne pas
oublier le FOR UPDATE pour poser le verrou
La requête va poser un verrou exclusif sur toutes les lignes dont client_id vaut 14 ou plus.
client_id animal_id date_reservation date_adoption prix paye
14
58
2012-02-25
2012-02-25
700.00 1
15
30
2008-08-17
2008-08-17
735.00 1
Imaginons maintenant qu'une seconde session démarre une transaction à ce moment-là, insère et commite une ligne dans
Adoption pour le client 15.
Si, par la suite, la première session refait la même requête de sélection avec verrou exclusif, elle va faire apparaître une troisième
ligne de résultat : l'adoption nouvellement insérée (étant donné que pour poser le verrou, la session va aller chercher les données
les plus à jour, prenant en compte le commit de la seconde session).
Cette ligne nouvellement apparue malgré les verrous est une "ligne fantôme".
Pour pallier ce problème, qui est contraire au principe d'isolation, les verrous posés par des requêtes de lecture, de modification
et de suppression sont des verrous dits "de clé suivante" ; ils empêchent l'insertion d'une ligne dans les espaces entre les lignes
verrouillées, ainsi que dans l'espace juste après les lignes verrouillées.
L'espace entre ? L'espace juste après ?
Nous avons vu que les verrous se basent sur les index pour verrouiller uniquement les lignes nécessaires. Voici un petit schéma
qui vous expliquera ce qu'est cet "index de clé suivante".
On peut représenter l'index sur client_id de la table Adoption de la manière suivante (je ne mets que les client_id < 10) :
Représentation de l'index sur
client_id
Si l'on insère une adoption avec 4 pour client_id , l'index va être réorganisé de la manière suivante :
Insertion d'une nouvelle
valeur dans l'index
Mais, si l'on pose un verrou de clé suivante sur l'index, sur les lignes dont client_id vaut 4, on va alors verrouiller les lignes, les
espaces entre les lignes et les espaces juste après. Ceci va bloquer l'insertion de la nouvelle ligne :
Partie 5 : Sécuriser et automatiser ses actions
249/414
www.openclassrooms.com