Skip navigation links
MySQL 5.0 Reference Manual :: 7 Optimisation de MySQL :: 7.3 Verrouillage de tables :: 7.3.2 Problème de verrouillage de tables
7.4 Optimiser la structure de la base de données »
Section Navigation      [Toggle]

7.3.2. Problème de verrouillage de tables

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 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.

Pour les grandes tables, le verrouillage de table est meilleur que le verrouillage de lignes, pour la plupart des applications, mais il recèle quelque pièges.

Le verrouillage de tables 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.

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. See Section 13.5.2.8, « Syntaxe de SET ».

  • Vous pouvez spécifier qu'une requête particulière SELECT est très importante, en utilisant l'attribut HIGH_PRIORITY. See Section 13.1.7, « Syntaxe de SELECT ».

  • Si vous avez des problèmes avec des INSERT combinés avec des SELECT, utilisez les tables MyISAM car elle supportent les commandes SELECTs 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. See Section 13.1.4, « Syntaxe de INSERT ».

  • Si vous avez des problèmes avec des combinaisons de SELECT et DELETE, l'option LIMIT de DELETE peut aider. See Section 13.1.1, « Syntaxe de DELETE ».

  • Utiliser SQL_BUFFER_RESULT avec les commandes SELECT peut aider à réduire la durée des verrous. See Section 13.1.7, « Syntaxe de SELECT ».

  • Vous pouvez changer le code de verrouillage dans le fichier mysys/thr_lock.c pour n'utiliser qu'une queue unique. Dans ce cas, les lectures et écritures auront la même priorité, ce qui peut aider certaines applications.

Voici quelques conseils avec le système de verrouillage de MySQL :

  • Les accès concurents ne sont pas un problème si vous ne mélangez pas les sélections et les modifications de nombreuses lignes dans la même table.

  • Vous pouvez utiliser LOCK TABLES pour accélérer les opérations : de nombreuses modifications dans un même verrou seront plus rapides. Répartir le contenu de la table en plusieurs tables peut aussi aider.

  • Si vous rencontrez des problèmes de vitesse avec les verrous de tables, vous devez être capables d'améliorer les performances en convertissant certaines tables en InnoDB ou BDB. See Chapitre 15, Le moteur de tables InnoDB. See Section 14.4, « Tables BDB ou BerkeleyDB ».

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.