Syntaxe de SAVEPOINT et ROLLBACK TO SAVEPOINT
<<<		 Syntaxe de LOCK TABLES/UNLOCK TABLES Syntaxe de SET TRANSACTION
>>>
14.4 Commandes relatives aux verrous et aux transactions
14 Syntaxe des commandes SQL
 Manuel de Référence MySQL 4.1 : Version Française
. Syntaxes de START TRANSACTION , COMMIT et ROLLBACK
. Commandes qui ne peuvent pas être annulées
. Commandes qui peuvent causer une validation implicite
. Syntaxe de SAVEPOINT et ROLLBACK TO SAVEPOINT
->Syntaxe de LOCK TABLES/UNLOCK TABLES
. Syntaxe de SET TRANSACTION

14.4.5 Syntaxe de LOCK TABLES/UNLOCK TABLES


LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [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. Note : LOCK TABLES n'est pas compatible avec les transactions, et valide automatiquement toute transaction active avant de verrouiller une table.

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 !

Si vos requêtes utilisent un alias pour une table, alors vous devez verrouiller la table avec l'alias. Le verrouillage ne fonctionnera pas si vous verrouillez la table sans spécifier l'alias : 

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
De même, lorsque vous verrouillez une table avec un alias, vous devez utiliser le nom de l'alias dans vos requêtes : 

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;
Les verrous WRITE ont normalement des priorités supérieures aux verrous READ , afin de s'assurer que les modifications sont exécutées 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 de lecture et un verrou d'écriture, il pose le verrou de lecture avant celui d'écriture.
  • 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 connaître certaines choses sur ce schéma :

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 verrouillée en WRITE .La raison pour laquelle les requêtes sont plus rapides avec LOCK TABLES est que MySQL ne rafraîchît 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 rafraîchi après chaque requête SQL). Cela accélè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 nécessite LOCK TABLES pour s'exécuter 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 inséré une nouvelle ligne dans la table trans entre l'exécution 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 dans de nombreuses situations. Transactions et opérations atomiques .

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. Problèmes avec ALTER TABLE . .

<< Syntaxe de LOCK TABLES/UNLOCK TABLES >>
Syntaxe de SAVEPOINT et ROLLBACK TO SAVEPOINT Commandes relatives aux verrous et aux transactions Syntaxe de SET TRANSACTION