les commandes LOCK TABLES et UNLOCK TABLES provoquent une validation implicite si elles sont exécutées à
l'intérieur d'une transaction.
Pour utiliser à la fois les transactions et les verrous de table, il faut renoncer à démarrer explicitement les transactions, et donc
utiliser le mode non-autocommit. Lorsque l'on est dans ce mode, il est facile de contourner la validation implicite provoquée par
LOCK TABLES et UNLOCK TABLES : il suffit d'appeler LOCK TABLES avant toute modification de données, et de
commiter/annuler les modifications avant d'exécuter UNLOCK TABLES.
Exemple :
Code : SQL
SET autocommit = 0;
LOCK TABLES Adoption WRITE; -- La validation implicite ne commite
rien puisque aucun changement n'a été fait
UPDATE Adoption SET date_adoption = NOW() WHERE client_id = 9 AND
animal_id = 54;
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE
client_id = 9;
ROLLBACK;
UNLOCK TABLES; -- On a annulé les changements juste avant donc la
validation implicite n'a aucune conséquence
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE
client_id = 9;
SET autocommit = 1;
Syntaxe et utilisation : verrous de ligne
Ces verrous ne peuvent pas être posés sur une table utilisant le moteur MyISAM ! Tout ce qui est dit ici concerne les
tables InnoDB uniquement.
Comme les verrous de table, les verrous de ligne peuvent être de deux types :
Les verrous partagés : permettent aux autres sessions de lire les données, mais pas de les modifier (équivalents aux
verrous de table de lecture) ;
Les verrous exclusifs : ne permettent ni la lecture ni la modification des données (équivalents aux verrous d'écriture).
Requêtes de modification, insertion et suppression
Les requêtes de modification et suppression des données posent automatiquement un verrou exclusif sur les lignes
concernées, à savoir les lignes sélectionnées par la clause WHERE, ou toutes les lignes s'il n'y a pas de clause WHERE
(ou s'il n'y a pas d'index, sur les colonnes utilisées comme nous verrons plus loin).
Les requêtes d'insertion quant à elles posent un verrou exclusif sur la ligne insérée .
Requêtes de sélection
Les requêtes de sélection, par défaut, ne posent pas de verrous. Il faut donc en poser explicitement au besoin.
Verrou partagé
Pour poser un verrou partagé, on utilise LOCK IN SHARE MODE à la fin de la requête SELECT.
Code : SQL
SELECT * FROM Animal WHERE espece_id = 5 LOCK IN SHARE MODE;
Cette requête pose donc un verrou partagé sur les lignes de la table Animal pour lesquelles espece_id vaut 5.
Partie 5 : Sécuriser et automatiser ses actions
242/414
www.openclassrooms.com
l'intérieur d'une transaction.
Pour utiliser à la fois les transactions et les verrous de table, il faut renoncer à démarrer explicitement les transactions, et donc
utiliser le mode non-autocommit. Lorsque l'on est dans ce mode, il est facile de contourner la validation implicite provoquée par
LOCK TABLES et UNLOCK TABLES : il suffit d'appeler LOCK TABLES avant toute modification de données, et de
commiter/annuler les modifications avant d'exécuter UNLOCK TABLES.
Exemple :
Code : SQL
SET autocommit = 0;
LOCK TABLES Adoption WRITE; -- La validation implicite ne commite
rien puisque aucun changement n'a été fait
UPDATE Adoption SET date_adoption = NOW() WHERE client_id = 9 AND
animal_id = 54;
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE
client_id = 9;
ROLLBACK;
UNLOCK TABLES; -- On a annulé les changements juste avant donc la
validation implicite n'a aucune conséquence
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE
client_id = 9;
SET autocommit = 1;
Syntaxe et utilisation : verrous de ligne
Ces verrous ne peuvent pas être posés sur une table utilisant le moteur MyISAM ! Tout ce qui est dit ici concerne les
tables InnoDB uniquement.
Comme les verrous de table, les verrous de ligne peuvent être de deux types :
Les verrous partagés : permettent aux autres sessions de lire les données, mais pas de les modifier (équivalents aux
verrous de table de lecture) ;
Les verrous exclusifs : ne permettent ni la lecture ni la modification des données (équivalents aux verrous d'écriture).
Requêtes de modification, insertion et suppression
Les requêtes de modification et suppression des données posent automatiquement un verrou exclusif sur les lignes
concernées, à savoir les lignes sélectionnées par la clause WHERE, ou toutes les lignes s'il n'y a pas de clause WHERE
(ou s'il n'y a pas d'index, sur les colonnes utilisées comme nous verrons plus loin).
Les requêtes d'insertion quant à elles posent un verrou exclusif sur la ligne insérée .
Requêtes de sélection
Les requêtes de sélection, par défaut, ne posent pas de verrous. Il faut donc en poser explicitement au besoin.
Verrou partagé
Pour poser un verrou partagé, on utilise LOCK IN SHARE MODE à la fin de la requête SELECT.
Code : SQL
SELECT * FROM Animal WHERE espece_id = 5 LOCK IN SHARE MODE;
Cette requête pose donc un verrou partagé sur les lignes de la table Animal pour lesquelles espece_id vaut 5.
Partie 5 : Sécuriser et automatiser ses actions
242/414
www.openclassrooms.com