InnoDB, verrouillage, transactions et index

30 septembre 2009 par stephane

Bin voyons, tout ça en un seul post ! On se rapproche doucement de Noël, mais quand même !
Je m’explique…

Je me suis intéressé récemment aux instructions SELECT … LOCK IN SHARE MODE et SELECT … FOR UPDATE, qui sont censées permettre de verrouiller explicitement des lignes pour les tables InnoDB.
On comprend maintenant les deux premiers termes du titre :)

Pour mes test, je crée une table avec quelques enregistrements :
mysql> CREATE TABLE innotest (
id int(11) NOT NULL,
col varchar(10) DEFAULT NULL
) ENGINE=InnoDB;

mysql> INSERT INTO innotest (id,col) VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee');

1er essai :
J’ouvre une 1ère session :
mysql> SELECT * FROM innotest WHERE col > 'c%' LOCK IN SHARE MODE;
+----+------+
| id | col  |
+----+------+
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
+----+------+
3 rows in set (0,00 sec)

Si je comprends bien, LOCK IN SHARE MODE pose un verrou partagé sur les lignes d’id 3, 4 et 5. Donc, normalement, si j’ouvre une autre session et que j’essaie de poser un verrou exclusif sur la ligne d’id 5, ma requête devrait être bloquée jusqu’à ce que je lève mon verrou partagé.

Essayons :

J’ouvre une 2nde session :
mysql> SELECT * FROM innotest WHERE id=5 FOR UPDATE;
+----+------+
| id | col  |
+----+------+
|  5 | eee  |
+----+------+
1 row in set (0,00 sec)

Hum ? La requête dans la 2nde session n’est pas bloquée, ce qui signifie que soit le verrou partagé n’a pas été posé, soit le verrou a bien été posé mais a été relâché d’une manière ou d’une autre.
Direction donc le manuel de référence.

Bon sang, mais c’est bien sûr ! Les verrous sont relâchés à la fin des transactions et comme je suis en mode AUTOCOMMIT, la transaction se termine tout de suite après chacun de mes SELECT.
Conclusion : il faut refaire la même chose, mais dans une transaction (3è terme du titre :) )

2è essai:
J’ouvre une 1ère session :
mysql> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM innotest WHERE col > 'c%' LOCK IN SHARE MODE;
+----+------+
| id | col  |
+----+------+
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
+----+------+
3 rows in set (0,00 sec)

puis une 2nde session :
mysql> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM innotest WHERE id=5 FOR UPDATE;

Cette fois, je ne récupère pas la main sur mon SELECT … FOR UPDATE car ce SELECT ne peut pas acquérir le verrou exclusif sur la ligne d’id 5 à cause du SELECT de l’autre session qui a déjà un verrou partagé sur cette ligne. Ouf ! Je vérifie bien que le SELECT … LOCK IN SHARE MODE a posé des verrous comme je le souhaitais.

Si maintenant, je fais un COMMIT dans ma 1ère session, le verrou partagé est levé et le SELECT … FOR UPDATE peut alors se terminer.

A noter que dans la 2ème session, je n’ai pas réellement besoin du START TRANSACTION, puisque ce sont les verrous posés par le SELECT … LOCK IN SHARE MODE qui m’intéressent ici.

Ce point étant clarifié, passons à une autre vérification : le LOCK IN SHARE MODE a-t-il bien verrouillé uniquement les lignes d’id 3, 4 et 5 ?

Facile : il suffit de répéter la même séquence que dans le 2è essai mais en modifiant la requête dans la 2è session pour demander un verrou exclusif sur la ligne d’id 1. Cette ligne n’étant pas censée être verrouillée, je dois pouvoir obtenir le verrou immédiatement.

3è essai :
1ère session :
mysql> START ...

2nde session :
mysql> SELECT * FROM innotest WHERE id=1 FOR UPDATE;

Et là, c’est le drame ! Le verrou exclusif ne peut pas être acquis et donc la requête est bloquée. Il n’y a qu’un seul suspect possible, c’est le SELECT … LOCK IN SHARE MODE de la 1ère session.

Comme bien souvent, pour comprendre ce qui se passe dans une requête, un petit coup d’EXPLAIN est bien utile :
mysql> EXPLAIN SELECT * FROM innotest WHERE col > 'c%' LOCK IN SHARE MODE\G
******************* 1. row *****************
id: 1
select_type: SIMPLE
table: innotest
type: ALL
...

Maintenant, tout est clair ! Le type ALL indique un full table scan, or comme InnoDB acquiert les verrous au fur et à mesure qu’il en a besoin, dans ce cas, il lui faut acquérir un verrou sur toutes les lignes de la table. Evidemment, ce n’était pas très facile à deviner…

Tout espoir est-il perdu de faire verrouiller par une transaction une partie des lignes et de faire verrouiller par une autre transaction une autre partie des lignes ? A priori, si InnoDB était capable dans mon 1er SELECT de trouver les lignes 3, 4 et 5 sans faire de full table scan, ça pourrait être possible.

Comment faire ce miracle ? Avec un index sur col, bien sûr ! (Et voilà, 4è terme du titre :) )

mysql> ALTER TABLE innotest ADD INDEX idx_col(col);

et on recommence la séquence du 3è essai :
1ère session :

2nde session :
mysql> SELECT * FROM innotest WHERE id=1 FOR UPDATE;

Aïe, ça bloque encore et toujours :(

Un EXPLAIN sur le SELECT … LOCK IN SHARE MODE montre pourtant bien que l’index est utilisé :
mysql> EXPLAIN SELECT * FROM innotest WHERE col > 'c%' LOCK IN SHARE MODE\G
************* 1. row ***********
id: 1
select_type: SIMPLE
table: innotest
type: range
possible_keys: idx_col
key: idx_col
...

Oui mais maintenant, le problème vient du 2è SELECT. Comme il n’y a pas d’index sur id, MySQL fait un full table scan pour résoudre la requête (c’est confirmé par un appel à EXPLAIN) et pose des verrous exclusifs sur toutes les lignes rencontrées…sauf que les lignes 3 à 5 ont déjà un verrou partagé : il n’est donc pas possible de poser le verrou exclusif, ce qui bloque toute la requête.

La solution passe là encore par un index, mais cette fois-ci sur id. Ou alors, en conservant uniquement l’index sur col, il faut réécrire la requête pour qu’elle fasse intervenir l’index, par exemple :
mysql> SELECT * FROM innotest WHERE name='aaa' FOR UPDATE;

Evidemment, dans la vraie vie, il n’est pas toujours possible de réécrire ainsi ses requêtes, mieux vaut donc poser les bons index.

Cerise sur le gâteau pour finir, en ne conservant que l’index sur col, on peut faire apparaître une jolie deadlock en pensant ne verrouiller que des lignes différentes :
1ère session:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM innotest WHERE col > 'c%' LOCK IN SHARE MODE;

2nde session :
mysql> SELECT * FROM innotest WHERE id=1 FOR UPDATE;

puis de retour dans la 1ère session :
mysql> SELECT * FROM innotest where col='bbb' LOCK IN SHARE MODE;

ce qui provoque dans la 2è session :
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Moralité de l’histoire : votre bonne conscience vous répète constamment que les index sont vitaux. Faites plaisir à votre conscience : posez des index ! (pertinents bien sûr…)

Laisser une réponse