MySQL ステートメントロックの実装の分析

概要: 2 つの MySQL SQL ステートメントロックの分析

次のSQL文にどのようなロックが追加されるか見てみましょう

SLQ1: id = 10 の場合、t1 から * を選択します。
SQL2: id = 10 の t1 から削除します。

（１）idは主キーですか？

（２）現在のシステムの分離レベルはどの程度ですか？

（３）id列が主キーでない場合、id列にインデックスがありますか？

（4）id列にセカンダリインデックスがある場合、このインデックスはセカンダリインデックスですか？

（５）２つのSQL文の実行計画は何ですか？インデックススキャンまたはフルテーブルスキャン

実際の実行計画はMySQLの出力に基づいている必要がある

組み合わせ 1: id 列は主キー、RC 分離レベル組み合わせ 2: id 列はセカンダリ一意インデックス、RC 分離レベル組み合わせ 3: id 列はセカンダリ非一意インデックス、RC 分離レベル組み合わせ 4: id 列にインデックスがない、RC 分離レベル組み合わせ 5: id 列は主キー、RR 分離レベル組み合わせ 6: id 列はセカンダリ一意インデックス、RR 分離レベル組み合わせ 7: id 列はセカンダリ非一意インデックス、RR 分離レベル組み合わせ 8: id 列にインデックスがない、RR 分離レベル

シリアル化可能な分離レベル

RR RC 分離レベルでは、SQL1: 選択はロックされず、スナップショット読み取りが使用されます。以下では、SQL2: 削除操作のロックについてのみ説明します。
ペルコナ

組み合わせ 1: id 主キー + RC
ペルコナ

---トランザクション 1286310、アクティブ 9 秒
ロック構造体 2 個、ヒープ サイズ 360、行ロック 1 個、UNDO ログ エントリ 1
MySQL スレッド ID 341、OS スレッド ハンドル 0x7f4d540d0700、クエリ ID 4510972 ローカルホスト ルートのクリーンアップ中
テーブル ロック テーブル `test`.`t1` trx id 1286310 ロック モード IX
レコード ロック スペース ID 29 ページ番号 3 n ビット 80 インデックス `PRIMARY` テーブル `test`.`t1` trx ID 1286310 lock_mode X はレコードをロックしますが、ギャップはロックしません

マイグレーション

---トランザクション 5936、アクティブ 171 秒
ロック構造体 2 個、ヒープ サイズ 360、行ロック 1 個、UNDO ログ エントリ 1
MySQL スレッド ID 2、OS スレッド ハンドル 0x7f5677201700、クエリ ID 364 localhost root
テーブル ロック テーブル `test`.`t1` トランザクション ID 5936 ロック モード IX
レコード ロック スペース ID 6 ページ番号 3 n ビット 80 インデックス `PRIMARY` テーブル `test`.`t1` trx ID 5936 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 5 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ 6; 16 進数 000000001730; 昇順 0;;
 2: 長さ 7; 16 進数 26000001550110; asc & U ;;
 3: 長さ 1; 16 進数 61; asc a;;

組み合わせ2: IDユニークインデックス + RC
ユニークインデックスの更新には、ユニークインデックス id=10 レコード用とクラスター化インデックス name='d' レコード用の 2 つの X ロックが必要です。
ペルコナ

---トランザクション 1286327、アクティブ 3 秒
3 つのロック構造体、ヒープ サイズ 360、2 つの行ロック、UNDO ログ エントリ 1
MySQL スレッド ID 344、OS スレッド ハンドル 0x7f4d5404e700、クエリ ID 4510986 ローカルホスト ルートのクリーンアップ中
テーブル ロック テーブル `test`.`t2` trx id 1286327 ロック モード IX
レコード ロック スペース ID 30 ページ番号 4 n ビット 80 テーブル `test`.`t2` のインデックス `id` trx ID 1286327 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック スペース ID 30 ページ番号 3 n ビット 80 インデックス `PRIMARY` テーブル `test`.`t2` trx ID 1286327 lock_mode X はレコードをロックしますが、ギャップはロックしません

マイグレーション

---トランザクション 5938、アクティブ 3 秒
3 つのロック構造体、ヒープ サイズ 360、2 つの行ロック、UNDO ログ エントリ 1
MySQL スレッド ID 2、OS スレッド ハンドル 0x7f5677201700、クエリ ID 374 localhost root
テーブル ロック テーブル `test`.`t2` trx id 5938 ロック モード IX
レコード ロック スペース ID 7 ページ番号 4 n ビット 80 テーブル `test`.`t2` のインデックス `id` trx ID 5938 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 7 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ1; 16進数64; asc d;;

レコード ロック スペース ID 7 ページ番号 3 n ビット 80 テーブル `test`.`t2` のインデックス `PRIMARY` trx ID 5938 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 7 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 64; asc d;;
 1: 長さ 6; 16 進数 000000001732; 昇順 2;;
 2: 長さ 7; 16 進数 27000001560110; asc ' V ;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

組み合わせ3: ID非一意インデックス + RC
ID列が通常のインデックスの場合、SQLクエリ条件を満たすすべての対応するレコードがロックされます。同時に、主キーインデックスのレコードもロックされます。
ペルコナ

---トランザクション 1286339、アクティブ 9 秒
3 つのロック構造体、ヒープ サイズ 360、4 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 347、OS スレッド ハンドル 0x7f4b67fff700、クエリ ID 4511015 ローカルホスト ルートのクリーンアップ中
テーブル ロック テーブル `test`.`t3` trx id 1286339 ロック モード IX
レコード ロック スペース ID 31 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 1286339 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック スペース ID 31 ページ番号 3 n ビット 80 テーブル `test`.`t3` のインデックス `PRIMARY` trx ID 1286339 lock_mode X はレコードをロックしますが、ギャップはロックしません

マイグレーション

---トランザクション 5940、アクティブ 3 秒
3 つのロック構造体、ヒープ サイズ 360、4 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 2、OS スレッド ハンドル 0x7f5677201700、クエリ ID 378 localhost root
テーブル ロック テーブル `test`.`t3` trx id 5940 ロック モード IX
レコード ロック スペース ID 8 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 5940 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 4 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ1; 16進数62; asc b;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ1; 16進数64; asc d;;

レコード ロック スペース ID 8 ページ番号 3 n ビット 80 テーブル `test`.`t3` のインデックス `PRIMARY` trx ID 5940 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 4 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 62; asc b;;
 1: 長さ 6; 16 進数 000000001734; 昇順 4;;
 2: 長さ 7; 16進数 28000001570110; asc ( W ;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 64; asc d;;
 1: 長さ 6; 16 進数 000000001734; 昇順 4;;
 2: 長さ 7; 16進数 28000001570132; asc ( W 2;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

組み合わせ4: IDインデックスなし + RC
ペルコナ

---トランザクション 1286373、アクティブ 5 秒
2 つのロック構造体、ヒープ サイズ 360、2 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 348、OS スレッド ハンドル 0x7f4d54193700、クエリ ID 4511037、localhost ルートのクリーンアップ中
テーブル ロック テーブル `test`.`t4` trx id 1286373 ロック モード IX
レコード ロック スペース ID 33 ページ番号 3 n ビット 80 インデックス `PRIMARY` テーブル `test`.`t4` trx ID 1286373 lock_mode X はレコードをロックしますが、ギャップはロックしません

マイグレーション

---トランザクション 5946、アクティブ 2 秒
2 つのロック構造体、ヒープ サイズ 360、2 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 2、OS スレッド ハンドル 0x7f5677201700、クエリ ID 382 localhost root
テーブル ロック テーブル `test`.`t4` trx id 5946 ロック モード IX
レコード ロック スペース ID 9 ページ番号 3 n ビット 80 テーブル `test`.`t4` のインデックス `PRIMARY` trx ID 5946 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 3 物理レコード: n_fields 4; コンパクト フォーマット; 情報ビット 32
 0: 長さ 1; 16 進数 62; asc b;;
 1: 長さ 6; 16 進数 00000000173a; asc :;;
 2: 長さ 7; 16 進数 2b0000015a0110; asc + Z ;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 64; asc d;;
 1: 長さ 6; 16 進数 00000000173a; asc :;;
 2: 長さ 7; 16 進数 2b0000015a012c; asc + Z ,;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

組み合わせ5: id主キー + RR
参考組み合わせ1

組み合わせ6: IDユニークインデックス + RR
参考組み合わせ2

組み合わせ7: ID非一意インデックス + RR
ペルコナ

---トランザクション 1592633、アクティブ 24 秒
4 つのロック構造体、ヒープ サイズ 1184、5 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 794、OS スレッド ハンドル 0x7f4d5404e700、クエリ ID 7801799 ローカルホスト ルートのクリーンアップ中
Trx 読み取りビューでは、ID >= 1592634 の trx は表示されず、< 1592634 が表示されます。
テーブル ロック テーブル `test`.`t3` trx id 1592633 ロック モード IX
レコード ロック スペース ID 31 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 1592633 lock_mode X
レコード ロック スペース ID 31 ページ番号 3 n ビット 80 インデックス `PRIMARY` テーブル `test`.`t3` trx ID 1592633 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック スペース ID 31 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 1592633 lock_mode X ロック レコード前のギャップ

マイグレーション

---トランザクション 5985、アクティブ 7 秒
4 つのロック構造体、ヒープ サイズ 1184、5 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 12、OS スレッド ハンドル 0x7f56770fd700、クエリ ID 500 localhost root
テーブル ロック テーブル `test`.`t3` trx id 5985 ロック モード IX
レコード ロック スペース ID 8 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 5985 lock_mode X
レコード ロック、ヒープ番号 4 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ1; 16進数64; asc d;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 8000000a; 昇順 ;;
 1: 長さ1; 16進数62; asc b;;

レコード ロック スペース ID 8 ページ番号 3 n ビット 80 テーブル `test`.`t3` のインデックス `PRIMARY` trx ID 5985 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 4 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 64; asc d;;
 1: 長さ 6; 16 進数 000000001761; asc a;;
 2: 長さ 7; 16 進数 3f0000016d0132; asc ? m 2;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 62; asc b;;
 1: 長さ 6; 16 進数 000000001761; asc a;;
 2: 長さ 7; 16 進数 3f0000016d0110; asc ? m ;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック スペース ID 8 ページ番号 4 n ビット 80 テーブル `test`.`t3` のインデックス `idx_key` trx ID 5985 lock_mode X ロック レコード前のギャップ
レコード ロック、ヒープ番号 8 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 0
 0: 長さ 4; 16 進数 8000000b; 昇順 ;;
 1: 長さ1; 16進数66; asc f;;

組み合わせ8: インデックスなしのID + RR
ペルコナ

---トランザクション 1592639、アクティブ 4 秒
2 つのロック構造体、ヒープ サイズ 360、7 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 794、OS スレッド ハンドル 0x7f4d5404e700、クエリ ID 7801804 ローカルホスト ルートのクリーンアップ中
テーブル ロック テーブル `test`.`t4` trx id 1592639 ロック モード IX
レコード ロック スペース ID 33 ページ番号 3 n ビット 80 テーブル `test`.`t4` のインデックス `PRIMARY` trx ID 1592639 lock_mode X

マイグレーション

---トランザクション 6000、アクティブ 3 秒
2 つのロック構造体、ヒープ サイズ 360、7 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 12、OS スレッド ハンドル 0x7f56770fd700、クエリ ID 546 localhost root
テーブル ロック テーブル `test`.`t4` trx id 6000 ロック モード IX
レコード ロック スペース ID 9 ページ番号 3 n ビット 80 テーブル `test`.`t4` のインデックス `PRIMARY` trx ID 6000 lock_mode X
レコード ロック、ヒープ番号 1 物理レコード: n_fields 1; コンパクト フォーマット; 情報ビット 0
 0: 長さ 8; 16 進数 73757072656d756d; asc 上限;;

レコード ロック、ヒープ番号 2 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 0
 0: 長さ 1; 16 進数 61; asc a;;
 1: 長さ 6; 16 進数 000000001722; 昇順 ";;
 2: 長さ 7; 16 進数 9e0000014e0110; asc N ;;
 3: 長さ 4; 16 進数 8000000f; 昇順 ;;

レコード ロック、ヒープ番号 3 物理レコード: n_fields 4; コンパクト フォーマット; 情報ビット 32
 0: 長さ 1; 16 進数 62; asc b;;
 1: 長さ 6; 16 進数 000000001770; asc p;;
 2: 長さ 7; 16 進数 47000001730110; asc G s ;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック、ヒープ番号 4 物理レコード: n_fields 4; コンパクト フォーマット; 情報ビット 0
 0: 長さ1; 16進数63; asc c;;
 1: 長さ 6; 16 進数 000000001722; 昇順 ";;
 2: 長さ 7; 16 進数 9e0000014e0122; asc N ";;
 3: 長さ 4; 16 進数 80000006; 昇順 ;;

レコード ロック、ヒープ番号 5 物理レコード: n_fields 4、コンパクト フォーマット、情報ビット 32
 0: 長さ 1; 16 進数 64; asc d;;
 1: 長さ 6; 16 進数 000000001770; asc p;;
 2: 長さ 7; 16 進数 4700000173012c; asc G s ,;;
 3: 長さ 4; 16 進数 8000000a; 昇順 ;;

レコード ロック、ヒープ番号 6 物理レコード: n_fields 4; コンパクト フォーマット; 情報ビット 0
 0: 長さ 1; 16 進数 66; asc f;;
 1: 長さ 6; 16 進数 000000001722; 昇順 ";;
 2: 長さ 7; 16 進数 9e0000014e0134; asc N 4;;
 3: 長さ 4; 16 進数 8000000b; 昇順 ;;

レコード ロック、ヒープ番号 7 物理レコード: n_fields 4; コンパクト フォーマット; 情報ビット 0
 0: 長さ 2; 16 進数 7a7a; asc zz;;
 1: 長さ 6; 16 進数 000000001722; 昇順 ";;
 2: 長さ 7; 16 進数 9e0000014e013d; asc N =;;
 3: 長さ 4; 16 進数 80000002; 昇順 ;;

組み合わせ9: シリアル化可能

上記の単純な SQL の場合、最後のケースは Serializable 分離レベルです。 SQL2: delete from t1 where id = 10; の場合、Serializable 分離レベルは Repeatable Read 分離レベルとまったく同じなので、ここでは紹介しません。

Serializable 分離レベルは SQL1 に影響します: select * from t1 where id = 10; この SQL ステートメントはスナップショット読み取りであり、RC および RR 分離レベルではロックされません。ただし、Serializable 分離レベルでは、SQL1 によって読み取りロックが追加されるため、スナップショット読み取りは存在しなくなり、MVCC 同時実行制御は Lock-Based CC にダウングレードされます。

結論: MySQL/InnoDB では、いわゆるロックなしの読み取りはすべての状況に当てはまるわけではなく、分離レベルに関連しています。 Serializable 分離レベルでは、ロックなしの読み取りは無効になり、すべての読み取り操作は現在の読み取りになります。

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