mysqlデッドロックとは何ですか

mysql では、デッドロックとは、2 つ以上の異なるプロセスまたはスレッド間でのリソースの競合、または共通のリソースまたはプロセスの競合により、相互に待機する現象を指します (スレッド間) 間の通信により、各スレッドが発生します外力がなければ、最終的にはシステム全体がクラッシュしてしまいます。 MySQL におけるデッドロックの必要条件: 1. リソースの排他条件、2. リクエスト アンド ホールド条件、3. 非剥奪条件、4. 相互獲得ロック条件。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

1. デッドロックとは何ですか?

デッドロックとは、2 つ以上の異なるプロセスまたはスレッドでのリソースの競合、共通リソースの競合、またはプロセス (またはスレッド間の通信) が原因で互いに待機する現象を指します。 ) により、各スレッドがハングして相互に待機するため、外部からの力がなければ、最終的にはシステム全体がクラッシュします。

このとき、システムがデッドロック状態にある、またはシステムがデッドロックに陥っているといい、このように常にお互いを待ち続けているプロセスをデッドロックプロセスと呼びます。

2. Mysql でのデッドロックの必要条件

• ##リソース排他条件

# は、同じリソースをめぐって競合する場合の複数のトランザクションの相互排他性を指します。つまり、リソースは一定期間内に 1 つのトランザクションによってのみ占有されます。これは排他的リソース (行ロックなど) とも呼ばれます。

• リクエスト アンド ホールド条件

ロック A はトランザクション a で取得されましたが、新しいロックが取得されたことを意味しますB が要求され、ロック B は別のトランザクション b によってすでに占有されています。このとき、トランザクション a はブロックされますが、取得したロック 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 元を送金します。これは 1 回の取引で完了します。最初にユーザー A の残高とユーザー B の残高を取得する必要があります。これら 2 つのデータは後で変更する必要があるため、書き込みロックが必要です (UPDATE の場合) 他のトランザクションの変更によって変更が失われてデータがダーティになるのを防ぐためにロックします。 関連する 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_

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

<pre class="brush:php;toolbar:false"># 转账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;</pre>実行後の結果:

データ更新は正常です

#3.4 デッドロック転送プロセス

初期化された残高は次のとおりです:

このシナリオが高い同時実行下で存在すると仮定すると、ユーザー A がユーザー B に 50 元を送金すると、ユーザー B もユーザー A に 30 元を送金します。

Java プログラム操作のプロセスとタイムラインは次のとおりです:

1. ユーザー A がユーザー B に 50 元を送金し、プログラムでトランザクション 1 を開く必要があります。 SQLを実行し、Aの残高を取得し、Aのデータをロックします。

# 事务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 元を送金します。彼はプログラムでトランザクション 2 を開いて SQL を実行し、B の残高を取得し、B のデータをロックする必要があります。

# 事务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 中国語 Web サイトの他の関連記事を参照してください。