Mysql でデッドロックのトラブルシューティング プロセスの完全な記録を共有する

テスト環境でユーザーにカードとクーポンを同時に送信するテストを行った際にデッドロックが発生しました。関連情報を検索して解決したので、以下の記事では主にMysqlデッドロックのトラブルシューティング手順について説明します。必要に応じて、完全な記録を参照していただければ幸いです。

まえがき

私がこれまでに遭遇したデータベースのデッドロックは、すべてバッチ更新中のロック順序の不一致によって引き起こされたデッドロックでしたが、先週、理解するのが難しいデッドロックに遭遇しました。この機会に、mysql のデッドロックの知識と一般的なデッドロックのシナリオを学び直しました。調査を重ね、同僚と議論した結果、最終的にこのデッドロック問題の原因を突き止め、多くのことを得ることができました。私たちはバックエンドプログラマーなので、DBA ほどロック関連のソースコードを深く解析する必要はありませんが、基本的なデッドロックのトラブルシューティング方法をマスターできれば、日々の開発に大きなメリットをもたらします。

追記: この記事ではデッドロックの基礎知識は紹介しません。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)

デッドロックが発生する操作は次のとおりです:

5 デッドロックのプロンプト: エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。 、a) 値 (10, 2);

Steps

Transaction 1

Transaction 2

1

始まります

2

a = 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) に変更されることです。

デッドロック生成のプロセスを詳しく説明するために、引き続き表を使用します:

5delete from test where a = 2; トランザクション 1 は a=2 で X ロックを適用しようとしていますが、トランザクション 2 はすでに X ロックを適用しているため、2 つの X ロックは相互に排他的です。 X ロック アプリケーション ロック要求キューに入ります。 6 デッドロックが発生し、トランザクション 1 の重みが小さいため、ロールバックされる (犠牲者になる) ことが選択されました。 【挿入フェーズ 2】insert into test (id, a) names (10, 2); トランザクション 2 が

ステップ	トランザクション1	トランザクション2
1		begin
2		delete from test where a = 2; 実行は成功し、トランザクション 2 は a=2 で X ロックを占有し、タイプはレコード ロックです。
3	begin
4		【フェーズ1を挿入】テスト(id, a)値(10, 2)に挿入します。トランザクション2はSロックを適用します。重複キーチェック。チェックが成功しました。
		データの挿入 を開始し、S ロックが X ロックにアップグレードされ、タイプは挿入意図です。同様に、X ロックがキューに入り、待機のループが形成され、デッドロックが発生します。

概要

デッドロックのトラブルシューティングを行う場合は、まずデッドロック ログに基づいてループ待機シナリオを分析し、次に各トランザクションで現在実行されている SQL に基づいてロックの種類と順序を分析し、その逆を行う必要があります。ループがどのように形成されているかを推測して、デッドロックの原因を見つけます。

以上がMysql でデッドロックのトラブルシューティング プロセスの完全な記録を共有するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。