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!

---

Autres billets susceptibles de vous intéresser :

Les covering index, de la théorie à la pratique avec MyISAM et InnoDB

Cardinalité, sélectivité et distributivité d’un index MySQL : quel impact sur le plan d’exécution ?

Générer un jeu de données : shell, mysqlslap, et procédure stockée

Installation du MySQL Cluster

mysql_secure_installation, utile mais non paramétrable

Cet article a été publié le Tuesday 28 August 2007 à 12:47 am et est classé dans MySQL. Vous pouvez suivre les réponses reçues par cet article grâce au fil RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre site.