두 개의 INSERT 문으로 인해 발생하는 교착 상태에 대해 이야기해 보겠습니다.

이 글은 MySQL의 교착상태와 관련된 문제를 주로 소개하며, 두 개의 동일한 INSERT 문으로 인해 발생하는 교착상태에 대한 관련 지식을 소개합니다.

두 개의 동일한 INSERT 문이 실제로 교착 상태를 일으켰습니다. 이것이 인간 본성의 왜곡인가요, 아니면 도덕성 상실인가요? 한숨을 쉬지 않을 수 없습니다. 젠장! 이것도 교착상태로 이어질 수 있고, 그러다가 눈물을 글썽이며 무기력하게 비즈니스 코드가 바뀌게 된다.

자, 왜 두 개의 동일한 INSERT 문이 교착 상태를 일으킬 수 있는지 자세히 분석하기 전에 먼저 몇 가지 기본 지식을 소개하겠습니다.

환경을 준비하세요

스토리의 원활한 전개를 위해 수없이 사용되었던 새로운 Hero 테이블을 만듭니다:

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;

그런 다음 이 테이블에 몇 가지 레코드를 삽입합니다:

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

이제 Hero 테이블에는 두 개의 인덱스가 있습니다. ( 고유 보조 인덱스, 클러스터형 인덱스) 도식은 다음과 같습니다.

INSERT 문을 잠그는 방법

"MySQL 실행 방법: 기본부터 MySQL 이해"를 읽어본 친구는 알아야 할, INSERT 문은 일반 실행 중에 잠금 구조를 생성하지 않습니다. 이는 클러스터형 인덱스 레코드와 함께 제공되는 숨겨진 열을 암시적 잠금으로 사용하여 레코드를 보호합니다.

그러나 일부 특별한 시나리오에서는 INSERT 문이 여전히 잠금 구조를 생성합니다. 나열해 보겠습니다.

1. 삽입할 다음 레코드가 다른 트랜잭션에 의해 간격 잠금에 추가된 경우

Every. time 새로운 레코드를 삽입할 때 다음 삽입할 레코드에 gap lock이 추가되었는지 확인해야 하며, gap lock이 추가된 경우 INSERT 문을 차단하고 삽입 의도 잠금을 생성해야 한다.

예를 들어 Hero 테이블의 경우 트랜잭션 T1은 REPEATABLE READ(향후에는 RR, READ COMMITTED도 향후 RC라고도 함) 격리 수준에서 실행되고 다음 명령문이 실행됩니다.

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

이 문은 기본 키 값이 1, 3, 8인 세 개의 레코드가 모두 X 유형의 다음 키 잠금으로 추가됩니다. 믿을 수 없다면 SHOW ENGINE INNODB STATUS 문을 사용해 보겠습니다. 잠금 상황을 보세요. 그림에서 화살표가 가리키는 레코드는 숫자 값이 8인 레코드입니다. 레코드:

팁:

SELECT, DELETE, UPDATE 문을 잠그는 방법은 이미 나와 있습니다. 이전 기사에서 분석했으므로 여기서는 자세히 다루지 않겠습니다.

이때, 트랜잭션 T2는 기본 키 값이 4인 클러스터형 인덱스 레코드를 삽입하려고 합니다. 레코드를 삽입하기 전에 T2는 먼저 페이지에서 기본 키 값이 4인 클러스터형 인덱스 레코드의 위치를 ​​찾아야 합니다. 키 값이 4인 다음 레코드의 주요 기본 키 값은 8이고, 기본 키 값이 8인 클러스터형 인덱스 레코드에 갭 잠금이 추가되었음을 확인합니다(다음 키 잠금에는 형식적인 레코드 잠금이 포함되며 gap 잠금) 이후 트랜잭션 1 차단 상태로 진입하여 삽입 의도 잠금 유형의 잠금 구조를 생성해야 합니다.

트랜잭션 T2에서 INSERT 문을 실행하여 확인합니다.

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

이제 T2는 차단 상태에 진입합니다. SHOW ENGINE INNODB STATUS를 사용하여 잠금 상황을 확인합니다.

기본이 되는 것을 볼 수 있습니다. T2의 키 값은 8 입니다. 클러스터형 인덱스 레코드에 삽입 의도 잠금이 추가되고(즉, lock_mode X는 화살표가 가리키는 Rec 삽입 의도 전 간격을 잠급니다) 대기 상태입니다.

확인 후 코드에서 어떻게 구현되는지 살펴보겠습니다.

lock_rec_insert_check_and_lock 함수는 다른 트랜잭션이 이 INSERT 삽입을 방지하는지 확인하는 데 사용됩니다. 그렇다면 이 트랜잭션은 차단됩니다. 또 다른 트랜잭션은 삽입 의도 잠금을 생성하기 위해 Gap 잠금 레코드를 추가합니다. 구체적인 프로세스는 다음과 같습니다.

팁:

lock_rec_other_has_constricting 함수를 사용하여 이번에 획득할 잠금과 기존 잠금을 ​​감지합니다. 기록 충돌이 있는 경우 관심 있는 학생들이 살펴볼 수 있습니다.

2. 중복 키가 발생한 경우

새 레코드 삽입 시 페이지에 있는 기존 레코드의 기본 키 또는 고유 보조 인덱스 열이 기본 키 또는 고유 보조 인덱스 열과 다른 것으로 발견된 경우 삽입할 레코드의 인덱스 열 인덱스 열 값은 동일합니다(단, 여러 레코드의 고유한 보조 인덱스 열 값이 동시에 NULL일 수 있습니다. 여기서는 이러한 상황을 고려하지 않습니다). 이번에는 새 레코드를 삽입하는 트랜잭션이 페이지 레코드 잠금에 이미 존재하는 것과 동일한 키 값을 얻습니다.

기본 키 값이 중복된 경우:

• 격리 수준이 RC보다 크지 않으면 새 레코드를 삽입하는 트랜잭션은 기본 중복이 포함된 기존 클러스터형 인덱스 레코드에 S자 형식 레코드 잠금을 추가합니다. 핵심 가치.

• 격리 수준이 RR 이상인 경우 새 레코드를 삽입하는 트랜잭션은 중복 기본 키 값을 사용하여 기존 클러스터형 인덱스 레코드에 S 유형 다음 키 잠금을 추가합니다.

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

위 내용은 두 개의 INSERT 문으로 인해 발생하는 교착 상태에 대해 이야기해 보겠습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!