5.3 Verrouillage de tables
5 Optimisation de MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Comment MySQL verrouille les tables
->Problème de verrouillage de tables
|
5.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
.
|
