MySQL文のロックの実現分析
摘要：MySQL二つのSQL文のロックの分析
下のSQL文にはどんな錠がかけられていますか？
（2）現在のシステムの隔離レベルは何ですか？
（3）id列がメインキーでない場合、id列にインデックスがありますか？
（4）id列に二級索引がある場合、この索引は二級索引ですか？
（5）二つのSQLの実行計画は何ですか？索引のスキャンですか？それとも全表スキャンですか？
実際の実行計画はMySQLの出力に準拠する必要があります。
組み合わせ1：id列はメインキーで、RC分離レベル
組み合わせの2：id列は二級の唯一の索引で、RC隔離レベル
組み合わせ3：id列は二級の非一意索引、RC隔離レベルです。
組み合わせ4：id列にはインデックスがなく、RC隔離レベル
組み合わせ5：id列はメインキー、RRはレベルを分離します。
組み合わせ六：id列は二級の唯一の索引で、RR隔離レベル
組み合わせ7：id列は二級の非一意索引で、RR隔離レベル
組み合わせ8：id列にインデックスがなく、RRレベルが分離されています。
Serializable隔離レベル
RR RC分離レベルではSQL 1：selectはロックされず、スナップショットを採用しています。以下はSQL 2：delete操作のアンロックのみについて説明します。
Perceona
組み合わせ一：idキー+RC
Perceona
唯一のインデックスの更新には、2つのXロックが必要であり、1つは一意のインデックスID＝10レコードに対応し、1つはクラスタインデックスname='d'の記録に対応する。
Perceona
IDは普通の索引として登録されており、対応するすべてのSQLクエリ条件を満たすレコードにロックがかけられます。また、これらの主要キーインデックスに記録されている記録もロックされます。
Perceona
Perceona
参考セット1
組み合わせ六：id一意索引+RR
を参照してください
組み合わせ7：id非一意索引+RR
Perceona
Perceona
前述の簡単なSQLに対して、最後の場合：Serializable分離レベル。SQL 2：delete from t 1 where id=10；Serializable隔離レベルはRepeable Read隔離レベルと完全に一致していますので、紹介しません。
Serializableの分離レベルは、SQL 1:select*from t 1 where id=10に影響を与えます。このSQLは、RC、RRの分離レベルでは、いずれもスナップショットであり、ロックしない。しかし、Serializableの分離レベルでは、SQL 1は読み取りロックをかけます。つまり、スナップショットはもう存在しません。MVCCは同時にLock-Based CCに制御を下げます。
結論：MySQL/InnoDBでは、読むということはロックしないで、すべての場合に適用されるのではなく、隔離レベルに関するものです。Serializableはレベルを分離して、読んでロックをかけないでもう創立しなくて、すべてのは読んで操作して、すべて当面読みます。
下のSQL文にはどんな錠がかけられていますか？
SLQ1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;
（2）現在のシステムの隔離レベルは何ですか？
（3）id列がメインキーでない場合、id列にインデックスがありますか？
（4）id列に二級索引がある場合、この索引は二級索引ですか？
（5）二つのSQLの実行計画は何ですか？索引のスキャンですか？それとも全表スキャンですか？
実際の実行計画はMySQLの出力に準拠する必要があります。
組み合わせ1：id列はメインキーで、RC分離レベル
組み合わせの2：id列は二級の唯一の索引で、RC隔離レベル
組み合わせ3：id列は二級の非一意索引、RC隔離レベルです。
組み合わせ4：id列にはインデックスがなく、RC隔離レベル
組み合わせ5：id列はメインキー、RRはレベルを分離します。
組み合わせ六：id列は二級の唯一の索引で、RR隔離レベル
組み合わせ7：id列は二級の非一意索引で、RR隔離レベル
組み合わせ8：id列にインデックスがなく、RRレベルが分離されています。
Serializable隔離レベル
RR RC分離レベルではSQL 1：selectはロックされず、スナップショットを採用しています。以下はSQL 2：delete操作のアンロックのみについて説明します。
Perceona
組み合わせ一：idキー+RC
Perceona
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
---TRANSACTION 5936, ACTIVE 171 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root
TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX
RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001730; asc 0;;
2: len 7; hex 26000001550110; asc & U ;;
3: len 1; hex 61; asc a;;
唯一のインデックスの更新には、2つのXロックが必要であり、1つは一意のインデックスID＝10レコードに対応し、1つはクラスタインデックスname='d'の記録に対応する。
Perceona
---TRANSACTION 1286327, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up
TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX
RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
---TRANSACTION 5938, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root
TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001732; asc 2;;
2: len 7; hex 27000001560110; asc ' V ;;
3: len 4; hex 8000000a; asc ;;
IDは普通の索引として登録されており、対応するすべてのSQLクエリ条件を満たすレコードにロックがかけられます。また、これらの主要キーインデックスに記録されている記録もロックされます。
Perceona
---TRANSACTION 1286339, ACTIVE 9 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up
TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
---TRANSACTION 5940, ACTIVE 3 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root
TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570110; asc ( W ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570132; asc ( W 2;;
3: len 4; hex 8000000a; asc ;;
Perceona
---TRANSACTION 1286373, ACTIVE 5 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up
TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap
---TRANSACTION 5946, ACTIVE 2 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root
TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a0110; asc + Z ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a012c; asc + Z ,;;
3: len 4; hex 8000000a; asc ;;
参考セット1
組み合わせ六：id一意索引+RR
を参照してください
組み合わせ7：id非一意索引+RR
Perceona
---TRANSACTION 1592633, ACTIVE 24 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up
Trx read view will not see trx with id >= 1592634, sees < 1592634
TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec
---TRANSACTION 5985, ACTIVE 7 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root
TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;
RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001761; asc a;;
2: len 7; hex 3f0000016d0132; asc ? m 2;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001761; asc a;;
2: len 7; hex 3f0000016d0110; asc ? m ;;
3: len 4; hex 8000000a; asc ;;
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 1; hex 66; asc f;;
Perceona
---TRANSACTION 1592639, ACTIVE 4 sec
2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up
TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X
---TRANSACTION 6000, ACTIVE 3 sec
2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root
TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 61; asc a;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0110; asc N ;;
3: len 4; hex 8000000f; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001770; asc p;;
2: len 7; hex 47000001730110; asc G s ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 63; asc c;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0122; asc N ";;
3: len 4; hex 80000006; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001770; asc p;;
2: len 7; hex 4700000173012c; asc G s ,;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 66; asc f;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0134; asc N 4;;
3: len 4; hex 8000000b; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 2; hex 7a7a; asc zz;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e013d; asc N =;;
3: len 4; hex 80000002; asc ;;
前述の簡単なSQLに対して、最後の場合：Serializable分離レベル。SQL 2：delete from t 1 where id=10；Serializable隔離レベルはRepeable Read隔離レベルと完全に一致していますので、紹介しません。
Serializableの分離レベルは、SQL 1:select*from t 1 where id=10に影響を与えます。このSQLは、RC、RRの分離レベルでは、いずれもスナップショットであり、ロックしない。しかし、Serializableの分離レベルでは、SQL 1は読み取りロックをかけます。つまり、スナップショットはもう存在しません。MVCCは同時にLock-Based CCに制御を下げます。
結論：MySQL/InnoDBでは、読むということはロックしないで、すべての場合に適用されるのではなく、隔離レベルに関するものです。Serializableはレベルを分離して、読んでロックをかけないでもう創立しなくて、すべてのは読んで操作して、すべて当面読みます。