MySQL 교착상태 문제 해결 방법(상세 예시)-MySQL 튜토리얼-php.cn

이 기사는 mysql에 대한 관련 지식을 제공합니다. 주로 일반적인 교착 상태 사례에 대한 분석 및 토론을 소개하고 가능한 한 교착 상태를 피하는 방법에 대한 몇 가지 제안을 제공하여 모든 사람에게 도움이 되기를 바랍니다.

MySQL 교착상태 문제 해결 방법(상세 예시)

1. 교착 상태란 무엇입니까

교착 상태는 동시 시스템에서 흔히 발생하는 문제이며 MySQL 데이터베이스의 동시 읽기 및 쓰기 요청 시나리오에서도 나타납니다. "교착 상태"는 두 개 이상의 트랜잭션이 서로가 이미 보유한 잠금을 해제할 때까지 기다리거나 일관되지 않은 잠금 시퀀스로 인해 루프에서 잠금 리소스를 기다리고 있을 때 발생합니다. 일반적인 오류 메시지는 잠금을 얻으려고 할 때 교착 상태가 발견되었습니다...입니다. Deadlock found when trying to get lock...。

举例来说 A 事务持有 X1 锁，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。

MySQL 교착상태 문제 해결 방법(상세 예시)

如上图，是右侧的四辆汽车资源请求产生了回路现象，即死循环，导致了死锁。

从死锁的定义来看，MySQL 出现死锁的几个要素为：

两个或者两个以上事务
每个事务都已经持有锁并且申请新的锁
锁资源同时只能被同一个事务持有或者不兼容
事务之间因为持有锁和申请锁导致彼此循环等待

2、InnoDB 锁类型

为了分析死锁，我们有必要对 InnoDB 的锁类型有一个了解。

MySQL 교착상태 문제 해결 방법(상세 예시)

MySQL InnoDB 引擎实现了标准的行级别锁：共享锁( S lock ) 和排他锁 ( X lock )

不同事务可以同时对同一行记录加 S 锁。

如果一个事务对某一行记录加 X 锁，其他事务就不能加 S 锁或者 X 锁，从而导致锁等待。

如果事务 T1 持有行 r 的 S 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理:

T2 请求 S 锁立即被允许，结果 T1 T2 都持有 r 行的 S 锁

T2 请求 X 锁不能被立即允许

如果 T1 持有 r 的 X 锁，那么 T2 请求 r 的 X、S 锁都不能被立即允许，T2 必须等待 T1 释放 X 锁才可以，因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示：

MySQL 교착상태 문제 해결 방법(상세 예시)

2.1、间隙锁( gap lock )

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值，如果对 4 加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:

如果索引列是唯一索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。

对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。

2.2、next-key lock

next-key lock 实际上就是行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)

在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读

예를 들어 트랜잭션 A는 X1 잠금을 보유하고 X2 잠금을 적용하고, 트랜잭션 B는 X2 잠금을 보유하고 X1 잠금을 적용합니다. 트랜잭션 A와 B는 잠금을 유지하고 상대방이 보유한 잠금을 적용한 후 루프를 기다리면서 교착 상태가 발생합니다.

위 MySQL 교착상태 문제 해결 방법(상세 예시)과 같이, 오른쪽 4개 입니다. 차량 자원 요구로 인해 루프 현상, 즉 무한 루프가 발생하여 교착 상태가 발생했습니다.

교착 상태 정의에서 MySQL의 교착 상태에 대한 몇 가지 요소는 다음과 같습니다.
두 개 이상의 트랜잭션
각 트랜잭션은 이미 잠금을 보유하고 있으며 새 잠금을 적용합니다

잠금 리소스는 보유만 가능합니다. 동시에 동일한 트랜잭션에 의해 또는 호환되지 않습니다

MySQL 교착상태 문제 해결 방법(상세 예시)

🎜잠금 보유 및 잠금 적용으로 인해 트랜잭션이 루프에서 서로를 기다립니다🎜🎜🎜🎜2. InnoDB 잠금 유형🎜🎜교착 상태를 분석하기 위해서는 InnoDB의 잠금 유형을 이해하는 데 필요합니다. 🎜🎜 MySQL 교착상태 문제 해결 방법(상세 예시)

🎜🎜MySQL InnoDB 엔진은 표준행 수준 잠금: 공유 잠금(S 잠금) 및 배타적 잠금(X 잠금)🎜🎜🎜🎜🎜 서로 다른 트랜잭션이 동시에 동일한 행 레코드에 S 잠금을 추가할 수 있습니다. 🎜🎜🎜🎜트랜잭션이 특정 레코드 행에 X 잠금을 추가하면 다른 트랜잭션은 S 잠금이나 X 잠금을 추가할 수 없으므로 잠금 대기가 발생합니다. 🎜🎜🎜🎜🎜트랜잭션 T1이 행 r의 S 잠금을 보유하고 있는 경우 다른 트랜잭션 T2가 r의 잠금을 요청하면 다음 처리가 수행됩니다. 🎜🎜🎜🎜🎜T2는 즉시 S 잠금을 요청하고 As가 허용됩니다. 결과적으로, T1과 T2 모두 r 행에 S 잠금이 설정되어 있습니다.🎜🎜🎜🎜T2의 X 잠금 요청은 즉시 허용될 수 없습니다.🎜🎜🎜🎜🎜T1이 T1을 보유하면 X 잠금만 해제할 수 있습니다. 잠금은 어떤 잠금과도 호환되지 않습니다. 공유 잠금과 단독 잠금의 호환성은 다음과 같습니다: 🎜🎜 MySQL 교착상태 문제 해결 방법(상세 예시)

🎜🎜2.1 갭락(gap lock) 🎜🎜갭락은 틈을 막아 삽입을 방지합니다. 인덱스 열에 2, 4, 8의 세 가지 값이 있다고 가정합니다. 4가 잠기면 두 간격(2,4)과 (4,8)도 동시에 잠기게 됩니다. 다른 트랜잭션은 이 두 간격 사이에 인덱스 값이 있는 레코드를 삽입할 수 없습니다. 그러나 간격 잠금에는 예외가 있습니다. 🎜🎜🎜🎜🎜인덱스 열이 고유 인덱스인 경우 이 레코드만 잠기고(행 잠금만 추가됨) 간격은 잠기지 않습니다. 🎜🎜🎜🎜조인트 인덱스이고 고유 인덱스인 경우 where 조건에 조인트 인덱스의 일부만 포함된 경우에도 간격 잠금이 추가됩니다. 🎜🎜🎜🎜🎜2.2, next-key lock🎜🎜next-key lock은 사실 이 레코드 앞의 row lock + gap lock 조합입니다. 인덱스 값 10, 11, 13 및 20이 있다고 가정하면 가능한 다음 키 잠금에는 다음이 포함됩니다. 🎜🎜🎜(negative infinity, 10], (10, 11], (11, 13], (13, 20], ( 20, 양의 무한대)🎜🎜🎜RR 격리 수준에서 InnoDB는 주로 phantom reading 문제를 방지하기 위해 다음 키 잠금을 사용합니다. 🎜🎜2.3, 의도 잠금(Intention lock)🎜🎜 InnoDB는 행 잠금과 테이블 잠금이 동시에 존재할 수 있도록 지원합니다. InnoDB는 의도 잠금이라는 추가 잠금 방법을 지원합니다. 의도 잠금은 두 가지 유형으로 구분됩니다. 🎜🎜🎜🎜🎜의도 공유 잠금(IS): 공유 잠금은 특정 행에 추가됩니다. 🎜의도적 배타적 잠금(IX): 트랜잭션은 테이블의 특정 행에 배타적 잠금을 추가하려고 합니다🎜🎜🎜🎜🎜InnoDB 스토리지 엔진은 행 수준 잠금을 지원하므로 의도적 잠금은 실제로 전체 요청을 제외한 모든 요청을 차단하지 않습니다. 테이블 수준 잠금과 행 수준 잠금의 호환성은 다음과 같습니다. 🎜🎜🎜🎜

2.4. 삽입 의도 잠금(Insert Intention lock)

삽입 의도 잠금은 레코드 행을 삽입하기 전에 설정되는 간격 잠금입니다. 이 잠금은 삽입 모드의 신호를 해제합니다. 즉, 여러 트랜잭션이 삽입 시 동일한 인덱스 간격에 있습니다. , 간격의 같은 위치에 삽입되지 않으면 서로 기다릴 필요가 없습니다. 열에 인덱스 값 2와 6이 있다고 가정합니다. 두 트랜잭션의 삽입 위치가 서로 다른 한(예: 트랜잭션 A는 3을 삽입하고 트랜잭션 B는 4를 삽입) 동시에 삽입할 수 있습니다.

2.5. 잠금 모드 호환성 매트릭스

수평은 잠금이고 수직은 요청된 잠금입니다.

MySQL 교착상태 문제 해결 방법(상세 예시)

3. 교착 상태 로그를 읽어보세요.

특정 사례를 분석하기 전에 먼저 수행 방법을 이해해 보겠습니다. 교착 상태 로그를 읽고 교착 상태 로그의 정보를 최대한 활용하여 교착 상태 문제를 해결하는 데 도움을 주세요.

다음 테스트 사례의 데이터베이스 시나리오는 다음과 같습니다. MySQL 5.7 트랜잭션 격리 수준은 RR입니다MySQL 5.7 事务隔离级别为 RR

表结构和数据如下:

MySQL 교착상태 문제 해결 방법(상세 예시)

测试用例如下:

MySQL 교착상태 문제 해결 방법(상세 예시)

通过执行show engine innodb status 可以查看到最近一次死锁的日志。

3.1、日志分析如下:

1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

事务号为2322，活跃 6秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:

MySQL 교착상태 문제 해결 방법(상세 예시)

mysql tables in use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁，对于 DML 语句为 LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

로그인 후 복사

LOCK WAIT 表示正在等待锁，2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)

1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

로그인 후 복사

MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )

delete from student where stuno=5 表示事务1正在执行的 sql，比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的，通常显示当前正在等待锁的 sql。

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

로그인 후 복사

RECORD LOCKS 表示记录锁，此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁，本案例中其实是 Next-Key Lock 。

事务2的 log 和上面分析类似:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

로그인 후 복사

显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X

LOCK_gap，不过我们从日志里面看不到事务2执行的 delete from student where stuno=5;

这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )

4、经典案例分析

4.1、事务并发 insert 唯一键冲突

表结构和数据如下所示:

MySQL 교착상태 문제 해결 방법(상세 예시)

测试用例如下:

MySQL 교착상태 문제 해결 방법(상세 예시)

日志分析如下:

事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功，持有 a=10 的 排他行锁( Xlocks rec but no gap )
事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁
테이블 구조와 데이터는 다음과 같습니다. 🎜🎜🎜🎜테스트 예시는 다음과 같습니다.🎜🎜🎜🎜 show Engine innodb status를 실행하면 최근 교착 상태의 로그를 볼 수 있습니다. 🎜🎜3.1 로그 분석은 다음과 같습니다. 🎜🎜1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6초 인덱스 읽기 시작🎜🎜 트랜잭션 번호는 2322, 6초 동안 활성, 인덱스 읽기 시작 인덱스 읽기 데이터를 기준으로 트랜잭션 상태를 나타냅니다. 다른 일반적인 상태는 다음과 같습니다. 🎜🎜🎜🎜 사용 중인 mysql 테이블 1은 현재 트랜잭션이 테이블을 사용함을 나타냅니다. 🎜🎜locked 1은 테이블에 테이블 잠금이 있음을 의미하고, DML 문의 경우 LOCK_IX🎜rrreee🎜LOCK WAIT는 잠금을 기다리는 것을 의미하고, 2 잠금 struct(s) trx->trx_locks 잠금 목록의 길이가 2임을 나타냅니다. 각 연결 목록 노드는 테이블 잠금, 레코드 잠금 및 자동 증분 잠금을 포함하여 트랜잭션이 보유한 잠금 구조를 나타냅니다. 이 사용 사례에서 2locks는 IX 잠금을 나타내고 lock_mode X(다음 키 잠금)🎜🎜1 행 잠금은 현재 트랜잭션이 보유한 행 레코드 잠금/갭 잠금 수를 나타냅니다. 🎜rrreee🎜MySQL 스레드 ID 37은 트랜잭션을 실행하는 스레드 ID가 37임을 의미합니다(예: show processlist, 표시된 ID) 🎜🎜Stuno=5인 학생에서 삭제 1. SQL 실행 시 불편한 점은 show Engine innodb status가 전체 SQL을 볼 수 없다는 점입니다. 일반적으로 현재 잠금을 대기 중인 SQL이 표시됩니다. 🎜rrreee🎜RECORD LOCKS는 레코드 잠금을 나타냅니다. 이 내용은 트랜잭션 1이 학생 테이블에서 idx_stuno의 X 잠금을 기다리고 있음을 나타냅니다. 🎜🎜트랜잭션 2의 로그는 위 분석과 유사합니다. 🎜rrreee🎜트랜잭션 2의 학생(stuno,score) 값(2,10)에 삽입하면 a=5 잠금 모드 X🎜
🎜 LOCK_gap이 있음을 보여줍니다. , 그러나 트랜잭션 2에 의해 실행된 stuno=5;🎜
🎜 학생의 삭제 내용은 로그에서 볼 수 없습니다. 이는 DBA가 로그만으로 교착 상태 문제를 분석하기 어려운 근본적인 이유이기도 합니다. . 🎜🎜3.***** (2) 이 잠금이 부여되기를 기다리는 중:🎜🎜RECORD LOCKS 공간 ID 11 페이지 번호 5 n 비트 72 테이블 cw****.****student trx id의 인덱스 idx_stuno 2321 lock_mode 삽입 고유 키 충돌🎜🎜테이블 구조 및 데이터는 다음과 같습니다.🎜🎜🎜🎜🎜🎜 테스트 예시는 다음과 같습니다: 🎜🎜🎜🎜 로그 분석은 다음과 같습니다:🎜
1. 🎜t7(id,a) 값에 트랜잭션 T2 삽입 (26,10) 문 삽입이 성공하고 a=10 배타적 행 잠금(Xlocks rec 하지만 간격 없음)🎜 li>
2. 🎜트랜잭션 T1은 t7(id,a) 값 (30,10)에 삽입합니다. T2의 첫 번째 삽입이 이미 a=10 레코드를 삽입했기 때문입니다. T1 삽입 a=10은 고유 키 충돌을 일으키며, 충돌의 고유 인덱스와 S Next-key 잠금을 적용해야 합니다(즉, 잠금 모드 S 대기). 이는 갭 잠금입니다. > (,10], (10,20] 간격 영역 사이에 잠금이 적용됩니다. 🎜
3. 트랜잭션 T2가 t7(id,a) 값에 삽입 (40, 9) 이 문에 의해 삽입된 a=9 값은 적용된 gap lock 4-10 사이입니다. 트랜잭션 T1에 의해 필요하므로 트랜잭션 T2의 두 번째 insert 문은 트랜잭션 T1의 S-Next-key Lock이 해제될 때까지 기다려야 하며 lock_mode X는 기록 삽입 의도 대기 전에 간격을 잠급니다. 로그에 표시됩니다. gap 锁4-10之间，故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。
4.2、先 update 再 insert 的并发死锁问题

表结构如下，无数据:

测试用例如下:

死锁分析:
可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

5、如何尽可能避免死锁
1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
2. 调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。
3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解
테이블 구조는 다음과 같습니다. 데이터가 없습니다.

테스트 예시는 다음과 같습니다

🎜🎜 교착 상태 분석:
예, 트랜잭션 업데이트에서 존재하지 않는 두 개의 레코드를 확인하여 gap lock(gap lock)을 차례로 얻었습니다. gap lock은 호환되므로 업데이트 프로세스 중에 차단되지 않습니다. . 둘 다 간격 잠금을 유지한 다음 의도 잠금을 삽입하기 위해 경쟁합니다. Gap 잠금을 보유하고 있는 다른 세션이 있는 경우 현재 세션에서는 삽입 의도 잠금을 적용할 수 없어 교착 상태가 발생합니다. 🎜

5. 교착상태를 최대한 피하는 방법