6.7 Commandes relatives aux verrous et aux transactions
6 Référence du langage MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Syntaxe de BEGIN/COMMIT/ROLLBACK ->Syntaxe de LOCK TABLES/UNLOCK TABLES . Syntaxe de SET TRANSACTION
|
6.7.2 Syntaxe de LOCK TABLES/UNLOCK TABLES
LOCK TABLES nom_de_table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, nom_de_table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...] ... UNLOCK TABLES
|
LOCK TABLES
verrouille une table pour le thread courant.
UNLOCK
TABLES
déverrouillera automatiquement tous les verrous posés par le thread
courant. Toutes les tables verrouillées par le thread courant sont
automatiquement déverrouillées quand ce thread utilise à nouveau
LOCK TABLES
,
ou quand la connexion au serveur est perdue.L'utilisation de
LOCK TABLES
dans MySQL 4.0.2 nécessite le privilège
LOCK TABLES
global et un privilège de
SELECT
sur les tables impliquées.
Dans MySQL 3.23, il faut les privilèges
SELECT
,
INSERT
,
DELETE
et
UPDATE
sur les tables.
Les principales raisons d'utiliser
LOCK TABLES
sont l'émulation de transactions
ou l'accélération des processus de modification de tables. Cela sera détaillé plus loin.
Si un thread obtient un verrouillage
READ
sur une table, ce thread (et tous les autres threads)
peuvent uniquement accéder a cette table en lecture. Si un thread obtient un verrouillage
WRITE
sur une table, alors seul le thread qui a posé le verrou peut lire ou écrire sur cette table.
Tous les autres threads sont bloqués.
La différence entre
READ LOCAL
et
READ
est que
READ LOCAL
autorise des requêtes
INSERT
non-conflictuelles à être exécutées alors que le verrou est posé.
Ceci ne peut cependant pas être utilisé si vous souhaitez modifier les fichiers de la base de données
en dehors de MySQL pendant que le verrou est posé.
Quand vous utilisez
LOCK TABLES
, vous devez verrouiller toutes les tables que vous allez utiliser,
et vous devez utiliser les mêmes alias sur ce que vous utiliserez dans vos requêtes !
Si vous utilisez une table a plusieurs reprises dans une requête (avec des alias), vous devez
verrouiller chacun des alias !
Les verrous
WRITE
ont normalement des priorités supérieures aux verrous
READ
,
afin de s'assurer que les updates sont exécutés au plus vite. Cela signifie que si un
thread demande un verrou
READ
et qu'un autre thread demande un verrou
WRITE
, la demande de
verrou
READ
attendra que le thread
WRITE
ait abouti pour libérer le verrou.
Vous pouvez utiliser le verrou
LOW_PRIORITY WRITE
pour permettre à d'autres threads d'obtenir
des verrous
READ
pendant que le thread attend le verrou
WRITE
. Vous ne devriez utiliser les verrous
LOW_PRIORITY WRITE
que si vous êtes sûr qu'il y aura effectivement un moment où aucun
thread ne posera de verrou
READ
.
LOCK TABLES
fonctionne de la manière suivante :
- Trie toutes les tables à verrouiller dans un ordre défini par MySQL
(l'utilisateur ne définit pas d'ordre).
- Si une table est verrouillée avec un verrou read et un verrou write, il pose le verrou write avant le read.
- Verrouille une table à la fois jusqu'à ce que le thread ait tous ses verrous.
Cette politique garantit le bon verrouillage des tables.
Il faut cependant connaitre certaines choses sur ce schema :
Si vous utilisez un verrou
LOW_PRIORITY WRITE
pour une table, cela signifie seulement que
MySQL attendra, pour poser ce verrou, qu'aucun autre thread ne réclame de verrou
READ
.
Quand le thread aura le verrou
WRITE
et qu'il attendra que les verrous soient posés
sur les autres tables de la liste, tous les autres threads attendront que le verrou
WRITE
soit
libéré. Si cela devient un problème grave pour votre application, il est conseillé de convertir des tables
en tables supportant les transactions.
Vous pouvez terminer un thread attendant un verrouillage de table en toute
sécurité avec
KILL
. Syntaxe de
KILL
.
Il est
déconseillé
de verrouiller des tables utilisées
avec
INSERT DELAYED
, car, dans ce cas, la requête
INSERT
est exécutée dans un autre thread.
Normalement, vous n'avez pas besoin de verrouiller les tables puisque chaque
requête
UPDATE
est atomique : aucun autre thread ne peut interférer avec
une autre requête active. Il existe cependant quelques cas où vous aurez besoin
de verrouiller les tables :
-
Si vous allez exécuter plusieurs requêtes sur plusieurs tables, il est préférable,
d'un point de vue rapidité, de verrouiller les tables dont vous aurez besoin.
L'inconvénient, bien sur, est que les autres threads ne pourront pas intervenir
sur ces tables durant vos opérations, ni en extraire des informations si la table
est en
WRITE
-locked.La raison pour laquelle les requêtes sont plus rapides avec
LOCK TABLES
est que
MySQL ne rafraichit pas l'index des clés des tables verrouillées tant que
UNLOCK TABLES
n'est pas invoqué (normalement, le cache des clés est rafraichi après chaque requête SQL).
Cela accelère les insertions, les modifications et les suppressions de données dans les tables
MyISAM
.
-
Si vous utilisez un type de table dans MySQL qui ne supporte pas les transactions, vous devez utiliser
LOCK TABLES
pour vous assurez qu'aucun autre thread ne s'intercale entre un
SELECT
et un
UPDATE
. L'exemple suivant necessite
LOCK TABLES
pour s'executer en toute sécurité :
mysql> LOCK TABLES trans READ, customer WRITE; mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id; mysql> UPDATE customer SET total_value=sum_from_previous_statement -> WHERE customer_id=some_id; mysql> UNLOCK TABLES;
|
Sans
LOCK TABLES
, Il est possible qu'un autre thread ait inseré une nouvelle ligne dans la table
trans
entre l'execution du
SELECT
et l'exécution de la requête
UPDATE
.
L'utilisation de modifications incrémentales (
UPDATE customer SET
value=value+nouvelle_valeur
) ou de la fonction
LAST_INSERT_ID()
permet de se passer de
LOCK TABLES
en de nombreuses occasions.
Il est aussi possible de résoudre de nombreux cas en utilisant un verrou utilisateur, avec les fonctions
GET_LOCK()
et
RELEASE_LOCK()
. Ces verrous sont stockés dans une table de hashage dans le serveur
et utilisent les fonctions
pthread_mutex_lock()
et
pthread_mutex_unlock()
pour plus de vitesse.
Fonctions diverses .
Voir Comment MySQL verrouille les tables pour plus de détails.
Il est possible de verrouiller tous les tables de toutes les bases avec la commande
FLUSH TABLES WITH READ LOCK
.
Syntaxe de
FLUSH
.
C'est une méthode très pratique pour effectuer des sauvegardes si vous utilisez un
système de fichiers qui, comme Veritas, permet de créer des instantanés.
NOTE
:
LOCK TABLES
ne fonctionne pas avec les transactions
et validera automatiquement toutes les transactions actives avant de poser
verrouiller la table.
|