Explication détaillée de la plage de verrouillage au niveau des lignes du moteur InnoDB de la base de données MySQL

Le moteur de base de données MySQL InnoDB prend en charge le verrouillage au niveau des lignes, ce qui signifie que nous pouvons effectuer des opérations de verrouillage sur certaines lignes de données de la table. L'impact de l'opération de verrouillage est le suivant : si une chose effectue une opération de verrouillage sur une ligne de la table. table, et une autre transaction doit également effectuer une opération de verrouillage sur la même ligne, de sorte que l'opération de verrouillage de la deuxième transaction peut être bloquée. Une fois bloquée, la deuxième transaction ne peut qu'attendre que la première transaction soit terminée (validation ou restauration). ou expire.

Cet article présente principalement les concepts liés aux verrous de ligne dans InnoDB, en se concentrant sur la portée de verrouillage des verrous de ligne :

• Quels types d'instructions SQL seront verrouillés ?

• Quel type de verrou dois-je ajouter ?

• Quelles lignes seront verrouillées par l'instruction de verrouillage ?

Connaissances de base

Ci-dessus, nous avons brièvement présenté les verrous au niveau des lignes d'InnoDB Afin de comprendre la partie de vérification ultérieure, nous devons ajouter quelques connaissances de base. Si vous connaissez très bien les connaissances correspondantes, vous pouvez passer directement à la partie vérification.

1. Types de verrous InnoDB

Le moteur InnoDB utilise sept types de verrous, à savoir :

• Verrouillage exclusif partagé (Partagé et verrous exclusifs)

• Verrouillages d'intention

• Verrouillages d'enregistrement

• Verrous d'espacement

• Verrous à clé suivante

• Insérer des verrous d'intention

• Verrous AUTO-INC

Cet article concerne principalement les verrous partagés et exclusifs, les verrous d'enregistrement, les verrous d'espacement et les verrous à clé suivante si vous l'êtes. Si vous êtes intéressé par d'autres types de serrures, vous pouvez en apprendre davantage à leur sujet par vous-même, et je n'entrerai pas dans les détails ici.

1.1 Verrous partagés et exclusifs

Les concepts de verrous partagés (verrous S) et de verrous exclusifs (verrous X) sont apparus dans de nombreux langages de programmation. Décrivons d'abord l'impact de ces deux verrous dans MySQL :

• Si une transaction ajoute un verrou S à une certaine ligne de données, une autre transaction peut également ajouter un verrou S à la ligne correspondante . lock, mais le verrou X ne peut pas être ajouté à la ligne correspondante.

• Si une transaction ajoute un verrou X à une certaine ligne de données, une autre transaction ne peut ajouter ni un verrou S ni un verrou X à la ligne correspondante.

Utilisez un tableau matriciel classique pour continuer à illustrer la relation d'exclusion mutuelle entre les verrous partagés et les verrous exclusifs :

--

S

X

--	S	X
S	0	1
X	1	1

S td >

0

1

X

1

1

Dans la figure, S représente un verrou partagé et X représente un verrou exclusif. 0 représente la compatibilité du verrou et 1 représente le conflit de verrouillage n'est pas bloqué et le conflit est bloqué. Le tableau montre qu'une fois qu'une transaction ajoute un verrou exclusif, les autres transactions doivent attendre tout verrou. Plusieurs verrous partagés ne se bloqueront pas.

1.2 Verrous d'enregistrement, verrous d'espacement, verrous à clé suivante

Ces trois types de verrous décrivent tous la portée du verrou, ils sont donc expliqués ensemble.

Les définitions suivantes sont tirées de la documentation officielle de MySQL

• Verrouillages d'enregistrement : les verrous d'enregistrement verrouillent un enregistrement dans l'index.

• Verrous d'espacement : les verrous d'espacement verrouillent soit la valeur au milieu de l'enregistrement d'index, soit la valeur avant le premier enregistrement d'index ou la valeur après le dernier enregistrement d'index.

• Verrous à clé suivante : le verrouillage à clé suivante est une combinaison d'un verrouillage d'enregistrement sur l'enregistrement d'index et d'un verrouillage d'espace avant l'enregistrement d'index. Les enregistrements d'index sont mentionnés dans les définitions de

. Pourquoi? Quelle est la relation entre les verrous de ligne et les index ? En fait, InnoDB termine l'opération de verrouillage en recherchant ou en analysant l'index dans la table. InnoDB ajoutera un verrou partagé ou un verrou exclusif à chaque donnée d'index qu'il rencontrera. Nous pouvons donc appeler des verrous au niveau de la ligne (verrouillage au niveau de la ligne) des verrous d'enregistrement d'index (verrouillage d'enregistrement d'index), car les verrous au niveau de la ligne sont ajoutés à l'index correspondant à la ligne.

Les plages de verrouillage des trois types de serrures sont différentes et progressivement élargies. Donnons un exemple pour expliquer brièvement la plage de verrouillage des différents verrous. Supposons que la colonne d'index du tableau t ait quatre valeurs numériques​​3, 5, 8 et 9. Selon le document officiel, la plage de verrouillage du trois verrous est déterminé comme suit :

• La plage de verrouillage du verrouillage d'enregistrement est un enregistrement d'index distinct, qui correspond aux quatre lignes de données 3, 5, 8 et 9.

• Le verrouillage de l'espace verrouille l'espace dans la rangée, qui est représenté par un ensemble comme (-∞,3), (3,5), (5,8), (8 ,9), (9,+∞).

• Le verrouillage Next-Key est une combinaison du verrouillage de l'enregistrement d'index et du verrouillage de l'espace avant le verrouillage de l'enregistrement d'index. Il est représenté par un ensemble comme (-∞,3], (3. , 5], (5,8], (8,9], (9,+∞).

Enfin, il y a trois autres points à ajouter concernant le verrouillage de l'espace :

1. Les verrous d'espacement empêchent d'autres transactions d'insérer simultanément des données d'espacement, ce qui peut résoudre efficacement le problème fantôme. Pour cette raison, tous les niveaux d'isolation des transactions n'utilisent pas de verrous d'espacement, Le moteur MySQL InnoDB utilise uniquement les verrous d'espacement au niveau d'isolement de lecture répétable (par défaut)

2. Le rôle des verrous d'espacement est uniquement d'empêcher d'autres transactions d'insérer des données dans les espaces. Cela empêchera d'autres transactions d'avoir le même verrou d'espacement. Cela signifie que à l'exception de l'instruction insert, d'autres instructions SQL peuvent ajouter des verrous d'espacement à la même ligne sans être bloquées

3. .

   Pour le comportement de verrouillage de l'index unique, le verrouillage d'espacement sera invalide. À ce stade, seul le verrouillage d'enregistrement fonctionnera

Instruction de verrouillage

<.> Nous avons déjà présenté qu'InnoDB implémente le verrouillage en analysant les enregistrements d'index lors de l'exécution des instructions SQL. Quelles instructions ajouteront quel type de verrous ? Décrivons-les un par un :

select. ... from instruction : Le moteur InnoDB utilise le contrôle de concurrence multiversion (MVCC) pour implémenter la lecture non bloquante, donc pour les instructions de lecture de sélection ordinaires, InnoDB ne verrouille pas [Note 1]

• select ... from lock in share mode instruction : La différence entre cette instruction et l'instruction select ordinaire est que le verrouillage en share est ajouté à la fin du mode, nous pouvons deviner d'après le sens littéral qu'il s'agit d'une instruction de lecture verrouillée. , et le type de verrou est un verrou partagé (verrouillage en lecture). InnoDB ajoutera les verrous de clé suivante à tous les enregistrements d'index recherchés, mais si le seul index analysé est la seule ligne, la clé suivante est rétrogradée en verrou d'enregistrement d'index <.>

• select ... from for update instruction : Comme l'instruction ci-dessus, cette instruction ajoute un verrou exclusif (write lock ) ajoutera les verrous de clé suivante à tous les enregistrements d'index recherchés. , mais si la ligne unique d'un index unique est analysée, la clé suivante est rétrogradée en verrou d'enregistrement d'index

update... où ... instruction : InnoDB ajoutera les verrous de clé suivante à tous les enregistrements d'index recherché, mais si la ligne unique d'un index unique est analysée, la clé suivante est rétrogradée en verrou d'enregistrement d'index. 【Note 2】
supprimer... où... déclaration :. InnoDB ajoutera des verrous de clé suivante à tous les enregistrements d'index recherchés, mais si une ligne unique d'un index unique est analysée, la clé suivante est rétrogradée en verrou d'enregistrement d'index.
instruction insert : InnoDB définira uniquement un verrou d'enregistrement d'index exclusif sur la ligne à insérer.
Enfin deux points à ajouter :
Si une requête utilise un index auxiliaire et ajoute un verrou exclusif à l'enregistrement d'index, InnoDB verrouillera l'enregistrement d'index agrégé correspondant.

Si votre instruction SQL ne peut pas utiliser d'index, MySQL doit analyser la table entière pour traiter l'instruction. Le résultat est que chaque ligne de la table sera verrouillée et les autres utilisateurs en seront empêchés. d'accéder à l'instruction. Toutes les insertions dans la table.
Vérification des instructions SQL

2. Sans plus tarder, passons à la partie clé de vérification des instructions SQL de cet article.

   1. Environnement de test

   Base de données : MySQL 5.6.35
   Niveau d'isolement des transactions : lecture répétable
   Terminal d'accès à la base de données : client MySQL

   Scénario de vérification

   2.

   2.1 Scénario 1

   Créer une table :

   CREATE TABLE `user` (
    `id` int(11) NOT NULL,
    `name` varchar(8) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   Insérer des données :

   INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
   INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
   INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
   INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
   INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

   Nous exécutons d'abord le modèle de l'instruction SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name='e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de name à l'étape 5, et observez les résultats :

   name的值	执行结果
   a	不阻塞
   b	不阻塞
   d	阻塞
   e	阻塞
   f	阻塞
   h	不阻塞
   i	不阻塞

   En observant les résultats, nous constatons que l'instruction SQL
   SELECT * FROM user where name='e' for update
   verrouille un total de trois lignes d'enregistrements dans le nom de l'index, (L'intervalle c,e] doit être le prochain verrou à clé et l'intervalle (e,h) est l'intervalle après l'enregistrement d'index e.

   Ensuite, nous déterminez quelle partie du verrou de la clé suivante est le verrou de l'enregistrement d'index.

   Modèle pour l'exécution des instructions SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name='e' for update;	--
   3	--	SELECT * FROM user where name=#{name} for update;
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de name à l'étape 5 et observez le. résultat :

   name的值	执行结果
   d	不阻塞
   e	阻塞
   f	不阻塞

   Parce que le verrouillage d'espacement ne bloquera que l'instruction d'insertion, donc pour les mêmes données d'index, si l'instruction insert bloque mais que l'instruction select for update ne bloque pas, il s'agit d'un verrouillage d'espacement. Si les deux instructions sont bloquées, il s'agit d'un verrouillage d'enregistrement d'index. En observant les résultats de l'exécution, nous pouvons voir que d et f sont le verrouillage d'espace, et e est le verrouillage d'enregistrement d'index

   Conclusion : via deux SQL, nous avons déterminé que la plage de verrouillage pour le nom de l'index auxiliaire lorsque la condition de requête est

   est (c, e], (e,g), où :

   where name='e'  

   ajoute le verrouillage de l'enregistrement d'index [e] à l'enregistrement d'index e analysé par l'instruction SQL
   est verrouillé L'espace avant e, les données (c, e) entre c et e sont ajoutées avec un verrouillage d'espace.
   Les deux premiers constituent la serrure à clé suivante (c, e).
   Il est à noter que l'écart (e, g ) derrière e est également verrouillé.
   Les lecteurs attentifs ont peut-être remarqué ce que nous avons dit ici. Il n'y a pas de données de limite d'écart c et g dans les données de test. valeur limite :

   Remplacez les valeurs de id et name à l'étape 5, et observez les résultats :

   id的值	name=c	执行结果	id的值	name=g	执行结果
   --	--	--	-3	g	组塞
   --	--	--	-2	g	阻塞
   -1	c	不阻塞	-1	g	阻塞
   1	c	不阻塞	1	g	不阻塞
   2	c	不阻塞	2	g	阻塞
   3	c	不阻塞	3	g	不阻塞
   4	c	阻塞	4	g	阻塞
   5	c	阻塞	5	g	阻塞
   6	c	阻塞	6	g	阻塞
   7	c	不阻塞	7	g	不阻塞
   8	c	阻塞	8	g	不阻塞
   9	c	不阻塞	9	g	不阻塞
   10	c	阻塞	10	g	不阻塞
   11	c	阻塞	-	-	-
   12	c	阻塞	-	-	-

   En observant les résultats d'exécution ci-dessus, nous avons constaté que lorsque name est égal à c et e, le le résultat de l'instruction insert augmente avec la valeur de id Différent du verrouillage pendant un certain temps et du non-verrouillage pendant un certain temps. Il faut que la colonne ID soit verrouillée pour provoquer un tel résultat.

   Si nous ne regardons pas d'abord les résultats de la id=5 ligne de données, nous trouvons un modèle :

   • Quand name=c, name=c correspond à id=3 Les espaces (3,5), (5,7), (7,9) et (9,∞) après l'enregistrement de données d'index agrégé d'identifiant sont tous verrouillés.

   • Quand name=e, name=e correspond aux écarts (5,7), (3,5), (1, 3), (-∞,1) sont tous fermé. id=7

   • Nous pouvons utiliser l'instruction

     pour déterminer que les verrous ajoutés aux espaces ci-dessus sont tous des verrous d'espace. select * from user where id = x for update;

   Ensuite, nous expliquons la situation de verrouillage du

   id=5

   Modèle d'exécution des instructions SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name='e' for update;	--
   3	--	SELECT * FROM user where id=#{id} for update;
   5	rollback;	--
   6	--	rollback;

   Remplacez l'identifiant à l'étape 5 Valeur , résultat de l'observation :

   通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

   最终结论：  
   对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

   1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。

   2. 对辅助索引对应的聚合索引加上索引记录锁。

   3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

   上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

   2.2 场景二

   建表：

   CREATE TABLE `user` (
     `id` int(11) NOT NULL,
     `name` varchar(8) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `index_name` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   注意与场景一表user不同的是name列为唯一索引。

   插入数据：

   INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
   INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
   INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
   INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
   INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

   首先我们执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name='e' for update;
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
   5	rollback;	--
   6	--	rollback;

   替换步骤5中name的值，观察结果：

   name的值	执行结果
   a	不阻塞
   b	不阻塞
   c	不阻塞
   d	不阻塞
   e	阻塞
   f	不阻塞
   g	不阻塞
   h	不阻塞
   i	不阻塞

   由测试结果可知，只有name='e'这行数据被锁定。

   通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

   2.3 场景三

   场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

   借用场景一的表和数据。

   建表：

   CREATE TABLE `user` (
     `id` int(11) NOT NULL,
     `name` varchar(8) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `index_name` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   插入数据：

   INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
   INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
   INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
   INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
   INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

   执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
   5	rollback;	--
   6	--	rollback;

   替换步骤5中name的值，观察结果：

   name的值	执行结果
   a	阻塞
   b	阻塞
   c	阻塞
   d	阻塞
   e	阻塞
   f	阻塞
   g	阻塞
   h	阻塞
   i	阻塞

   这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

   我们执行以下的SQL语句执行计划：

    explain select * from user where name>'e' for update;

   执行结果：

   +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
   | id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
   +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
   |  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
   +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
   1 row in set (0.00 sec)

   如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

   通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

   接下来我们再制造一组数据。  
   建表：

   CREATE TABLE `user` (
    `id` int(11) NOT NULL,
    `name` varchar(8) NOT NULL,
    `age` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_name` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   插入数据：

   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

   这张表和前表的区别是多了一列非索引列age。

   我们再执行一下同样的SQL语句执行计划：

    explain select * from user where name>'e' for update;

   执行结果：

   +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
   | id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
   +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
   |  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
   +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
   1 row in set (0.00 sec)

   是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

   因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

   1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

   2. g和i对应id列中的7和9加索引记录锁。

   3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

   接下来我们逐一测试：

   首先测试验证了next-key锁范围，执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
   5	rollback;	--
   6	--	rollback;

   替换步骤5中name的值，观察结果：

   name的值	执行结果
   a	不阻塞
   b	不阻塞
   c	不阻塞
   d	不阻塞
   f	阻塞
   g	阻塞
   h	阻塞
   i	阻塞
   j	阻塞
   k	阻塞

   下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	SELECT * FROM user where name=#{name} for update;
   5	rollback;	--
   6	--	rollback;

   替换步骤5中name的值，观察结果：

   name的值	执行结果
   e	不阻塞
   f	不阻塞
   g	阻塞
   h	不阻塞
   i	阻塞
   j	不阻塞

   接下来验证对id列加索引记录锁，执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	SELECT * FROM user where id=#{id} for update;
   5	rollback;	--
   6	--	rollback;

   替换步骤5中id的值，观察结果：

   id的值	执行结果
   5	不阻塞
   6	不阻塞
   7	阻塞
   8	不阻塞
   9	阻塞
   10	不阻塞

   最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
   5	rollback;	--
   6	--	rollback;

   替换步骤5中id的值，观察结果：

   id的值	执行结果
   -1	不阻塞
   1	不阻塞
   2	不阻塞
   3	不阻塞
   4	不阻塞
   5	不阻塞
   6	阻塞
   7	阻塞
   8	阻塞
   9	阻塞
   10	阻塞
   11	阻塞
   12	阻塞

   注意7和9是索引记录锁记录锁。

   观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

   2.4 场景四

   我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

   建表：

   CREATE TABLE `user` (
     `id` int(11) NOT NULL,
     `name` varchar(8) NOT NULL,
     `age` int(11) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `index_name` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   插入数据：

   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
   INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

   和场景三表唯一不同是name列为唯一索引。

   SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

   我们通过SQL验证我们的结论，执行SQL语句的模板：

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de name à l'étape 5 et observez les résultats :

   name的值	执行结果
   a	不阻塞
   b	不阻塞
   c	不阻塞
   d	不阻塞
   f	阻塞
   g	阻塞
   h	阻塞
   i	阻塞
   j	阻塞
   k	阻塞

   Vérifions quelle partie du verrou de la clé suivante est le verrou d'espacement et quelle partie est le verrou de l'enregistrement d'index. pour exécuter l'instruction SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	SELECT * FROM user where name=#{name} for update;
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de name à l'étape 5 et observez les résultats :

   name的值	执行结果
   e	不阻塞
   f	不阻塞
   g	阻塞
   h	不阻塞
   i	阻塞
   j	不阻塞

   Grâce aux résultats de vérification des deux instructions SQL ci-dessus, nous avons a prouvé la tendance de la gamme de serrures de notre g et i Stack, la combinaison de touches suivante des deux.

   Ensuite, nous vérifions le transfert de verrouillage vers l'index agrégé après avoir verrouillé l'index auxiliaire, et exécutons le modèle de l'instruction SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	SELECT * FROM user where id=#{id} for update;
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de id à l'étape 5, et observez les résultats :

   id的值	执行结果
   5	不阻塞
   6	不阻塞
   7	阻塞
   8	不阻塞
   9	阻塞
   10	不阻塞

   Il ressort des résultats que des verrous d'enregistrement d'index sont ajoutés aux colonnes 7 et 9 de l'index agrégé name correspondant aux colonnes g et i de l'index auxiliaire id.

   Jusqu'à présent, tous les résultats expérimentaux sont exactement les mêmes que ceux du scénario trois, qui est également facile à comprendre. Après tout, le scénario quatre et le scénario trois ne sont que des types d'index auxiliaires différents name, l'un est un. index unique et l'autre est un index normal.

   Enfin, vérifiez l'intention, la clé suivante verrouille les données limites e et voyez que la conclusion est la même que celle du scénario trois.

   Modèle d'exécution des instructions SQL :

   步骤	client 1	client 2
   1	begin;	--
   2	SELECT * FROM user where name>'e' for update;	--
   3	--	begin;
   4	--	INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
   5	rollback;	--
   6	--	rollback;

   Remplacez la valeur de id à l'étape 5 et observez les résultats :

   id的值	执行结果
   -1	不阻塞
   1	不阻塞
   2	不阻塞
   3	不阻塞
   4	不阻塞
   5	不阻塞
   6	不阻塞
   7	阻塞
   8	不阻塞
   9	阻塞
   10	不阻塞
   11	不阻塞
   12	不阻塞

   Notez que 7 et 9 sont des verrous d'enregistrement de verrouillage d'enregistrement d'index .

   Les résultats montrent que lorsque name est répertorié comme limite supérieure e de l'enregistrement d'index, il n'y a pas de comportement de verrouillage sur l'identifiant, ce qui est différent du scénario trois.

   Les requêtes de plage pour les index uniques sont similaires aux requêtes de plage pour les index ordinaires. La seule différence est que lorsque l'index auxiliaire est égal à la valeur limite des plages supérieure et inférieure, les verrous d'espacement ne seront pas ajoutés. clé primaire.

   Plage de verrouillage de requête de plage d'index unique :

   • La plage de verrouillage pour les enregistrements d'index auxiliaires analysés est constituée de plusieurs enregistrements d'index suivants - Combinaison de superposition de gamme de touches.

   • Pour la plage de verrouillage de l'index agrégé (clé primaire), des verrous d'enregistrement d'index seront ajoutés aux colonnes de l'index agrégé correspondant à plusieurs index auxiliaires.

   Conclusion

   Le moteur InnoDB ajoutera les verrous correspondants aux enregistrements d'index qu'il a analysés. À travers le "Scénario 1", nous avons clairement indiqué que l'analyse d'un. commun La plage de verrouillage des enregistrements d'index. Grâce au "Scénario 3", nous pouvons déduire la plage de verrouillage de n'importe quel nombre d'enregistrements d'index pour analyser les index ordinaires. Grâce au "Scénario 2", nous avons déterminé la plage de verrouillage pour analyser un enregistrement d'index unique (ou clé primaire). Grâce au "Scénario 4", nous pouvons déduire la plage de verrouillage d'un nombre quelconque d'enregistrements d'index d'analyse (ou clés primaires). Il peut être utilisé de manière flexible dans des applications réelles pour déterminer si deux instructions SQL sont verrouillées l'une sur l'autre. Il convient également de noter ici que les conditions de requête de l'index ne peuvent pas être considérées comme acquises. Elles ne correspondent souvent pas à ce que nous comprenons. Il est nécessaire de juger du nombre d'enregistrements finalement analysés par l'index en fonction du plan d'exécution, sinon ce sera le cas. provoquer des écarts dans la compréhension de la plage de verrouillage.

   ---

   ---

   ---

   Remarques

   Remarque 1 : lorsque le niveau d'isolement des transactions est SERIALIZABLE, les instructions de sélection ordinaires ajouteront également des index analysés lors de l'exécution du verrouillage de la touche suivante. Si l'instruction analyse un index unique, le verrouillage de la clé suivante est rétrogradé en verrouillage d'enregistrement d'index.
   Remarque 2 : Lorsqu'une instruction de mise à jour modifie un enregistrement d'index agrégé (clé primaire), une opération de verrouillage implicite sera effectuée sur l'index auxiliaire affecté. Lorsqu'une analyse de vérification des doublons est effectuée avant l'insertion d'un nouvel enregistrement d'index secondaire et lorsqu'un nouvel enregistrement d'index secondaire est inséré, l'opération de mise à jour ajoute également des verrous partagés sur les enregistrements d'index secondaire concernés.

   Recommandations associées :
   

   Erreur MySQL lors de l'exécution du fichier SQL Erreur : Moteur de stockage inconnu'InnoDB Comment résoudre

   MySQL démarre Que faire si le moteur InnoDB est désactivé

   Comparaison entre le moteur de stockage MySQL MyISAM et InnoDB

   
   

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!