7.3.1 Méthodes de verrouillage

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.

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.

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

• Si il n'y a pas de verrou sur la table, pose un verrou en écriture dessus.
• Sinon, soumet une requête de verrouillage dans la queue de verrous d'écriture.

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

• Si il n'y a pas de verrou sur la table, pose un verrou en écriture dessus.
• Sinon, soumet une requête de verrouillage dans la queue de verrou de lecture.

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.

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 trouvs, les insertions concurrentes sont réactivées automatiquement, lorsque les trous sont bouchés par de nouvelles données.

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;

• Moins de conflits de lignes, lorsque les mêmes lignes sont utilisées par différents threads.
• Moins de modifications pour les annulations ( ROLLBACK )
• Rend possible le verrouillage d'une ligne pour une longue durée.

• Prend plus de mémoire que les verrous de page ou de table.
• Est plus lent que les verrous de page ou de table, lorsqu'il est utilisé sur une grand partie de la table, car il faut alors poser plusieurs verrous.
• Est vraiment bien pire que les autres verrous si vous utilisez souvent la requête GROUP BY sur la majeure partie des données, ou si vous avez à scanner toute la table.
• Avec des verrous de plus haut niveau, vous pouvez aussi supporter des verrous d'autres types, pour optimiser l'application, car le coût de l'administration est moindre que pour le verrouillage de ligne.

• Les lectures.
• Les lectures et les modifications sur des clés strictes : c'est le cas si une modification ou un effacement de ligne peut être lu en une seule opération dans l'index.

  UPDATE table_name SET column=value WHERE unique_key# DELETE FROM table_name WHERE unique_key=#

• SELECT combiné avec INSERT (et quelques UPDATE et DELETE rares).
• De nombreux scans / GROUP BY sur toute la table, sans aucune écriture.

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

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.