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!

Lorsque vous avez besoin de créer un identifiant unique, la plupart des SGBD vous propose ce que l’on appelle une séquence. Je traduirais ça par une suite, une liste ordonnée d’objets. A chaque fois que tu avais besoin d’un identifiant unique vous incrémentez la séquence de 1. En général la syntaxe est plus ou moins la même, select (sequence).NEXTVAL et le tour est joué. Maintenant MySQL ne fournit pas un mécanisme de ce genre pour l’instant, donc soit vous simulez vous même ce comportement ( en créant une table réservée à la génération d’identifiant unique) soit vous utilisez une colonne AUTO_INCREMENT.

CREATE TABLE `mytable` (
`id` tinyint unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
)

Un limitation déjà, l’AUTO_INCREMENT doit faire parti de la clé primaire. A chaque fois que vous ajoutez une ligne sans cette table la valeur sera incrémenté de 1 par rapport à la dernière valeur ajouté dans la table. Et c’est la que le bas blesse. J’ai eu cette semaine toute un chaîne de serveurs bloqué pendant quelques heures à cause de cet AUTO_INCREMENT.

Même si vous remettez l’AUTO_INCREMENT à 1 (ALTER TABLE tbl AUTO_INCREMENT = 1;) sur le prochain INSERT la valeur sera toujours MAX(id) + 1.

Il y a des limites à l’utilisation de cette fonctionnalité, principalement si votre architecture repose sur des serveurs en DUAL master. En résumé, l’AUTO_INCREMENT n’est PAS une séquence.