Explication de la serrure de la clé suivante - Clé primaire pour la gamme

Question

J'ai la question suivante, et je voulais utiliser le diagramme pour CONFIRMER SI J'AI BIEN COMPRIS LA QUESTION :

SELECT * FROM table WHERE pk > 99;

"pk" est la clé primaire

J'ai des difficultés à comprendre le verrouillage de la clé suivante, j'ai trouvé ce diagramme pour savoir quel verrouillage de l'espace sera appliqué et quel "verrouillage de la clé suivante".

Si le diagramme est erroné, faites-le moi savoir.

Answer 1

Le schéma semble correct.

Je suppose que pk dans votre exemple est la clé primaire d'une table InnoDB, et est donc l'index en grappe.

L'écart est verrouillé à partir d'une valeur supérieure à 97 et s'étend jusqu'à l'infini.

Cela semble étrange, car les valeurs 98 et 99 semblent devoir être exemptes de verrous, parce que la condition est sur WHERE pk > 99 et ne correspond donc pas aux valeurs 98 ou 99. Mais le verrou de la clé suivante verrouille le écart complet avant l'enregistrement de l'index, jusqu'à l'enregistrement de l'index précédent inclus.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks dit :

Un verrouillage de la clé suivante sur un enregistrement d'index affecte également le "vide" avant cet enregistrement d'index. En d'autres termes, un verrou sur la clé suivante est un verrou sur l'enregistrement d'index plus un verrou sur l'espace précédant l'enregistrement d'index.

Démonstration : Dans une première fenêtre, je lance une transaction qui acquiert une serrure à clé suivante :

mysql> select * from mytable;
+-----+-------+
| pk  | name  |
+-----+-------+
|   3 | hello |
|  97 | hi    |
| 101 | hola  |
| 103 | yo    |
| 107 | hey   |
+-----+-------+

mysql> start transaction;

mysql> select * from mytable where pk > 99 for update;
+-----+------+
| pk  | name |
+-----+------+
| 101 | hola |
| 103 | yo   |
| 107 | hey  |
+-----+------+

L'enregistrement de l'index pour le pk 101 est maintenant verrouillé, de même que l'intervalle qui suit le pk 97.

Je teste ceci dans une deuxième fenêtre :

mysql> insert into mytable values (99, 'test');
(hangs)

mysql> insert into mytable values (98, 'test');
(hangs)

mysql> update mytable set name='test' where pk=97;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0