mysql 교착상태란 무엇인가

MySQL에서 교착 상태란 둘 이상의 서로 다른 프로세스 또는 스레드 간의 리소스 경쟁으로 인해 서로 기다리는 현상을 의미합니다. 스레드 간 통신으로 인해 각 스레드가 중단되고 서로 기다려라. 외력이 없으면 결국 시스템 전체가 무너진다. MySQL의 교착 상태에 필요한 조건은 다음과 같습니다. 1. 리소스 독점 조건 2. 요청 및 보유 조건 3. 상호 획득 잠금 조건

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

1. 교착상태란 무엇인가요?

Deadlock은 둘 이상의 서로 다른 프로세스 또는 스레드 간의 리소스 경쟁 또는 프로세스 간(또는 스레드) 통신으로 인해 각 스레드가 중단되고 대기하는 현상을 말합니다. 외부의 힘이 없다면 결국 전체 시스템이 붕괴될 것입니다.

이때, 시스템이 교착상태에 빠졌다고 합니다.

2.Mysql에서 교착 상태가 발생하는 조건

• 리소스 배타 조건

은 동일한 리소스, 즉 일정 시간 내에 하나의 리소스를 두고 여러 트랜잭션이 경쟁하는 경우의 상호 배타성을 말합니다. 트랜잭션에 의해 점유된 경우에만 사용되며 배타적 리소스(예: 행 잠금)라고도 할 수 있습니다.

• 요청 및 보유 조건

은 A 잠금이 트랜잭션 a에서 획득되었지만 새로운 잠금 B 요청이 이루어졌고 잠금 B가 이미 다른 트랜잭션 b에 의해 점유되었음을 의미합니다. 시간이 지나면 트랜잭션 a는 차단되지만 획득한 잠금 A는 유지됩니다.

• 박탈 조건 없음

은 트랜잭션 a에서 획득한 잠금 A를 나타냅니다. 제출되기 전에는 박탈할 수 없으며, 사용 후에만 커밋되고 자체적으로 해제될 수 있습니다.

• 상호 잠금 획득 조건

은 교착 상태가 발생하면 상호 잠금 획득 프로세스가 있어야 함을 의미합니다. 즉, 보유 잠금 A가 잠금 B를 획득하는 트랜잭션과 동시에 트랜잭션이 잠금 B b도 잠금 A를 획득하고 있으며 이는 결국 상호 획득으로 이어지며 각 트랜잭션이 차단됩니다.

3. Mysql 클래식 교착 사례

A 계좌가 B 계좌로 50위안을 이체하면 B 계좌도 A 계좌로 30위안을 이체하는 경우가 있다고 가정해 보겠습니다. 교착상태 상황은 어떻습니까?

3.1 테이블 생성문

CREATE TABLE `account` (
  `id` int(11) NOT NULL COMMENT '主键',
  `user_id` varchar(56) NOT NULL COMMENT '用户id',
  `balance` float(10,2) DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='账户余额表';

3.2 관련 데이터 초기화

INSERT INTO `test`.`account` (`id`, `user_id`, `balance`) VALUES (1, 'A', 80.00);
INSERT INTO `test`.`account` (`id`, `user_id`, `balance`) VALUES (2, 'B', 60.00);

3.3 정상적인 전송 과정

교착상태 문제에 대해 이야기하기 전에 먼저 살펴보겠습니다. 먼저 일반적인 전송 프로세스를 참조하세요.
일반적인 상황에서는 사용자 A가 사용자 B에게 50위안을 전송하는데, 이는 한 번의 거래로 완료될 수 있습니다. 사용자 A의 잔액과 사용자 B의 잔액을 먼저 확보해야 합니다. 이 두 데이터는 나중에 수정해야 하기 때문에 쓰기 잠금이 필요합니다. (업데이트의 경우) 다른 트랜잭션 변경으로 인해 변경 사항이 손실되고 더티 데이터가 발생하는 것을 방지하기 위해 이를 잠급니다.
관련 SQL은 다음과 같습니다.

트랜잭션을 열기 전에 mysql의 자동 제출을 꺼야 합니다

set autocommit=0;
# 查看事务自动提交状态状态

show VARIABLES like 'autocommit';![여기에 이미지 설명 삽입](https:// img-blog.csdnimg .cn/a486a4ed5c9d4240bd115ac7b3ce5a3mysql 교착상태란 무엇인가?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_show VARIABLES like 'autocommit';![在这里插入图片描述](https://img-blog.csdnimg.cn/a486a4ed5c9d4240bd115ac7b3ce5a3mysql 교착상태란 무엇인가?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_

Q1NETiBA6ZqQIOmjjg==,size_20,color_FFFFFF,t_70,g_se,x_16)

Q1NETiBA6ZqQIOmjjg==,size_20,color_FFFFFF,t _70,g_se,x_16)

# 转账sql
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：80
SELECT user_id,@A_balance:=balance from account where user_id = 'A' for UPDATE;
# 获取B 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'B' for UPDATE;

# 修改A 的余额
UPDATE account set balance = @A_balance - 50 where user_id = 'A';
# 修改B 的余额
UPDATE account set balance = @B_balance + 50 where user_id = 'B';
COMMIT;

실행 결과 :

데이터 업데이트가 정상적으로 이루어지고 있는 것을 확인할 수 있습니다

3.4 Deadlock 전송 과정

초기화된 잔액은

높은 동시성 사용자 A가 사용자 B에게 50위안을 이체하고, 사용자 B도 사용자 A에게 30위안을 이체하는 시나리오가 있습니다.

그런 다음 Java 프로그램 작업의 프로세스와 타임라인은 다음과 같습니다.

1. 사용자 A는 사용자 B에게 50위안을 이체합니다. 그는 SQL을 실행하고 A의 잔액을 얻고 A의 기사를 잠그기 위해 프로그램에서 트랜잭션 1을 열어야 합니다. 데이터.

# 事务1
set autocommit=0;
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：80
SELECT user_id,@A_balance:=balance from account where user_id = 'A' for UPDATE;

2. 사용자 B는 사용자 A에게 30위안을 전송합니다. 그는 SQL을 실행하고 B의 잔액을 얻고 B의 데이터를 잠그려면 프로그램에서 트랜잭션 2를 열어야 합니다.

# 事务2
set autocommit=0;
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：60
SELECT user_id,@A_balance:=balance from account where user_id = 'B' for UPDATE;

3. 트랜잭션 1

# 获取B 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'B' for UPDATE;

# 修改A 的余额
UPDATE account set balance = @A_balance - 50 where user_id = 'A';
# 修改B 的余额
UPDATE account set balance = @B_balance + 50 where user_id = 'B';
COMMIT;

🎜의 나머지 SQL을 실행합니다.

可以看到，在事务1中获取B数据的写锁时出现了超时情况。为什么会这样呢？主要是因为我们在步骤2的时候已经在事务2中获取到B数据的写锁了，那么在事务2提交或回滚前事务1永远都拿不到B数据的写锁。

4.在事务2中执行剩下的sql

# 获取A 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'A' for UPDATE;

# 修改B 的余额
UPDATE account set balance = @A_balance - 30 where user_id = 'B';
# 修改A 的余额
UPDATE account set balance = @B_balance + 30 where user_id = 'A';
COMMIT;

同理可得，在事务2中获取A数据的写锁时也出现了超时情况。因为步骤1的时候已经在事务1中获取到A数据的写锁了，那么在事务1提交或回滚前事务2永远都拿不到A数据的写锁。

5. 为什么会出现这种情况呢？

主要是因为事务1和事务2存在相互等待获取锁的过程，导致两个事务都挂起阻塞，最终抛出获取锁超时的异常。

3.5 死锁导致的问题

众所周知，数据库的连接资源是很珍贵的，如果一个连接因为事务阻塞长时间不释放，那么后面新的请求要执行的sql也会排队等待，越积越多，最终会拖垮整个应用。一旦你的应用部署在微服务体系中而又没有做熔断处理，由于整个链路被阻断，那么就会引发雪崩效应，导致很严重的生产事故。

4、如何解决死锁问题？

要想解决死锁问题，我们可以从死锁的四个必要条件入手。
由于资源独占条件和不剥夺条件是锁本质的功能体现，无法修改，所以咱们从另外两个条件尝试去解决。

4.1 打破请求和保持条件

根据上面定义可知，出现这个情况是因为事务1和事务2同时去竞争锁A和锁B，那么我们是否可以保证锁A和锁B一次只能被一个事务竞争和持有呢？
答案是肯定可以的。下面咱们通过伪代码来看看：

/**
* 事务1入参(A, B)
* 事务2入参(B, A)
**/
public void transferAccounts(String userFrom, String userTo) {
     // 获取分布式锁
     Lock lock = Redisson.getLock();
     // 开启事务
     JDBC.excute("START TRANSACTION;");
     // 执行转账sql
     JDBC.excute("# 获取A 的余额并存入A_balance变量：80\n" +
             "SELECT user_id,@A_balance:=balance from account where user_id = '" + userFrom + "' for UPDATE;\n" +
             "# 获取B 的余额并存入B_balance变量:60\n" +
             "SELECT user_id,@B_balance:=balance from account where user_id = '" + userTo + "' for UPDATE;\n" +
             "\n" +
             "# 修改A 的余额\n" +
             "UPDATE account set balance = @A_balance - 50 where user_id = '" + userFrom + "';\n" +
             "# 修改B 的余额\n" +
             "UPDATE account set balance = @B_balance + 50 where user_id = '" + userTo + "';\n");
     // 提交事务
     JDBC.excute("COMMIT;");
     // 释放锁
     lock.unLock();
}

上面的伪代码显而易见可以解决死锁问题，因为所有的事务都是通过分布式锁来串行执行的。

那么这样就真的万事大吉了吗？

在小流量情况下看起来是没问题的，但是在高并发场景下这里将成为整个服务的性能瓶颈，因为即使你部署了再多的机器，但由于分布式锁的原因，你的业务也只能串行进行，服务性能并不因为集群部署而提高并发量，完全无法满足分布式业务下快、准、稳的要求，所以咱们不妨换种方式来看看怎么解决死锁问题。

4.2 打破相互获取锁条件（推荐）

要打破这个条件其实也很简单，那就是事务再获取锁的过程中保证顺序获取即可，也就是锁A始终在锁B之前获取。
我们来看看之前的伪代码怎么优化？

/**
* 事务1入参(A, B)
* 事务2入参(B, A)
**/
public void transferAccounts(String userFrom, String userTo) {
     // 对用户A和B进行排序，让userFrom始终为用户A，userTo始终为用户B
     if (userFrom.hashCode() > userTo.hashCode()) {
         String tmp = userFrom;
         userFrom = userTo;
         userTo = tmp;
     }
     // 开启事务
     JDBC.excute("START TRANSACTION;");
     // 执行转账sql
     JDBC.excute("# 获取A 的余额并存入A_balance变量：80\n" +
             "SELECT user_id,@A_balance:=balance from account where user_id = '" + userFrom + "' for UPDATE;\n" +
             "# 获取B 的余额并存入B_balance变量:60\n" +
             "SELECT user_id,@B_balance:=balance from account where user_id = '" + userTo + "' for UPDATE;\n" +
             "\n" +
             "# 修改A 的余额\n" +
             "UPDATE account set balance = @A_balance - 50 where user_id = '" + userFrom + "';\n" +
             "# 修改B 的余额\n" +
             "UPDATE account set balance = @B_balance + 50 where user_id = '" + userTo + "';\n");
     // 提交事务
     JDBC.excute("COMMIT;");
 }

假设事务1的入参为(A, B)，事务2入参为(B, A)，由于我们对两个用户参数进行了排序，所以在事务1中需要先获取锁A在获取锁B，事务2也是一样要先获取锁A在获取锁B，两个事务都是顺序获取锁，所以也就打破了相互获取锁的条件，最终完美解决死锁问题。

5、 如何预防死锁

阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：

1.尽量避免并发的执行涉及到修改数据的语句。
2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。
3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。
4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。
5.使用尽可能低的隔离级别。

6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。
7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。

死锁的概念：

如果一组进程中的每一个进程都在等待仅由该组进程中的其他进程才能引发的事件，那么改组进程是死锁的。

死锁的常见表现：

死锁不仅会发生多个进程中，也会发生在一个进程中。
（1）多进程死锁：有进程A，进程B，进程A拥有资源1，需要请求正在被进程B占有的资源2。而进程B拥有资源2，请求正在被进程A战友的资源1。两个进程都在等待对方释放资源后请求该资源，而相互僵持，陷入死锁。
（2）单进程死锁：进程A拥有资源1，而它又在请求资源1，而它所请求的资源1必须等待该资源使用完毕得到释放后才可被请求。这样，就陷入了自己的死锁。

产生死锁的原因：

（1）进程推进顺序不当造成死锁。
（2）竞争不可抢占性资源引起死锁。
（3）竞争可消耗性资源引起死锁。

死锁的四个必要条件（四个条件四者不可缺一）：

（1）互斥条件。某段时间内，一个资源一次只能被一个进程访问。
（2）请求和保持条件。进程A已经拥有至少一个资源，此时又去申请其他资源，而该资源又正在被进程使用，此时请求进程阻塞，但对自己已经获得的资源保持不放。
（3）不可抢占资源。进程已获得的资源在未使用完不能被抢占，只能在自己使用完时由自己释放。
（4）循环等待序列。存在一个循环等待序列P0P1P2……Pn，P0请求正在被进程P1占有的资源，P1请求正在被P2占有的资源……Pn正在请求被进程P0占有的资源。

解除死锁的两种方法：

（1）终止（或撤销）进程。终止（或撤销）系统中的一个或多个死锁进程，直至打破循环环路，使系统从死锁状态中解除出来。
（2）抢占资源。从一个或多个进程中抢占足够数量的资源，分配给死锁进程，以打破死锁状态。

6、死锁场景

本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：

行锁导致死锁
gap lock/next keys lock导致死锁
index merge 导致死锁
唯一索引冲突导致死锁

注：以下场景隔离级别均为默认的Repeatable Read；

1）行锁导致死锁

死锁原因详解：

1.两个事务执行过程时间上有交集，并且过程发生在两者提交之前
2.事务1更新uid=1的记录，事务2更新uid=2的记录，在RR级别，由于uid是唯一索引，因此两个事务将分别持有uid=1和2所在行的独占锁
3.事务1执行到第二条更新语句时，发现uid=2的行被锁住，进入阻塞等待锁释放；
4.事务2执行到第二条语句时发现uid=1的行被锁，同样进入阻塞
5.两个事务互相等待，死锁产生。

相应业务案例和解决方案：
该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示lock_mode X locks rec but not gap waiting（即行锁而非间隙锁），解决方案：

1.避免循环更新，优化为一条where锁定要更新的记录批量更新
2.如果非要循环更新，尝试取消事务（能接受的话），即每一条更新为一个独立的事务

2）gap lock/next keys lock导致死锁

死锁原因分析：

1.事务1执行delete age = 27，务2执行delete age = 31，在RR级别，操作条件不是唯一索引时，行锁会升级为next keys
lock（可以理解为间隙锁），因此事务1锁住了25到27和27到29的区间，事务2锁住了29到31的区间
2.事务1执行insert age = 30，等待事务2释放锁
3.事务2执行insert age = 28，等待事务1释放锁
4.死锁产生，死锁日志显示lock_mode X locks gap before rec insert intention waiting

解决方案：

1.降低事务隔离级别到Read Committed，该隔离级别下间隙锁降级为行锁，可以减少死锁发生的概率
2.避免这种场景- -

3）index merge导致死锁

t_user结构改造为：

死锁分析：

1.在符合场景前提的情况下（即表数据量较大，index_merge未关闭），通过explain分析update t_user where zone_id = 1 and uid = 1可以发现type是index_merge，即会用到zone_id和uid两个索引
2.上锁的过程为：

事务1：
① 锁住zone_id=1对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=1对应的主键索引行锁id = [1,2]
③ 锁住uid=1对应的间隙锁: uid in (1, 2)
④ 锁住uid=1对应的主键索引行锁: id = [1, 3]

事务2：
① 锁住zone_id=2对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=2对应的主键索引行锁id = [3,4]
③ 锁住uid=2对应的间隙锁: uid in (1,2)
④ 锁住uid=2对应的主键索引行锁: id = [2, 4]

1、如果两个事务上锁的顺序相反，则有一定的概率出现死锁。另外，index_merge的形式锁住了很多不符合条件的行，浪费了资源。一般死锁日志打印的信息为：

lock_mode
 X locks rec but not gap waiting Record lock

解决方案：创建联合索引，使执行计划只会用到一个索引。

注：

update table set name = “wea” where col_1 = 1 or col_2 = 2 ;

col_1和col_2为联合索引，遵循最左原则col_1会走索引，但col_2会对整个索引进行扫描，此时会对整个索引加锁。

【相关推荐：mysql视频教程】

위 내용은 mysql 교착상태란 무엇인가의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!