MySQL의 교착상태 문제 해결 프로세스 전체 기록 공유

테스트 환경에서 사용자에게 쿠폰 동시 전송을 테스트할 때 교착 상태가 발생했습니다. 관련 정보를 검색하여 이 문제를 해결했기 때문에 이를 요약하려고 생각하여 다음 기사는 Mysql에 관한 것입니다. 교착상태 해결 과정 전체 기록입니다. 필요하신 분들은 참고하시면 도움이 되실 것 같습니다.

머리말

이전에 겪었던 데이터베이스 교착상태는 모두 일괄업데이트 시 잠금 순서 불일치로 인한 교착상태였는데, 위의 Zhou는 교착상태에 빠졌습니다. 그건 이해하기 어려웠어요. 저는 이번 기회를 통해 mysql 교착상태 지식과 일반적인 교착상태 시나리오를 다시 배웠습니다. 동료들과 여러 차례 조사하고 토론한 끝에 마침내 이 교착 상태 문제의 원인을 발견하고 많은 것을 얻었습니다. 우리는 백엔드 프로그래머이지만 DBA만큼 잠금 관련 소스 코드를 깊이 분석할 필요는 없지만 기본적인 교착 상태 문제 해결 방법을 숙지할 수 있다면 일상적인 개발에 큰 도움이 될 것입니다.

PS: 이 글에서는 교착 상태에 대한 기본 지식을 소개하지 않습니다. mysql의 잠금 원리에 대해서는 이 글의 참고 자료에 제공된 링크를 참조하세요.

교착상태의 원인

먼저 데이터베이스 및 테이블 상황을 소개합니다. 회사 내부의 실제 데이터를 포함하기 때문에 다음과 같은 사항을 시뮬레이션 하였으며 구체적인 분석에는 영향을 미치지 않습니다. .

mysql 데이터베이스 5.5 버전을 사용합니다. 트랜잭션 격리 수준은 기본 RR(Repeatable-Read)이며 innodb 엔진을 사용합니다. 테스트 테이블이 있다고 가정합니다.

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

테이블의 구조는 기본 키 ID와 또 다른 고유 인덱스 a를 포함하여 매우 간단합니다. 테이블의 데이터는 다음과 같습니다.

mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 rows in set (0.00 sec)

교착 상태를 일으키는 작업은 다음과 같습니다.

步骤	事务1	事务2
1		begin
2		delete from test where a = 2;
3	begin
4	delete from test where a = 2; （事务1卡住）
5	提示出现死锁：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	insert into test (id, a) values (10, 2);

然后我们可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

分析

阅读死锁日志

遇到死锁，第一步就是阅读死锁日志。死锁日志通常分为两部分，上半部分说明了事务1在等待什么锁：

170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志里我们可以看到事务1当前正在执行delete from test where a = 2，该条语句正在申请索引a的X锁，所以提示lock_mode X waiting。

然后日志的下半部分说明了事务2当前持有的锁以及等待的锁：

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志的HOLDS THE LOCKS(S)块中我们可以看到事务2持有索引a的X锁，并且是记录锁（Record Lock）。该锁是通过事务2在步骤2执行的delete语句申请的。由于是RR隔离模式下的基于唯一索引的等值查询（Where a = 2），所以会申请一个记录锁，而非next-key锁。

从日志的WAITING FOR THIS LOCK TO BE GRANTED块中我们可以看到事务2正在申请S锁，也就是共享锁。该锁是insert into test (id,a) values (10,2)语句申请的。insert语句在普通情况下是会申请排他锁，也就是X锁，但是这里出现了S锁。这是因为a字段是一个唯一索引，所以insert语句会在插入前进行一次duplicate key的检查，为了使这次检查成功，需要申请S锁防止其他事务对a字段进行修改。

那么为什么该S锁会失败呢？这是对同一个字段的锁的申请是需要排队的。S锁前面还有一个未申请成功的X锁，所以S锁必须等待，所以形成了循环等待，死锁出现了。

通过阅读死锁日志，我们可以清楚地知道两个事务形成了怎样的循环等待，再加以分析，就可以逆向推断出循环等待的成因，也就是死锁形成的原因。

死锁形成流程图

为了让大家更好地理解死锁形成的原因，我们再通过表格的形式阐述死锁形成的流程：

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务2占有a=2下的X锁，类型为记录锁。
3	begin
4	delete from test where a = 2; 事务1希望申请a=2下的X锁，但是由于事务2已经申请了一把X锁，两把X锁互斥，所以X锁申请进入锁请求队列。
5	出现死锁，事务1权重较小，所以被选择回滚（成为牺牲品）。	insert into test (id, a) values (10, 2); 由于a字段建立了唯一索引，所以需要申请S锁以便检查duplicate key，由于插入的a的值还是2，所以排在X锁后面。但是前面的X锁的申请只有在事务2commit或者rollback之后才能成功，此时形成了循环等待，死锁产生。

확장

교착 상태를 해결하는 과정에서 동료는 위의 시나리오에서 수동으로 재현할 수 없는 또 다른 종류의 교착 상태가 발생한다는 사실도 발견했습니다. 높은 동시성 시나리오에서만 재현이 가능합니다.

여기에는 교착 상태에 해당하는 로그가 게시되지 않습니다. 이전 교착 상태와 핵심적인 차이점은 트랜잭션 2가 기다리고 있는 잠금이 S 잠금에서 X 잠금으로 변경된다는 점, 즉 lock_mode X locks gap <a href="http://www.php.cn/java/java-Before.html" target="_blank">before</a> rec insert intention waiting입니다.

교착 상태 생성 과정을 자세히 설명하기 위해 여전히 테이블을 사용합니다.

단계

트랜잭션 1

거래 2

1

步骤	事务1	事务2
1		begin
2		delete from test where a = 2; 执行成功，事务2占有a=2下的X锁，类型为记录锁。
3	begin
4		【insert第1阶段】insert into test (id, a) values (10, 2); 事务2申请S锁进行duplicate key进行检查。检查成功。
5	delete from test where a = 2; 事务1希望申请a=2下的X锁，但是由于事务2已经申请了一把X锁，两把X锁互斥，所以X锁申请进入锁请求队列。
6	出现死锁，事务1权重较小，所以被选择回滚（成为牺牲品）。	【insert第2阶段】insert into test (id, a) values (10, 2); 事务2开始插入数据，S锁升级为X锁，类型为insert intention。同理，X锁进入队列排队，形成循环等待，死锁产生。

시작

2

a = 2 실행이 성공한 테스트에서 삭제, 트랜잭션 2가 a=2를 차지함 X 잠금, 유형은 레코드 잠금입니다.

3

시작

4 td>

[1단계 삽입] 테스트(id, a) 값(10, 2)에 삽입합니다. 트랜잭션 2는 중복 키 확인을 위해 S 잠금을 적용합니다. 확인에 성공했습니다.

5

a = 2인 테스트에서 삭제; 트랜잭션 1은 a=2에서 X 잠금을 적용하려고 하지만 트랜잭션 2는 이미 잠금 X에 적용된 경우 두 개의 X 잠금은 상호 배타적이므로 X 잠금 애플리케이션이 잠금 요청 대기열에 들어갑니다.

6

교착 상태가 발생하고 트랜잭션 1의 가중치가 작아서 선택됩니다. 롤백(피해자가 됨)).

[2단계 삽입]테스트(id, a) 값에 삽입 ​​(10, 2) 트랜잭션 2 시작데이터 삽입, S 잠금이 X 잠금으로 업그레이드되고 유형이 삽입 의도입니다. 같은 방식으로 X 잠금이 대기열에 들어가 대기 루프를 형성하고 교착 상태가 발생합니다.

요약교착 상태를 해결할 때 먼저 루프 대기를 기반으로 분석해야 합니다. 교착상태 로그 시나리오를 작성하고, 각 트랜잭션이 현재 실행 중인 SQL을 기반으로 잠금 유형과 순서를 분석하고, 역방향으로 루프 대기를 구성하는 방법을 유추함으로써 교착상태의 원인을 찾을 수 있다.

위 내용은 MySQL의 교착상태 문제 해결 프로세스 전체 기록 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!