Une partie des colonnes du résultat montré ici a été retirée pour des raisons de clarté.
Nous avons donc des index sur les colonnes suivantes : id , nom, mere_id , pere_id , espece_id et race_id .
Mais aucun index sur la colonne date_naissance.
Il semblerait donc que lorsque l'on pose un verrou, avec dans la clause WHERE de la requête une colonne indexée (espece_id ), le
verrou est bien posé uniquement sur les lignes pour lesquelles espece_id vaut la valeur recherchée.
Par contre, si dans la clause WHERE on utilise une colonne non-indexée (date_naissance), MySQL n'est pas capable de
déterminer quelles lignes doivent être bloquées, donc on se retrouve avec toutes les lignes bloquées.
Pourquoi faut-il un index pour pouvoir poser un verrou efficacement ?
C'est très simple ! Vous savez que lorsqu'une colonne est indexée (que ce soit un index simple, unique, ou une clé primaire ou
étrangère), MySQL stocke les valeurs de cette colonne en les triant. Du coup, lors d'une recherche sur l'index, pas besoin de
parcourir toutes les lignes, il peut utiliser des algorithmes de recherche performants et trouver facilement les lignes concernées.
S'il n'y a pas d'index par contre, toutes les lignes doivent être parcourues chaque fois que la recherche est faite, et il n'y a donc
pas moyen de verrouiller simplement une partie de l'index (donc une partie des lignes). Dans ce cas, MySQL verrouille toutes les
lignes.
Cela fait une bonne raison de plus de mettre des index sur les colonnes qui servent fréquemment dans vos clauses WHERE !
Encore une petite expérience pour illustrer le rôle des index dans le verrouillage de lignes :
Session 1 :
Code : SQL
START TRANSACTION;
UPDATE Animal -- Modification de tous les rats
SET commentaires = CONCAT_WS(' ', 'Très intelligent.', commentaires)
WHERE espece_id = 5;
Session 2 :
Code : SQL
START TRANSACTION;
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 34; -- Modification de l'animal 34 (un chat)
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 72; -- Modification de l'animal 72 (un rat)
La session 1 se sert de l'index sur espece_id pour verrouiller les lignes contenant des rats bruns. Pendant ce temps, la session 2
veut modifier deux animaux : un chat et un rat, en se basant sur leur id .
La modification du chat se fait sans problème, par contre, la modification du rat est bloquée, tant que la transaction de la session
1 est ouverte.
Faites un rollback des deux transactions.
On peut conclure de cette expérience que, bien que MySQL utilise les index pour verrouiller les lignes, il n'est pas nécessaire
d'utiliser le même index pour avoir des accès concurrents.
Lignes fantômes et index de clé suivante
Qu'est-ce qu'une ligne fantôme ?
Partie 5 : Sécuriser et automatiser ses actions
248/414
www.openclassrooms.com
Nous avons donc des index sur les colonnes suivantes : id , nom, mere_id , pere_id , espece_id et race_id .
Mais aucun index sur la colonne date_naissance.
Il semblerait donc que lorsque l'on pose un verrou, avec dans la clause WHERE de la requête une colonne indexée (espece_id ), le
verrou est bien posé uniquement sur les lignes pour lesquelles espece_id vaut la valeur recherchée.
Par contre, si dans la clause WHERE on utilise une colonne non-indexée (date_naissance), MySQL n'est pas capable de
déterminer quelles lignes doivent être bloquées, donc on se retrouve avec toutes les lignes bloquées.
Pourquoi faut-il un index pour pouvoir poser un verrou efficacement ?
C'est très simple ! Vous savez que lorsqu'une colonne est indexée (que ce soit un index simple, unique, ou une clé primaire ou
étrangère), MySQL stocke les valeurs de cette colonne en les triant. Du coup, lors d'une recherche sur l'index, pas besoin de
parcourir toutes les lignes, il peut utiliser des algorithmes de recherche performants et trouver facilement les lignes concernées.
S'il n'y a pas d'index par contre, toutes les lignes doivent être parcourues chaque fois que la recherche est faite, et il n'y a donc
pas moyen de verrouiller simplement une partie de l'index (donc une partie des lignes). Dans ce cas, MySQL verrouille toutes les
lignes.
Cela fait une bonne raison de plus de mettre des index sur les colonnes qui servent fréquemment dans vos clauses WHERE !
Encore une petite expérience pour illustrer le rôle des index dans le verrouillage de lignes :
Session 1 :
Code : SQL
START TRANSACTION;
UPDATE Animal -- Modification de tous les rats
SET commentaires = CONCAT_WS(' ', 'Très intelligent.', commentaires)
WHERE espece_id = 5;
Session 2 :
Code : SQL
START TRANSACTION;
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 34; -- Modification de l'animal 34 (un chat)
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 72; -- Modification de l'animal 72 (un rat)
La session 1 se sert de l'index sur espece_id pour verrouiller les lignes contenant des rats bruns. Pendant ce temps, la session 2
veut modifier deux animaux : un chat et un rat, en se basant sur leur id .
La modification du chat se fait sans problème, par contre, la modification du rat est bloquée, tant que la transaction de la session
1 est ouverte.
Faites un rollback des deux transactions.
On peut conclure de cette expérience que, bien que MySQL utilise les index pour verrouiller les lignes, il n'est pas nécessaire
d'utiliser le même index pour avoir des accès concurrents.
Lignes fantômes et index de clé suivante
Qu'est-ce qu'une ligne fantôme ?
Partie 5 : Sécuriser et automatiser ses actions
248/414
www.openclassrooms.com