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はテーブルの追加と削除の操作を動的に実装します

推薦する

Centos7 での Rsync+crontab 定期同期バックアップ

最近、社内の重要なサーバデータを定期的にストレージにバックアップし、ついでにメモしておきたい以前、W...

CSS3入力ボックスの実装コードはGoogleログインのアニメーション効果に似ています

CSS3を使用して、Googleログインページと同様の入力ボックスをアニメーション化します。効果1 ...

CSS で写真のスタッキング効果を実装するサンプルコード

成果を達成するステップ1. 初期index.html最初の写真、一番上の写真を作成します。写真の i...

MySQL クロスデータベーストランザクション XA 操作の例

この記事では、例を使用して、MySQL のデータベース間トランザクション XA 操作について説明しま...

Linux テキスト検索コマンド find の詳細な使用方法

find コマンドは主にディレクトリやファイルを検索するために使用され、一致のために複数のパラメータ...

Dockerの国内イメージソースを変更する方法

Dockerデーモンのアクセラレータを構成する設定ファイルから Docker を起動し、/etc/d...

MySQL データのバックアップと復元のサンプル コード

1. データのバックアップ1. mysqldumpコマンドを使用してバックアップするmysqldum...

...

CSS3 天子グリッドリストのスタイルの書き方

多くのプロジェクトでは、中央に灰色の分割線があり、両側に分割線がないグリッド表示の機能を実装する必要...

Docker 構成 Alibaba Cloud イメージアクセラレーション プル実装

今日はdockerを使ってイメージをpullしたのですが、速度が悪くて見れず最後まで待ち続けました。...

MySQL ログトリガー実装コード

SQL文 ドロップトリガー もし sys_menu_edit が存在します。 各行のsys_menu...

DockerでRedisをデプロイして起動する方法

DockerでRedisをデプロイするまずLinuxにDockerをインストールし、次にDocker...

ffmpeg 中国語パラメータの説明と使用例

1. ffmpeg がビデオ ファイルをプッシュする場合、オーディオとビデオのエンコード形式は H2...

ES5とES6の違いを分析する

目次概要関数シグネチャオプションパラメータ非厳密モード例外処理実用要約する概要ご存知のとおり、ES6...