MySQL/Aurora/TiDBロック入門 – 第２回ロックモニターの読み方【動画解説付】

MySQL とその互換 DB のロックやトランザクションの挙動を紹介する入門シリーズ、「第１回トランザクション分離レベル」では READ COMMITTED や REPEATABLE READ でどういう挙動になるか紹介しました。

第２回目の今回は MySQL InnoDB のロックモニターの読み方、使い方について解説します。MySQL のロック機構を理解するツールとして便利なのでぜひご一読ください！

★ 第１回トランザクション分離レベル
★ 第２回ロックモニターの読み方
★ 第３回ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema でロックを測定
★ 第７回ギャップロックがあっても更新される
★ 第８回ネクストキーロックと降順インデックス
★ 第９回共有ロックで発生しやすいデッドロック
★ 第１０回ロック読み取りが READ COMMITTED になる詳細
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い

今回の解説動画です

実戦で必要になる行に対するロック

第１回で説明したように、REPEATABLE READ のトランザクション分離レベルで他から影響されないことは保証されますが、独立した他のトランザクションでの更新を止めることはできませんので、実戦で必要になる「自分の処理が終わるまで他のトランザクションも更新して欲しくない」というケースには対応出来ません。

これを実現するのが FOR UPDATE や LOCK IN SHARE MODE といった排他／共有の行ロックですが、行がある場合と無い場合で違う等、様々なパターンがあり複雑な仕組みとなっています。

机上で理論的に考えるより、実際に InnoDB がかけているロックをそのまま観察するとわかりやすいので、ロックモニターを使って直接観察する方法を解説します。

ロックモニター有効化の注意点

InnoDB のロックモニターはデフォルトでは無効になっています。本シリーズでは学習なので気にせず有効化していますが、ロックモニターを本番環境で有効化したい場合は、普段使われていないものを使うことによって起きるかもしれない不具合等のリスクを理解した上でご検討ください。

有効化は以下のコマンドで行います。

SET GLOBAL innodb_status_output_locks=ON;

以下の 3 行を持つテーブル t2 を使います

+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

UPDATE でかかる排他ロックを観察

まず基本的な更新でかかる排他ロックを観察してみましょう。トランザクションを開始し、k = 1 の行で v = 1 にします。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t2 SET v = 1 WHERE k = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

トランザクションは実行中のまま、別の端末で SHOW ENGINE INNODB STATUS\G を実行します

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
・・・・省略・・・・
------------
TRANSACTIONS
------------
・・・・省略・・・・
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 9490 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002512; asc     % ;;
 2: len 7; hex 020000010607aa; asc        ;;
 3: len 4; hex 80000001; asc     ;;
・・・・省略・・・・

TRANSACTIONS セクションの RECORD LOCKS という行に注目します

RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 9490 lock_mode X locks rec but not gap

行単位のロックはギャップロック含めて全て RECORD LOCKS という大文字で書かれた行ロックという説明でロックの種類が説明されます。この例の場合は PRIMARY キーにかかっていますが、どのインデックスでロックをかけているか、トランザクションの ID 、lock_mode が排他 (Exclusive) のネクストキーロックであることが説明されます。

ロックモードは以下の３種類になります

lock_mode X locks rec but not gap ギャップロックではない単純な行ロック
lock_mode X locks gap before rec 前方ギャップロック
lock_mode X 行ロックと前方ギャップロックが組み合わさったネクストキーロック

そしてこれらタイプの行ロックがかかっているレコードの場所として、続けて小文字も含んだ Record lock で始まる行があり、下のフォーマットで説明されます。同じタイプのロックで複数行にかかっている場合は、場所の説明だけが複数回繰り返し表示されます。

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002512; asc     % ;;
 2: len 7; hex 020000010607aa; asc        ;;
 3: len 4; hex 80000001; asc     ;;

フィールドを細かく見て行くと

0 : キーの値です。左端のビットが何かのフラグに使用され立っているため 0x8〜になっていますが値としてこのビットは無視し、この例の場合は 00000001 と読めます
1 : この値に更新するトランザクションの ID
2 : 以前の値に戻すための Undo ログの役割を担うロールバックセグメントへのポインタ
3: 3 以降キー以外のカラムの値が列挙されます

この例では、k = 1 の行に lock_mode X locks rec but not gap で単純行ロックでの排他ロックがかかっていることがわかります。

範囲に対する FOR UPDATE のロックを観察

まだこのトランザクションは実行したまま、次に k > 100 の範囲に対して FOR UPDATE のロックをかけてロックモニターで見てみます

mysql> SELECT * FROM t2 WHERE k > 100 FOR UPDATE;
Empty set (0.00 sec)

前述した k = 1 に対する単純ロックに加えて以下が追加されています

RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 9490 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;;

タイプは lock_mode X なので 前方ギャップロック + 単純行ロックがセットになったネクストキーロック であることがわかります。その場所ですが、キーが supremum という特殊な行になっています。

supremum は ∞ というか上限の値を表現した行で、InnoDB が下限の値 infimum とともに自動的に割り当てた仮想的な行です。k > 100 の FOR UPDATE によって、その仮想的な上限の行に前方ギャップとセットのネクストキーロックがかかっていることがわかります。

+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    0 |
| 100 |    0 |
| gap        |   <== ↓の前方ギャップ
| supremum   |   <== ネクストキーロックはここ
+-----+------+