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

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

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

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

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

(1)idは主キーですか?

(2)現在のシステムの分離レベルはどの程度ですか?

(3)id列が主キーでない場合、id列にインデックスがありますか?

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

(5)2つの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 分離​​レベルでは、ロックなしの読み取りは無効になり、すべての読み取り操作は現在の読み取りになります。

以下もご興味があるかもしれません:
  • MySQLのロック機構を理解するための記事

<<:  VMware 仮想マシンの NAT モードを構成する方法

>>:  jsはテーブルの追加と削除の操作を動的に実装します

推薦する

JavaScript で charAt() を使用して、最も頻繁に出現する文字とその出現回数をカウントする方法を教えます。

前回は、JavaScript の charAt() メソッドの使い方を紹介しました。今日は、最も多く...

MySQLでSQL文がどのように実行されるかの詳細な説明

概要最近MySQL関連の知識を勉強し始めました。学んだ知識ポイントと自分の理解を元に整理して共有しま...

mysql order by in の文字順序の詳細な説明 (推奨)

//MySQL ステートメント SELECT * FROM `MyTable` WHERE `id...

Dockerを使用してMQTTサーバーを構築するプロセスの詳細な説明

1. イメージをプルするdocker pull registry.cn-hangzhou.aliyu...

LeetCode の SQL 実装 (184. 部門内で最も高い給与)

[LeetCode] 184. 部門最高給与従業員テーブルにはすべての従業員が保存されます。すべて...

MySQLでグローバル変数とセッション変数を設定する2つの方法の詳細な説明

1. MySQL でグローバル変数を変更するには 2 つの方法があります。方法 1: my.ini ...

MySql 学習ノートにおけるトランザクション分離レベルの詳細な説明

背景トランザクションについて話すとき、誰もがそれに精通している必要があります。MySQL データベー...

HTML 名、ID、クラス (フォーマット/アプリケーション シナリオ/機能) などの違いの紹介。

ページには多くのコントロール (要素またはタグ) があります。これらのタグをより便利に操作するには、...

MySQL 5.7.25 圧縮版のインストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 5.7.25圧縮版のインストールと設定方法を参考までに紹介します。具体的な...

Flexレイアウトを使用してヘッドの固定コンテンツ領域のスクロールを実現する方法

ページ ヘッダーの固定レイアウトは、以前は position:fixed を使用して実装されていまし...

プロファイルを使用して遅い SQL を分析する MySQL の詳細な説明 (グループ左結合はサブクエリよりも効率的です)

プロファイルを使用して遅いSQLを分析するMySQL の SQL パフォーマンス アナライザーの主な...

elasticsearchを使用してインデックスデータを定期的に削除する

1. ESを使うこともあるリソースが限られている、またはビジネス上のニーズにより、最新の期間のデータ...

MySQLの基本的な共通コマンドの概要

目次MySQL の基本的な共通コマンド1. SQL文2. テーブルを作成する3. フィールドのプロパ...

CSSを使用して複数の画像を中央に水平に表示する方法

まず実装手順について説明します。最終結果 2. コードの実装HTML部分 <div class...