dbnewz

J’ai eu un petit problème sympa à résoudre. En résumé, nous avons 2 tables t1 et t2.

mysql> select * from t1;
+——+——+
| id | name |
+——+——+
| 1 | a |
| 2 | b |
| 3 | ab |
| 4 | bc |
+——+——+

mysql> select * from t2;
+————–+———+
| dependent_id | base_id |
+————–+———+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 3 | 2 |
| 4 | 2 |
+————–+———+

Nous ouvrons une transaction (T1) et nous exécutons la requête suivante.

SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;

+——+——+————–+———+
| id | name | dependent_id | base_id |
+——+——+————–+———+
| 1 | a | 1 | 1 |
| 3 | ab | 3 | 1 |
+——+——+————–+———+

Nous ouvrons une transaction (T2) et nous exécutons la requête suivante.

SELECT * FROM roles WHERE id=2 FOR UPDATE;

Et alors que les 2 requêtes ne sont pas sensées travailler sur les même lignes, T2 attend la fin de T1? Comment est ce possible?

Pour comprendre vous devez analyser le plan d’exécution.

mysql> explain SELECT * FROM t1 JOIN t2 WHERE t1.id=t2.dependent_id AND t2.base_id=1 FOR UPDATE;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |

Voila l’erreur, MySQL fait un full table scan (type ALL) pour exécuter cette requête et ainsi bloque toutes les lignes. T2 est ainsi obliger d’attendre la fin de T1. Pourquoi?

Ayant seulement 4 entrées dans cette table, l’optimiseur de MySQL préfère un faire un full table scan plutot qu’utiliser un index. De la j’ ai inséré d’autre entrée dans la table t1, et le plan d’exécution a changé.

| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 217 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.dependent_id | 1 | |

MySQL bloque ainsi seulement la PK et fait bien du ‘row level locking’. T2 peut aboutir sans problème. CQFD!

Mots-clefs : pratique

Cet article a été publié le Mardi 28 août 2007 à 12:47 et est classé dans MySQL. Vous pouvez en suivre les commentaires par le biais du flux RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre propre site.