MySQL デッドロックシナリオ例の分析

序文

最近、MySQL で RR レベルでデッドロック問題に遭遇しました。興味深いと思ったので、調べてみて記録に残しました。

関連する知識ポイント: 共有ロック、排他ロック、意図ロック、ギャップロック、挿入意図ロック、ロック待機キュー

シナリオ

分離レベル: 繰り返し読み取り

表の構造は次の通りです

テーブルtを作成（
 id int NULLでない主キー AUTO_INCREMENT、
 NULLでない整数 デフォルト0、
 b varchar(10) NULLでないデフォルト ''
 c varchar(10) NULLでないデフォルト ''
 ユニークキー uniq_a_b(a,b)、
 ユニークキー uniq_c(c)
);

データの初期化

t(a,b,c) に値(1,'1','1') を挿入します。

2つのセッションA/Bがあり、2つのトランザクションが次の順序で実行されます。

消す

Bが4を実行した後も、すべて正常のままです
Aが5を実行するとブロックされる
次にBは6を実行し、Bはデッドロックを報告し、Bはロールバックし、Aはデータを挿入する

デッドロック情報はshow engine innodb statusで確認できます。ここでは掲載しません。まず、いくつかのロックの概念を説明し、次にデッドロックのプロセスを理解します。

共有 (S) ロック/ミューテックス (X) ロック

共有ロックによりトランザクションはレコードを読み取ることができる
ミューテックスロックによりトランザクションはレコードの読み取りと書き込みが可能になる

これら 2 種類のロックは、行ロックおよびギャップロックと混在させることができます。複数のトランザクションが同時に S ロックを保持できますが、X ロックを保持できるのは 1 つのトランザクションだけです。

意図ロック

テーブルロック (ロックモードでもある) は、トランザクションが対応するテーブルのレコードに S ロックまたは X ロックを追加しようとしていることを示します。 SELECT ... LOCK IN SHARE MODE は、レコードに S ロックを追加する前にテーブルに IS ロックを追加し、SELECT ... FOR UPDATE は、レコードに X ロックを追加する前にテーブルに IX ロックを追加します。

これはMySQLのロック最適化戦略です。意図ロックの最適化ポイントがよく分かりません。アドバイスをお願いします。

2つのロックの互換性は次のとおりです

行ロック

とても簡単です。対応する行をロックするだけです。たとえば、更新、更新の選択、削除などでは、関係する行に行ロックが追加され、他のトランザクションが動作しないようにします。

ギャップロック

RR 分離レベルでは、ファントムリードを防ぐために、レコード自体に加えて、レコードの両側のギャップにもギャップロックを追加する必要があります。
たとえば、列 a に共通インデックスがあり、レコード 1、5、10 の 3 つがある場合、 select * from t where a=5 for updateレコード 5 に行ロックを追加するだけでなく、ギャップ (1,5) と (5,10) にギャップロックを追加して、他のトランザクションが値 5 のデータを挿入してファントム読み取りが発生するのを防ぎます。
a の通常のインデックスがユニークインデックスになると、値がユニークになり、 select * from t where a=5 for update 2 つのレコードを読み取ることができないため、ギャップロックは不要になります。

ギャップロックは互いに互換性があります。相互に排他的である場合、トランザクション A は左半分 (1,5) を保持し、トランザクション B は右半分 (1,10) を保持するため、前の例のレコード a=5 が削除されると、理論的には左と右のギャップロックが新しいロック (1,10) にマージされるはずですが、この新しい大規模ロックは誰のものになるのでしょうか。そのため、ギャップロックはSギャップロックでもXギャップロックでも互いに互換性があります。

意図ロックを挿入

挿入意図ロックは、実際には特別なギャップロックです。ギャップロックの前述の説明から、2 つのトランザクションは実際の挿入の前に一定期間ギャップロックを保持できますが、実際の挿入アクションをロックすることはできないことがわかります。実際の挿入の前に、MySQL は対応するレコードの挿入意図ロックを取得して、ギャップに値を挿入する意図を示すことも試みます。

挿入意図ロックとギャップロックは相互に排他的です。たとえば、トランザクション 1 がギャップ (1,5) をロックする場合、トランザクション 2 は a=3 の挿入意図ロックを取得できないため、ロックを待機する必要があります。

デッドロックプロセス分析

次に、前の例のデッドロックプロセスを分析します。まず、show engine innodb statusを見てください。

 *** （１）取引：
トランザクション 5967、アクティブ 8 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 3 ロック構造体、ヒープ サイズ 1136、2 行ロック、UNDO ログ エントリ 1
MySQL スレッド ID 9、OS スレッド ハンドル 140528848688896、クエリ ID 537 192.168.128.1 ルート更新
t(a,b) に値(0,'0') を挿入する
*** (1) このロックが許可されるのを待機しています:
レコード ロック スペース ID 64 ページ番号 4 n ビット 72 インデックス uniq_a_b テーブル `t2`.`t` trx ID 5967 lock_mode X ロック レコード挿入の前のギャップ 意図 待機
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** （２）取引：
トランザクション 5968、アクティブ 7 秒挿入中
使用中の MySQL テーブル 1、ロックされているテーブル 1
ロック構造体 3 個、ヒープ サイズ 1136、行ロック 2 個、UNDO ログ エントリ 1
MySQL スレッド ID 8、OS スレッド ハンドル 140528848484096、クエリ ID 538 192.168.128.1 ルート更新
t(a,b) に値(0,'0') を挿入する
*** (2) ロックを保持する:
レコード ロック スペース ID 64 ページ番号 4 n ビット 72 テーブル `t2`.`t` のインデックス uniq_a_b trx ID 5968 lock_mode X ロック レコード前のギャップ
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 64 ページ番号 4 n ビット 72 インデックス uniq_a_b テーブル `t2`.`t` trx ID 5968 ロック モード X ロック レコード挿入の前のギャップ 意図 待機
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** トランザクションをロールバックします (2)

セッション A (つまり、TRANSACTION 5967) はレコード (a=1、b='1') の前の挿入意図ロックを待機しており、セッション B (つまり、TRANSACTION 5968) はレコード (a=1、b='1') の前のギャップロックを保持していますが、挿入意図ロックも待機しています。これは一体何ですか？変じゃないですか？

プロセスを最初から分析する

AとBがそれぞれ取引を開始する
Aはまずselect * from t where a=0 and b='0' for update;が、レコードが存在しないため、排他ギャップロック(-∞,1)になります。
次に、B はselect * from t where a=0 and b='0' for update;実行し、最初に IX ロックを追加します。レコードが存在しないため、排他ギャップロック (-∞,1) が追加されます。ただし、ギャップロックは互いに互換性があるため、ブロックは発生しません。
Aはinsert into t(a,b) values(0,'0');を実行します。このとき、実際に挿入を開始するには、Aは(0,'0')に対する挿入意図ロックを取得する必要があります。これはBが保持している排他ギャップロック(-∞,1)と競合するため、ロックは待機し、レコード(0,'0')のロック待機キューに入ります(レコードは存在しませんが)
B は insert insert into t(a,b) values(0,'0'); 。B は排他ギャップロック (-∞,1) を保持していますが、A もそれを保持していることがわかります。そのため、B は待機キューに入り、A がそれを解放するのを待ちます。
ディン、デッドロック発生

デッドロック情報の解釈

トランザクション 1 (トランザクション 5967)、テーブル t2.t trx id 5967 のロックインデックス uniq_a_b の取得を待機しています。lock_mode X は、rec 挿入意図の待機前にギャップをロックします。つまり、一意のインデックス uniq_a_b に対する挿入意図ロックです (lock_mode X は、rec 挿入意図の待機前にギャップをロックします)。
ロック境界は

 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

2行のレコードを示します

0と1はuniq_a_bの値を表します。a=1、b=0x31（つまり、ASCIIコードの「1」）
a=1,b='1' は主キー id=1 に対応します。InnoDB のインデックス構造により、セカンダリインデックス (非主キーインデックス) は主キーインデックスを指し、主キーインデックスはデータを指すため、主キーにインデックスを付ける必要があります。

0x80000000のint値のビットORについては、理由がよくわかりません。専門家の説明が必要です。

トランザクション 2 (トランザクション 5968) は、テーブル t2.t trx id 5968 lock_mode X のギャップロックインデックス uniq_a_b を保持し、rec の前にギャップをロックして、挿入意図ロックインデックス uniq_a_b を待機します。テーブル t2.t trx id 5968 lock_mode X は、rec の挿入意図の前にギャップをロックするため、デッドロックが発生します。

原則として、InnoDB エンジンはロールバックコストが最も低いトランザクションをロールバックしますが、具体的な基準はあまり明確ではありません (ここでも、大物が必要です)。ここでは、InnoDB はトランザクション 2 をロールバックすることを選択します。この時点でデッドロックプロセス分析は完了する。

もう一つ

まだ終わってませんよ。。。テーブル構造が

テーブルtを作成（
 id int NULLでない主キー AUTO_INCREMENT、
 NULLでない整数 デフォルト0、
 b varchar(10) NULLでないデフォルト ''
 c varchar(10) NULLでないデフォルト ''
 ユニークキー uniq_c(c)、
 ユニークキー uniq_a_b(a,b)
);
t(a,b,c) に値(1,1,1) を挿入します。

uniq_a_b の前に c に一意のインデックス uniq_c を置くだけで、最終的なデッドロック情報が変わります。

 *** （１）取引：
トランザクション 5801、アクティブ 5 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 4 ロック構造体、ヒープ サイズ 1136、3 行ロック、UNDO ログ エントリ 1
MySQL スレッド ID 5、OS スレッド ハンドル 140528848688896、クエリ ID 380 192.168.128.1 ルート更新
t2(a,b) に値(0,'0') を挿入する
*** (1) このロックが許可されるのを待機しています:
レコード ロック スペース ID 56 ページ番号 5 n ビット 72 テーブル `t2`.`t2` のインデックス uniq_a_b trx ID 5801 lock_mode X ロック レコード挿入の前のギャップ 意図 待機
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** （２）取引：
トランザクション 5802、アクティブ 4 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
ロック構造体 3 個、ヒープ サイズ 1136、行ロック 2 個、UNDO ログ エントリ 1
MySQL スレッド ID 6、OS スレッド ハンドル 140528848484096、クエリ ID 381 192.168.128.1 ルート更新
t2(a,b) に値(0,'0') を挿入する
*** (2) ロックを保持する:
レコード ロック スペース ID 56 ページ番号 5 n ビット 72 テーブル `t2`.`t2` のインデックス uniq_a_b trx ID 5802 lock_mode X ロック レコード前のギャップ
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 80000001; 昇順 ;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 56 ページ番号 4 n ビット 72 テーブル `t2`.`t2` のインデックス uniq_c trx ID 5802 ロック モード S 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 0
 0: 長さ 0; 16 進数; 昇順;;
 1: 長さ 4; 16 進数 80000002; 昇順 ;;

*** トランザクションをロールバックします (2)

トランザクション 2 が待機しているロックは、以前の挿入意図ロックから共有ロックに変更されました。なんてこった？

ソースコードを読んでいないので、現象から推測することしかできません。テーブル構造内のcのユニークインデックスは(a,b)の前にあり、挿入時にcの値が指定されていないため、デフォルト値0が使用されます。InnoDBは、まず0のレコードがあるかどうかを確認する必要があります。ある場合は、ユニークキーの競合を報告するため、まずSロックを追加する必要がありますが、レコード(0,'0')にはすでにIXロックがあります。上記の互換性マトリックスを見ると、SロックとIXロックは相互に排他的であるため、ロックして待機することしかできません。

要約する

単純な選択と挿入のステートメントのように見えますが、その下には複雑なロックメカニズムが設計されています。これらのロックメカニズムを理解することは、効率的な SQL を書くのに役立ちます (少なくとも正しい 😂)

残る問題:

インテンションロックの最適化ポイントは何ですか?
ロック情報において、行レコードのビット OR の 0x80000000 とは何ですか?
ロックの排他性を決定する順序。シナリオ 1 では、(0,'0') に互換性のあるギャップロックがあり、待機キューにロックがあります。どちらを最初に決定する必要がありますか。
InnoDB のトランザクションロールバックコストを計算するアルゴリズム

参考文献

http://hedengcheng.com/?p=771
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
次キーロック
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません: