Let's talk about the deadlock caused by two INSERT statements

This article brings you related issues about deadlock in mysql. It mainly introduces the relevant knowledge about deadlock caused by two identical INSERT statements. I hope it will be helpful to everyone.

Two identical INSERT statements actually caused a deadlock. Is this a distortion of human nature or a loss of morality? We can’t help but sigh: Damn it! This can also lead to a deadlock, and then the business code is changed helplessly with tears in his eyes.

Okay, before we deeply analyze why two identical INSERT statements can cause deadlock, let’s introduce some basic knowledge.

Prepare the environment

For the smooth development of the story, we create a new hero table that has been used countless times:

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;

Then insert a few records into this table:

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

Now the hero table has two indexes (a unique secondary index and a clustered index). The diagram is as follows:

How about the INSERT statement? Locking

Friends who have read "How MySQL Runs: Understanding MySQL from the Fundamentals" must know that the INSERT statement does not generate a lock structure during normal execution. It relies on clustered indexes. The trx_id hidden column that comes with the record is used as an implicit lock to protect the record.

However, in some special scenarios, the INSERT statement will still generate a lock structure. Let’s enumerate:

1. The next record to be inserted has already been When gap locks are added to other transactions

Every time a new record is inserted, you need to check whether the next record to be inserted has been added to the gap lock. If a gap lock has been added, , the INSERT statement should be blocked and an insert intention lock generated.

For example, for the hero table, transaction T1 runs in the REPEATABLE READ (referred to as RR in the future, and READ COMMITTED will also be referred to as RC in the future) isolation level and executes the following statement:

# 事务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)

This statement will add X-type next-key locks to the three records with primary key values ​​1, 3, and 8. If you don’t believe it, we can use the SHOW ENGINE INNODB STATUS statement to see the locking situation. The arrow in the picture points to The record is the record with number value 8:

Tips:

As for how to lock the SELECT, DELETE, and UPDATE statements, we It has been analyzed in previous articles and will not be repeated here.

At this time, transaction T2 wants to insert a clustered index record with a primary key value of 4. Before T2 inserts the record, it must first locate the clustered index record with a primary key value of 4 on the page. In the position, it is found that the primary key value of the next record with the primary key value 4 is 8, and the clustered index record with the primary key value 8 has been added with a gap lock (next-key lock includes formal record lock and gap lock), then transaction 1 needs to enter the blocking state and generate a lock structure of type insert intention lock.

Let’s execute the INSERT statement in transaction T2 to verify:

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

At this time, T2 enters the blocking state, and we use SHOW ENGINE INNODB STATUS to check the locking situation:

It can be seen that T2 adds an insertion intention lock to the clustered index record with a primary key value of 8 (that is, the lock_mode X locks gap before rec insert intention pointed by the arrow), and is in the waiting state .

Okay, after verification, let's take a look at how it is implemented in the code:

## The lock_rec_insert_check_and_lock function is used to see if other transactions are blocked. For this INSERT, if yes, then this transaction will generate an insertion intention lock for the record that has been added with a gap lock by another transaction. The specific process is as follows:

Tips:

The lock_rec_other_has_conflicting function is used to detect whether the lock to be acquired this time conflicts with the existing lock on the record. Interested students can take a look.

2. When encountering duplicate keys

If when inserting a new record, the primary key of an existing record on the page is found Or the unique secondary index column has the same value as the primary key or unique secondary index column of the record to be inserted (but the value of the unique secondary index column of multiple records can be NULL at the same time, this situation is not considered here) , at this time, the transaction that inserts the new record will acquire the lock of the record with the same key value that already exists in the page.

If the primary key value is duplicated, then:

• When the isolation level is not greater than RC, a transaction that inserts a new record will duplicate the existing primary key value. Add S-type formal record locks to clustered index records.

• When the isolation level is not less than RR, a transaction that inserts a new record will add an S-type next-key lock to the existing clustered index record with a duplicate primary key value.

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加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视频教程

The above is the detailed content of Let's talk about the deadlock caused by two INSERT statements. For more information, please follow other related articles on the PHP Chinese website!