この記事では、MySQL ステートメントのロック分析は常に難しいトピックです。仕事をしていると、同僚からこの点に関してよく質問されます。今日は皆さんのお役に立てればと思い、この問題について簡単にお話します。
次の SQL ステートメントにどのようなロックが追加されているかを見てください
SLQ1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;
ログイン後にコピー
(1) id は主キーですか? (2) 現在のシステムの分離レベルは何ですか? 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のロックについてのみ説明します: 削除操作
Percona
の組み合わせ。 ：IDプライマリキー +rc
percona
rreee
mysql
rreeee
コンビネーション2：ID一意のインデックス + RC
ユニークなインデックスの更新には、2つのXロックが必要です。一意のインデックス id=10 のレコードに対応するものと、クラスター化インデックス name='d' に対応するもの: ID 非固有インデックス + RC
ID が通常のインデックスとしてリストされている場合、対応するすべての SQL クエリ条件が満たされます。レコードは同時にロックされ、主キー インデックス上のこれらのレコードもロックされます
Percona
---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
ログイン後にコピー
MySQL
---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;;
ログイン後にコピー
組み合わせ 4: ID インデックスなし + RC
Percona
---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
ログイン後にコピー
MySQL
りー
組み合わせ 5: ID 主キー + RR
参照 組み合わせ 1
組み合わせ 6: ID 固有インデックス + RR
参照 組み合わせ 2
組み合わせ 7: ID 非固有インデックス + RR
Percona
---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 ;;
ログイン後にコピー
MySQL
---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
ログイン後にコピー
組み合わせ 8: ID インデックスなし +RR
Percon a
---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 ;;
ログイン後にコピー
MySQL
---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
ログイン後にコピー
構成 9: シリアル化可能
前述の単純な SQL の場合、最後のケースはシリアル化可能な分離レベルです。 SQL2: delete from t1 where id = 10; の場合、シリアル化可能分離レベルは反復読み取り分離レベルとまったく同じであるため、導入されません。
シリアル化可能な分離レベルは SQL1 に影響します: select * from t1 where id = 10; この SQL は、RC および RR 分離レベルでは、ロックなしで読み取られるスナップショットです。ただし、シリアル化可能分離レベルでは、SQL1 によって読み取りロックが追加されます。これは、スナップショット読み取りが存在しなくなり、MVCC 同時実行制御がロックベース CC にダウングレードされることを意味します。
MySQL/InnoDB では、いわゆるロックなしの読み取りはすべての状況に適用されるわけではありませんが、分離レベルに関連します。読み取りがロックされていない場合、シリアル化可能分離レベルは無効になります。すべての読み取り操作は現在の読み取りです。
関連する推奨事項:
Mysql の高同時実行ロック トランザクション処理
PHP_PHP チュートリアルでファイルをロックする方法
MySQL トランザクションとロック メカニズム
以上がMySQL ステートメントのロックを実装する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
