16.11.7 Les verrous posés par différentes requêtes SQL avec InnoDB

Un verrou de lecture, une commande UPDATE ou DELETE pose généralement des verrous sur toutes les lignes qui sont analysée durant l'opération. La présence d'une clause WHERE n'a pas d'importance. InnoDB ne se souviens pas de la condition WHERE exacte, mais sait quel intervalles d'index ont été scannés. Les verrous sont du type 'prochaine clé', et cela empêche aussi les insertions dans l'``espace'' immédiatement après la ligne.

Si vous n'avez d'index valable pour votre requête, et que MySQL scanne toute la table, chaque ligne sera verrouillée, et bloquera ainsi toutes les insertions. Il est important de bien configurer ses index, poru que les requêtes ne fasse pas de scan de table inutiles.

• SELECT ... FROM ... : ceci est une lecture cohérente, qui lit un bilan de la base, et ne pose aucun verrou.
• SELECT ... FROM ... LOCK IN SHARE MODE : pose un verrou partagé sur la prochaine clé sur tous les index que la lecture rencontre.
• SELECT ... FROM ... FOR UPDATE : pose un verrou exclusif sur la prochaine clé sur tous les index que la lecture rencontre.
• INSERT INTO ... VALUES (...) : pose un verrou exclusif sur la ligne insérée. Notez que ce verrou n'est pas un verrou de clé, et il n'empêche pas les autres utilisateurs d'insérer des lignes. Si une erreur de clé double apparaît, un verrou sera posé partagé sera posé sur la ligne doublon.
• Durant l'initialisation d'une colonne AUTO_INCREMENT dans une table, InnoDB pose un verrou exclusif à la fin de l'index associé à la colonne AUTO_INCREMENT . Lors de l'accession au compteur d'incrémentation, InnoDB utilise un verrou spécifique, en mode AUTO-INC où le verrou ne dure que jusqu'à la fin de la requête SQL courante, au lieu de la fin de la transaction. InnoDB et AUTOCOMMIT .

  Avant MySQL 3.23.50, SHOW TABLE STATUS posait aussi un verrou exclusif sur les tables ayant une colonne AUTO_INCREMENT . Cela signifie que la commande SHOW TABLE STATUS pouvait aussi causer un blocage de verrou, ce qui surprenait beaucoup les utilisateurs. Depuis MySQL 3.23.50, InnoDB lit la valeur d'une table dont la colonne AUTO_INCREMENT a été initialisée, sans poser de verrou.

• INSERT INTO T SELECT ... FROM S WHERE ... : pose un verrou exclusif sur chaque ligne inséré dans T . Effectue la recherche sur S sous la forme d'une lecture cohérente, mais pose un verrou partagé sur l'index de prochaine clé de S si MySQL a activé le log. InnoDB doit poser un verrou dans cette dernière situation, car en cas d'exécution des instructions dans une phase de restauration, toutes les requêtes doivent être exécutées dans le même ordre.
• CREATE TABLE ... SELECT ... effectue une commande SELECT sous la forme d'une lecture cohérente, ou avec des verrous partagés, comme précédemment.
• REPLACE est similaire à une insertion, si il n'y a pas de collision sur la clé unique. Sinon, une verrou exclusif sur l'index de prochaine clé est posé sur la ligne qui sera modifiée.
• UPDATE ... SET ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé, à chaque ligne que la recherche trouve.
• DELETE FROM ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé à chaque ligne que la recherche trouve.
• Si la contrainte de FOREIGN KEY est définie sur une table, toute insertion, modification ou effacement qui requiert la vérification de la contrainte va poser un verrou de ligne sur la ligne dont il doit vérifier la contrainte. De plus, dans certains cas où la contrainte échoue, InnoDB pose ces verrous.
• LOCK TABLES ... : pose un verrou de table. L'implémentation de la couche MySQL pose ce verrou. La détection automatique des blocages de InnoDB ne peut détecter les blocages lorsque de tels verrous sont posés. Voyez la section suivante. Détection des blocages et annulation .De plus, comme MySQL ne connaît pas le verrouillage de lignes, il est possible que vous posiez un verrou sur une table où un autre utilisateur a déjà posé un verrou. Mais cela ne pose pas de problème quant à l'intégrité de la requête. Restrictions sur les tables InnoDB .