Parlons de l'impasse provoquée par deux instructions INSERT

Cet article vous apporte des problèmes liés au blocage dans MySQL. Il présente principalement les connaissances pertinentes sur le blocage causé par deux instructions INSERT identiques. J'espère qu'il sera utile à tout le monde.

Deux déclarations INSERT identiques ont en fait provoqué une impasse. Est-ce une distorsion de la nature humaine ou une perte de moralité ? On ne peut s'empêcher de soupirer : bon sang ! Cela peut également conduire à une impasse, puis le code de l'entreprise est modifié, impuissant, les larmes aux yeux.

D'accord, avant d'analyser en profondeur pourquoi deux instructions INSERT identiques peuvent provoquer une impasse, introduisons d'abord quelques connaissances de base.

Préparez l'environnement

Pour le bon déroulement de l'histoire, nous créons une nouvelle table des héros qui a été utilisée d'innombrables fois :

CREATE TABLE hero (
    number INT AUTO_INCREMENT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;

Insérez ensuite quelques enregistrements dans cette table :

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

Maintenant, la table des héros a deux index (Un index secondaire unique, un index clusterisé), le diagramme schématique est le suivant :

Comment verrouiller l'instruction INSERT

Les amis qui ont lu "Comment MySQL fonctionne : Comprendre MySQL à partir des principes fondamentaux" doivent savoir, Le L'instruction INSERT ne générera pas de structure de verrouillage lors de l'exécution normale. Elle s'appuie sur la colonne cachée trx_id fournie avec l'enregistrement d'index clusterisé comme verrou implicite pour protéger l'enregistrement.

Mais dans certains scénarios particuliers, l'instruction INSERT générera toujours une structure de verrouillage. Listons-la :

1. Lorsque le prochain enregistrement à insérer a été ajouté à un verrou d'espacement par d'autres transactions

. Chaque fois que vous insérez un nouvel enregistrement, vous devez vérifier si un verrou d'espacement a été ajouté au prochain enregistrement à insérer. Si un verrou d'espacement a été ajouté, l'instruction INSERT doit être bloquée et un verrou d'intention d'insertion doit être généré.

Par exemple, pour la table hero, la transaction T1 s'exécute dans le niveau d'isolement REPEATABLE READ (appelé RR à l'avenir, et READ COMMITTED sera également appelé RC à l'avenir), et l'instruction suivante est exécutée :

# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
+--------+------------+---------+
2 rows in set (0.02 sec)

Cette instruction sera Les trois enregistrements avec les valeurs de clé primaire 1, 3 et 8 sont tous ajoutés avec des verrous de clé suivante de type X. Si vous n'y croyez pas, utilisons l'instruction SHOW ENGINE INNODB STATUS pour. voir la situation de verrouillage. L'enregistrement pointé par la flèche dans l'image est l'enregistrement avec la valeur numérique 8. Enregistrement :

Conseils :

Quant à la façon de verrouiller les instructions SELECT, DELETE et UPDATE, nous l'avons déjà fait. nous les avons analysés dans des articles précédents, nous n'entrerons donc pas dans les détails ici.

À ce stade, la transaction T2 souhaite insérer un enregistrement d'index clusterisé avec une valeur de clé primaire de 4. Avant d'insérer l'enregistrement, T2 doit d'abord localiser l'emplacement de l'enregistrement d'index clusterisé avec une valeur de clé primaire de 4 dans la page. et découvrez que la valeur de clé primaire principale de l'enregistrement suivant avec la valeur de clé 4 est 8, et que l'enregistrement d'index clusterisé avec la valeur de clé primaire 8 a été ajouté avec un verrou d'espacement (le verrouillage de clé suivante inclut les verrous d'enregistrement formels et gap locks), puis transaction 1 Il faut entrer dans l'état de blocage et générer une structure de verrouillage de type insert intention lock.

Exécutons l'instruction INSERT dans la transaction T2 pour vérifier :

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(4, 'g关羽', '蜀');

À ce moment, T2 entre dans l'état de blocage. Utilisons SHOW ENGINE INNODB STATUS pour voir la situation de verrouillage :

On peut voir que le primaire. la valeur clé de T2 est 8. Un verrou d'intention d'insertion est ajouté à l'enregistrement d'index clusterisé (c'est-à-dire que le lock_mode X verrouille l'espace avant l'intention d'insertion rec pointée par la flèche), et il est dans l'état d'attente.

D'accord, après vérification, regardons comment cela est implémenté dans le code :

La fonction lock_rec_insert_check_and_lock est utilisée pour voir si d'autres transactions empêchent cette insertion INSERT. Si tel est le cas, alors cette transaction sera bloquée. Une autre transaction ajoute un enregistrement de verrouillage d'intervalle pour générer un verrou d'intention d'insertion. Le processus spécifique est le suivant :

Conseils :

la fonction lock_rec_other_has_conflicting est utilisée pour détecter si le verrou à acquérir cette fois et le verrou existant. le dossier En cas de conflit, les étudiants intéressés peuvent y jeter un œil.

2. Lorsque vous rencontrez des clés en double

Si lors de l'insertion d'un nouvel enregistrement, il s'avère que la clé primaire ou la colonne d'index secondaire unique de l'enregistrement existant sur la page est différente de la clé primaire ou secondaire unique. colonne d'index de l'enregistrement à insérer Les valeurs de la colonne d'index sont les mêmes (mais les valeurs des colonnes d'index secondaire uniques de plusieurs enregistrements peuvent être NULL en même temps. Cette situation ne sera pas prise en compte ici). cette fois, la transaction insérant le nouvel enregistrement obtiendra la même valeur de clé qui existe déjà dans la page de verrouillage d'enregistrement.

Si la valeur de la clé primaire est dupliquée, alors :

• Lorsque le niveau d'isolement n'est pas supérieur à RC, la transaction insérant un nouvel enregistrement ajoutera un verrou d'enregistrement formel en forme de S à l'enregistrement d'index cluster existant avec une clé primaire en double valeur clé.

• Lorsque le niveau d'isolement n'est pas inférieur à RR, une transaction qui insère un nouvel enregistrement ajoutera un verrou de clé suivante de type S à l'enregistrement d'index cluster existant avec une valeur de clé primaire en double.

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁，再强调一遍，加的是next-key锁！加的是next-key锁！加的是next-key锁！这是rc隔离级别中为数不多的给记录添加gap锁的场景。

小贴士：

本来设计InnoDB的大叔并不想在RC隔离级别引入gap锁，但是由于某些原因，如果不添加gap锁的话，会让唯一二级索引中出现多条唯一二级索引列值相同的记录，这就违背了UNIQUE约束。所以后来设计InnoDB的大叔就很不情愿的在RC隔离级别也引入了gap锁。

我们也来做一个实验，现在假设上边的T1和T2都回滚了，现在将隔离级别调至RC，重新开启事务进行测试。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(30, 'x荀彧', '魏');
ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'uk_name'

然后执行SHOW ENGINE INNODB STATUS语句看一下T1加了什么锁：

可以看到即使现在T1的隔离级别为RC，T1仍然给name列值为'x荀彧'的二级索引记录添加了S型next-key锁（图中红框中的lock mode S）。

如果我们的INSERT语句还带有ON DUPLICATE KEY... 这样的子句，如果遇到主键值或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X型锁，而不是S型锁（不过具体锁的具体类型是和前面描述一样的）。

好了，又到了看代码求证时间了，我们看一下吧：

row_ins_scan_sec_index_for_duplicate是检测唯一二级索引列值是否重复的函数，具体加锁的代码如下所示：

如上图所示，在遇到唯一二级索引列重复的情况时：

• 1号红框表示对带有ON DUPLICATE ...子句时的处理方案，具体就是添加X型锁。

• 2号红框表示对正常INSERT语句的处理方案，具体就是添加S型锁。

不过不论是那种情况，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key锁。

在主键重复时INSERT语句的加锁代码我们就不列举了。

3. 外键检查时

当我们向子表中插入记录时，我们分两种情况讨论：

• 当子表中的外键值可以在父表中找到时，那么无论当前事务是什么隔离级别，只需要给父表中对应的记录添加一个S型正经记录锁就好了。

• 当子表中的外键值在父表中找不到时：那么如果当前隔离级别不大于RC时，不对父表记录加锁；当隔离级别不小于RR时，对父表中该外键值所在位置的下一条记录添加gap锁。

死锁要出场了

好了，基础知识预习完了，该死锁出场了。

看下边这个平平无奇的INSERT语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀'), ('d邓艾', '魏');

这个语句用来插入两条记录，不论是在RC，还是RR隔离级别，如果两个事务并发执行它们是有一定几率触发死锁的。为了稳定复现这个死锁，我们把上边一条语句拆分成两条语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
INSERT INTO hero(name, country) VALUES('d邓艾', '魏');

拆分前和拆分后起到的作用是相同的，只不过拆分后我们可以人为的控制插入记录的时机。如果T1和T2的执行顺序是这样的：

也就是：

• T1先插入name值为g关羽的记录，可以插入成功，此时对应的唯一二级索引记录被隐式锁保护，我们执行SHOW ENGINE INNODB STATUS语句，发现啥一个行锁（row lock）都没有（因为SHOW ENGINE INNODB STATUS不显示隐式锁）：

• 接着T2也插入name值为g关羽的记录。由于T1已经插入name值为g关羽的记录，所以T2在插入二级索引记录时会遇到重复的唯一二级索引列值，此时T2想获取一个S型next-key锁，但是T1并未提交，T1插入的name值为g关羽的记录上的隐式锁相当于一个X型正经记录锁（RC隔离级别），所以T2向获取S型next-key锁时会遇到锁冲突，T2进入阻塞状态，并且将T1的隐式锁转换为显式锁（就是帮助T1生成一个正经记录锁的锁结构）。这时我们再执行SHOW ENGINE INNODB STATUS语句：

可见，T1持有的name值为g关羽的隐式锁已经被转换为显式锁（X型正经记录锁，lock_mode X locks rec but not gap）；T2正在等待获取一个S型next-key锁（lock mode S waiting）。

• 接着T1再插入一条name值为d邓艾的记录。在插入一条记录时，会在页面中先定位到这条记录的位置。在插入name值为d邓艾的二级索引记录时，发现现在页面中的记录分布情况如下所示：

很显然，name值为'd邓艾'的二级索引记录所在位置的下一条二级索引记录的name值应该是'g关羽'（按照汉语拼音排序）。那么在T1插入name值为d邓艾的二级索引记录时，就需要看一下name值为'g关羽'的二级索引记录上有没有被别的事务加gap锁。

有同学想说：目前只有T2想在name值为'g关羽'的二级索引记录上添加S型next-key锁（next-key锁包含gap锁），但是T2并没有获取到锁呀，目前正在等待状态。那么T1不是能顺利插入name值为'g关羽'的二级索引记录么？

我们看一下执行结果：

# 事务T2
mysql> INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

很显然，触发了一个死锁，T2被InnoDB回滚了。

这是为啥呢？T2明明没有获取到name值为'g关羽'的二级索引记录上的S型next-key锁，为啥T1还不能插入入name值为d邓艾的二级索引记录呢？

这我们还得回到代码上来，看一下插入新记录时是如何判断锁是否冲突的：

看一下画红框的注释，意思是：只要别的事务生成了一个显式的gap锁的锁结构，不论那个事务已经获取到了该锁(granted)，还是正在等待获取（waiting），当前事务的INSERT操作都应该被阻塞。

回到我们的例子中来，就是T2已经在name值为'g关羽'的二级索引记录上生成了一个S型next-key锁的锁结构，虽然T2正在阻塞（尚未获取锁），但是T1仍然不能插入name值为d邓艾的二级索引记录。

这样也就解释了死锁产生的原因：

• T1在等待T2释放name值为'g关羽'的二级索引记录上的gap锁。

• T2在等待T1释放name值为'g关羽'的二级索引记录上的X型正经记录锁。

两个事务相互等待对方释放锁，这样死锁也就产生了。

怎么解决这个死锁问题？

两个方案：

• 方案一：一个事务中只插入一条记录。

• 方案二：先插入name值为'd邓艾'的记录，再插入name值为'g关羽'的记录

推荐学习：mysql视频教程

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!