Méthodes de verrouillage
<<<		 Problème de verrouillage de tables Optimisation de MySQL
>>>
7.3 Verrouillage de tables
7 Optimisation de MySQL
 Manuel de Référence MySQL 4.1 : Version Française
. Méthodes de verrouillage
->Problème de verrouillage de tables

7.3.2 Problème de verrouillage de tables

Le système de verrou de MySQL est exempt de blocage.

MySQL utilise le verrouillage de table (au lieu du verrouillage de ligne ou de colonne) sur tous les types de tables, sauf InnoDB et BDB , pour obtenir un système de verrou à très haute vitesse. Pour les grandes tables, le verrouillage de table est bien plus rapide que le verrouillage de page, mais il y a aussi des inconvénients.

Pour les tables InnoDB et BDB , MySQL n'utilise le verrouillage de table que vous le demandez explicitement avec LOCK TABLES . Pour ces tables, nous vous recommandons de ne jamais utiliser la commande LOCK TABLES , car InnoDB utilise un verrouillage de ligne automatique, et BDB utilise un verrouillage de pages, pour assurer l'isolation des transactions.

Depuis MySQL version 3.23.7, vous pouvez insérer des lignes dans les tables MyISAM , en même temps que d'autres threads y lisent. Notez que, actuellement, cela ne fonctionne que si il n'y a pas de trous après les lignes effacées dans la table, au moment de l'insertion. Lorsque tous les trous ont été bouchés avec de nouvelles données, les insertions simultanées seront automatiquement réactivées.

Le verrouillage de table permet à de nombreux threads de lire dans la même table, mais si un thread désire écrire dans la table, il doit obtenir un verrou en écriture pour avoir un accès exclusif. Durant la modification, les autres threads qui voudront lire dans cette table, devront attendre.

Comme les modifications de tables sont considérées comme plus importantes que les lectures avec SELECT , toutes les commandes qui modifient la table ont priorités sur les lectures. Cela devrait vous assurer que les modifications ne sont pas retenues trop longtemps, à cause de nombreuses lectures sur une même table. Vous pouvez toutefois modifier cela avec l'option LOW_PRIORITY des commandes de modification, et l'option HIGH_PRIORITY de SELECT ).

Depuis MySQL version 3.23.7, vous pouvez utiliser la variable max_write_lock_count pour forcer MySQL à laisser temporairement la place à toutes les commandes SELECT , après un certain nombre de modifications dans la table.

Le verrouillage de table est une mauvaise technique dans les situations suivantes :
  • Un client exécute une commande SELECT qui prend très longtemps.
  • Un autre client exécute une commande UPDATE sur la table. Ce client va devoir attendre que la commande SELECT soit finie.
  • Un autre client exécute une autre commande SELECT sur la même table. Comme UPDATE a la priorité sur SELECT , cette commande SELECT va attendre que UPDATE soit finit. Il va donc attendre que le premier SELECT soit fini.
  • Un thread attend des erreurs comme full disk , auquel cas tous les threads qui veulent accéder à la table seront en attente de plus d'espace sur le disque.
Des solutions aux problèmes sont :
  • Essayez d'accélérer au maximum les commandes SELECT . Vous pourriez passer par une table de sommaire pour cela.
  • Démarrez mysqld avec l'option --low-priority-updates . Cela va donner aux commandes de modification une priorité plus faible que SELECT . Dans ce cas, c'est la commande SELECT du précédent scénario qui s'exécutera avant la commande INSERT .
  • Vous pouvez donner à une commande spécifique INSERT , UPDATE ou DELETE , une priorité plus basse avec l'attribut LOW_PRIORITY .
  • Démarrez mysqld avec une valeur faible pour max_write_lock_count afin de donner plus souvent la chance aux verrous READ la possibilité de lire des données, entre deux verrous WRITE .
  • Vous pouvez spécifier que toutes les modifications d'un thread spécifique doivent être faites avec un priorité basse, en utilisant la commande SQL : SET LOW_PRIORITY_UPDATES=1 . Syntaxe de SET .
  • Vous pouvez spécifier qu'une requête particulière SELECT est très importante, en utilisant l'attribut HIGH_PRIORITY . Syntaxe des SELECT .
  • Si vous avez des problèmes avec des INSERT combinés avec des SELECT , utilisez les tables MyISAM car elle supportent les commandes SELECT s et INSERT simultanées.
  • Si vous voulez mélanger les commandes INSERT et SELECT , utilisez l'attribut DELAYED de la commande INSERT pour résoudre ce problème. Syntaxe des INSERT .
  • Si vous avez des problèmes avec SELECT et DELETE , l'option LIMIT de DELETE peut aider. Syntaxe des DELETE .

<< Problème de verrouillage de tables >>
Méthodes de verrouillage Verrouillage de tables Optimisation de MySQL