Actuellement, MySQL ne supporte que le verrouillage de table pour les tables ISAM/MyISAM et MEMORY (HEAP), le verrouillage de page pour les tables BDB et le verrouillage de ligne pour InnoDB.

Dans de nombreux cas, vous pouvez faire prévoir le type de verrouillage qui sera le plus efficace pour une application, mais il est très difficile de savoir si un type de verrou est meilleur que l'autre. Tout dépend de l'application, et des différentes composants qui utilisent les verrous.

Pour décider si vous voulez utiliser un type de table avec verrouillage de ligne, vous devez commencer par étudier ce que votre application fait, et quel est le schéma d'utilisation des sélections et modifications. Par exemple, la plupart des applications Web font de nombreuses sélections, peu d'effacements, des modifications basées sur des clés, et des insertions dans des tables spécifiques. Le moteur de base MySQL MyISAM est très bien optimisé pour cette application.

Toutes les méthodes de verrouillage de MySQL sont exemptes de blocage, sauf pour les tables InnoDB et BDB. Ceci fonctionne en demandant tous les verrous d'un seul coup, au début de la requête, et en verrouillant les tables toujours dans le même ordre.

Les tables InnoDB obtiennent automatiquement leur verrou de ligne et les tables BDB leur verrou de page, durant le traitement de la requête SQL, et non pas au démarrage de la transaction.

La méthode de verrouillage des tables de MySQL en écriture (WRITE) fonctionne comme ceci :

La méthode de verrouillage des tables de MySQL en lecture (READ) fonctionne comme ceci :

Lorsqu'un verrou est libéré, le verrou est donné aux threads de la queue de verrou en écriture, puis à ceux de la queue de verrou en lecture.

Cela signifie que si vous avez de nombreuses modifications dans une table, la commande SELECT va attendre qu'il n'y ait plus d'écriture avant de lire.

Depuis MySQL 3.23.33, vous pouvez analyser le comportement des verrous sur une table avec les variables de statut Table_locks_waited et Table_locks_immediate :

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Depuis MySQL 3.23.7 (3.23.25 pour Windows), vous pouvez librement mélanger des commandes INSERT et SELECT sur une table MyISAM sans verrous, si les commandes INSERT sont sans conflit. C'est à dire, vous pouvez insérer des lignes dans une table MyISAM en même temps que d'autres clients lisent la même table. Aucun conflit ne survient si la table ne contient aucun bloc libre dans les données, et que les lignes sont insérées à la fin de la table. Les trous sont des lignes qui ont été effacées. S'il y a des trouvés, les insertions concurrentes sont réactivées automatiquement, lorsque les trous sont bouchés par de nouvelles données.

Pour contourner ce problème dans les cas où vous voulez faire de nombreuses INSERT et SELECT sur la même table, vous pouvez insérer les lignes dans une table temporaire, et ne modifier la table réelle que de temps en temps, à partir de la table temporaire.

Ceci peut être fait comme ceci :

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDB utilise un verrouillage de ligne, et BDB utilise un verrouillage de page. Pour les moteurs InnoDB et BDB, un blocage de verrou est possible. Cela est dû au fait que InnoDB obtient automatiquement un verrou de ligne, et BDB pose le verrou de page durant le traitement SQL, et non pas au démarrage de la transaction.

Avantages du verrouillage de ligne :

Inconvénients du verrouillage de ligne :

Les verrous de tables sont supérieurs aux verrous de page ou de ligne dans les cas suivants :

Autres possibilités alternatives au verrouillage de ligne ou de page :

Le versionnage (comme celui que nous utilisons pour les insertions simultanées avec MySQL), où vous pouvez avoir un thread qui écrit et de nombreux autres qui lisent. Cela signifie que les bases ou tables supportent différentes vues des données, suivants le moment d'accès aux données. D'autres noms pour cette techniques sont time travel, copy on write ou copy on demand.

La copy on demand (copie sur demande) est dans de nombreuses situations bien meilleure que le verrouillage de page ou de ligne. Le pire reste l'utilisation de mémoire, qui est bien plus forte qu'avec les verrous normaux.

Au lieu d'utiliser le verrouillage de ligne, vous pouvez utiliser des verrous au niveau de l'application (comme les get_lock/release_lock de MySQL). Cela ne fonctionne qu'avec les applications bien élevées.