Comprendre le verrouillage

  • 2 minutes

Pour empêcher les utilisateurs d’obtenir des données incorrectes retournées par uneSELECT instruction, MySQL verrouille les objets de base de données. InnoDB utilise le verrouillage des métadonnées pour gérer l’accès simultané et la cohérence des données. Le verrouillage des métadonnées affecte les commandes suivantes :

  • CREATE ou DELETE INDEX
  • MODIFY TABLE
  • DROP TABLE
  • Opérations de maintenance de table, telles que OPTIMIZE TABLE et TABLE REPAIR.
  • LOCK TABLE <mytable> WRITE

Le verrouillage des métadonnées empêche les instructions de langage de définition de données (DDL) d’agir sur les objets affectés pendant qu’une transaction est en cours.

Notes

Les instructions DDL définissent les objets de base de données. Il s’agit de commandes telles que CREATE, ALTER, DROP, etc.

Pour que le verrouillage des métadonnées fonctionne correctement sans impacter les performances, MySQL devrait avoir la validation automatique définie sur ON. Cela empêche les validations en attente et les objets verrouillés empêchant d’autres utilisateurs d’y accéder. La validation automatiquedéfinie sur ON par défaut. Si possible, effectuez des opérations DLL lorsque peu ou pas d’utilisateurs sont connectés.

InnoDB utilise également des verrous de ligne partagés et exclusifs. Un verrou partagé autorise les lectures, et les verrous exclusifs sont exclusifs à une transaction, arrêtant ainsi les autres transactions qui modifient la ligne. InnoDB verrouillera également les tables si nécessaire.

Les verrous d’intervalle sont utilisés pour verrouiller les index et garantir des résultats cohérents à partir d’instructions SELECT.

Un interblocage se produit lorsque deux transactions s’attendent mutuellement pour libérer un verrou. InnoDB résout ce problème avec le paramètre innodb_lock_wait_timeout. La requête attend le temps spécifié, puis abandonne.

Le tableau suivant décrit les paramètres de serveur relatifs aux verrous. Ces paramètres peuvent également être définis au niveau de la session.

Paramètre de serveur Description Default
innodb_lock_wait_timeout Temps en secondes pendant lequel une transaction attend un verrou de ligne. 50 secondes
lock_wait_timeout Temps en secondes pour acquérir des verrous de métadonnées.