MySQL デッドロックのトラブルシューティングの全プロセス記録

【著者】

Liu Bo: Ctrip テクニカルサポートセンターのシニアデータベースマネージャー。SQL Server と MySQL の運用、保守、トラブルシューティングに重点を置いています。

【環境】

バージョン番号: 5.6.21

分離レベル: REPEATABLE READ

[問題の説明]

監視アラームを受信した後、オンラインアプリケーション DeadLock はエラーを報告しました。このエラーは 15 分ごとに時間どおりに表示されます。エラー統計は次のとおりです。

ログを表示するには、Mysql サーバーにログインします。

mysql> エンジン innodb ステータスを表示\G

*** （１）取引：

トランザクション 102973、アクティブ 11 秒開始インデックス読み取り

使用中の MySQL テーブル 3、ロックされている 3

LOCK WAIT 4 つのロック構造体、ヒープ サイズ 1136、3 つの行ロック

MySQL スレッド ID 6、OS スレッド ハンドル 140024996574976、クエリ ID 83 localhost us 更新中

テストテーブルの更新

SET列1 = 1、

列2 = sysdate(),

列3 = '026'

列4 = 0

AND列5 = 485

AND 列6 = 'SEK'

*** (1) このロックが許可されるのを待機しています:

レコード ロック スペース ID 417 ページ番号 1493 n ビット 1000 インデックス idx_column6 テーブル test.TestTable trx ID 102973 lock_mode X 待機中

レコード ロック、ヒープ番号 859 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 0

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007e1452; asc ~ R;;

*** （２）取引：

トランザクション 102972、アクティブ 26 秒開始インデックス読み取り

使用中の MySQL テーブル 3、ロックされている 3

219 個のロック構造体、ヒープ サイズ 24784、2906 個の行ロック、UNDO ログ エントリ 7

MySQL スレッド ID 5、OS スレッド ハンドル 140024996841216、クエリ ID 84 localhost us 更新中

テストテーブルの更新

列1を1に設定

列2 = sysdate(),

列3 = '026'

列4 = 0

AND 列5 = 485

AND 列6 = 'SEK'

*** (2) ロックを保持する:

レコード ロック スペース ID 417 ページ番号 1493 n ビット 1000 インデックス idx_Column6 テーブル test.TestTable trx ID 102972 lock_mode X

レコード ロック、ヒープ番号 1 物理レコード: n_fields 1; コンパクト フォーマット; 情報ビット 0

0: 長さ 8; 16 進数 73757072656d756d; asc 上限;;


レコード ロック、ヒープ番号 859 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 0

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007e1452; asc ~ R;;

*** (2) このロックが許可されるのを待機しています:

レコード ロック スペース ID 601 ページ番号 89642 n ビット 1000 インデックス idx_column6 テーブル test.TestTable trx ID 32231892482 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません

レコード ロック、ヒープ番号 38 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 0

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007eea14; asc ~ ;;

一見すると、同じインデックスを持つ同じ行を更新するとブロックになるため、TimeOut エラーが報告されるはずです。なぜ DeadLock エラーが報告されるのでしょうか?

[予備分析]

まず（2）トランザクション、トランザクション32231892482を分析してみましょう。

待機中のロック情報は次のとおりです。

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007eea14; 昇順

保持されるロック情報は次のとおりです。

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007eeac4; 昇順

まず(1) TRANSACTION、TRANSACTION 32231892617を分析してみましょう。

待機中のロック情報は次のとおりです。

0: 長さ 3; 16 進数 53454b; 昇順 SEK;;

1: 長さ 8; 16 進数 80000000007eeac4; 昇順

したがって、2 つのリソースが相互に依存し、デッドロックが発生するデッドロックテーブルを描くことができます。

取引	所有	待って
32231892617	53454b\80000000007eea14	53454b\80000000007eeac4
32231892482	53454b\80000000007eeac4	53454b\80000000007eea14

もう一度、説明の結果を見てみましょう。

mysql>desc UPDATE TestTable SET Column1=1、Column2 = sysdate()、Column3 = '025' Column4 = 0 AND Column5 = 477 AND Column6 = 'SEK' \G;
************************** 1. 行 ****************************
id: 1
選択タイプ: 更新
テーブル: TestTable
パーティション: NULL
タイプ: インデックスマージ
可能なキー: column5_index、idx_column5_column6_Column1、idxColumn6
キー: column5_index、idxColumn6
キーの長さ: 8,9
参照: NULL
行数: 7
フィルター: 100.00
追加: intersect(column5_index,idxColumn6); を使用する where を使用する

EXTRA 列が表示されます:

intersect(column5_index,idxColumn6) を使用する

5.1 からは、インデックスマージ最適化テクノロジが導入され、複数のインデックスを使用して同じテーブルで条件付きスキャンを実行できるようになりました。

関連ドキュメント: http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

インデックスマージメソッドは、複数の範囲スキャンで行を取得し、その結果を 1 つにマージするために使用されます。マージでは、基礎となるスキャンの結合、共通部分、または共通部分の結合を生成できます。このアクセスメソッドは、単一のテーブルからのインデックススキャンをマージします。複数のテーブルにわたるスキャンはマージしません。

【シミュレーションと検証】

上記の予備分析に基づいて、交差が原因であると推測されるため、テスト環境でシミュレーションして検証し、デッドロックをシミュレートするために 2 つのセッションを開きます。

時系列	セッション1	セッション2
1	始める;
2	UPDATE TestTable SET Column2 = sysdate() Column4 = 0 AND Column5 = 47 AND Column6 = 'SEK 実行は成功しました。7 行に影響します
3		始める;
4		UPDATE TestTable SET Column2 = sysdate(), Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'; ブロックされました
5	UPDATE TestTable SET Column2 = sysdate(), Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'; 実行成功	エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

上記の情報に基づいて、セッション 2 はブロックされていますが、時系列 5 でセッション 1 に必要なリソースの X ロックも取得していることがわかります。別のクエリ select count(Column5) from TestTable where Column5 = 485 を開き、SET TRANSACTION ISOLATION LEVEL SERIALIZABLE を設定し、Column5 = 485 の行をクエリして、ロック待機情報を確認します。
mysql> r.trx_id 待機中trx_id、r.trx_mysql_thread_id 待機中スレッド、r.trx_query 待機中クエリ、b.trx_id を選択

mysql> SELECT r.trx_id waiting_trx_id、r.trx_mysql_thread_id waiting_thread、r.trx_query waiting_query、b.trx_id blocking_trx_id、b.trx_mysql_thread_id blocking_thread、b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \G;

************************** 1. 行 ****************************

待機中のtrx_id: 103006

待機スレッド: 36

待機クエリ: テストテーブルを更新 SET 列1 = 1、列2 = sysdate()、列3 = '026' 列4 = 0 AND 列5 = 485 AND 列6 = 'SEK'

ブロッキング_trx_id: 103003

ブロックスレッド: 37

ブロッキングクエリ: NULL

************************** 2. 行 ****************************

待機中のtrx_id: 421500433538672

待機スレッド: 39

待機クエリ: TestTable から count(Column5) を選択します。Column5 = 485

ブロッキング_trx_id: 103006

ブロックスレッド: 36

ブロッキングクエリ: UPDATE TestTable SET 列1 = 1、列2 = sysdate()、列3 = '026' 列4 = 0 AND 列5 = 485 AND 列6 = 'SEK'

セットに 2 行、警告 1 件 (0.00 秒)

mysql> information_schema.innodb_lock_waits \G から * を選択します。

************************** 1. 行 ****************************

リクエスト_trx_id: 103006

要求されたロックID: 103006:417:1493:859

ブロッキング_trx_id: 103003

ブロッキングロックID: 103003:417:1493:859

************************** 2. 行 ****************************

リクエスト_trx_id: 421500433538672

要求されたロックID: 421500433538672:417:749:2

ブロッキング_trx_id: 103006

ブロッキングロックID: 103006:417:749:2

セットに 2 行、警告 1 件 (0.00 秒)

mysql> INNODB_LOCKS から * を選択します \G;

************************** 1. 行 ****************************

ロックID: 103006:417:1493:859

ロック_trx_id: 103006

ロックモード: X

ロックタイプ: レコード

lock_table: テスト.TestTable

ロックインデックス: idxColumn6

ロックスペース: 417

ロックページ: 1493

ロック_rec: 859

ロックデータ: 'SEK'、8262738

************************** 2. 行 ****************************

ロックID: 103003:417:1493:859

ロック_trx_id: 103003

ロックモード: X

ロックタイプ: レコード

lock_table:テスト.テストテーブル

ロックインデックス: idxColumn6

ロックスペース: 417

ロックページ: 1493

ロック_rec: 859

ロックデータ: 'SEK'、8262738

************************** 3. 行 ****************************

ロックID: 421500433538672:417:749:2

ロック_trx_id: 421500433538672

ロックモード: S

ロックタイプ: レコード

lock_table: テスト.TestTable

ロックインデックス: 列5インデックス

ロックスペース: 417

ロックページ: 749

ロック_rec: 2

ロックデータ: 485, 8317620

************************** 4. 行 ****************************

ロックID: 103006:417:749:2

ロック_trx_id: 103006

ロックモード: X

ロックタイプ: レコード

lock_table: テスト.TestTable

ロックインデックス: 列5インデックス

ロックスペース: 417

ロックページ: 749

ロック_rec: 2

ロックデータ: 485, 8317620

セットに 4 行、警告 1 件 (0.00 秒)

Session2、trx_id 103006 が trx_id 421500433538672 をブロックし、trx_id 421500433538672 の requested_lock も lock_data: 485、8317620 であることがわかります。これは、セッション 2 がブロックされていないにもかかわらず、インデックス column5_index に関連するロックを取得していることを示しています。ブロックされる理由は、交差によるものです。idxColumn6 のロックも必要です。これで考え方は明確になりました。次の表に示すように、ロック割り当て全体の情報を簡略化してみましょう (要求されたロックはシアン色で示され、取得する必要があるが取得されていないロックは赤色で示されます)。