MySQL と互換 DB のロックに関して解説する入門シリーズ第８回の今回は前方ギャップロックとレコードロックがセットになったネクストキーロックが、ORDER BY によるソートの向き、あるいは降順インデックスでどのような変化があるのか？ について解説します。
今回解説するのはロックの挙動の細かいところですが、例えば「既存データはこれから作業するので排他ロックしつつ、新規データの挿入は邪魔せず受け入れたい」といった場合にはロック範囲の境界部分が意図通りになっているかは重要なポイントになります。
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックでデッドロック
★ 第１０回 ロック読み取りは READ COMMITTED
★ 第１１回 Aurora と MySQL の違い
★ 第１２回 TiDB と MySQL の違い
t1 テーブルと t1locks ビュー
本記事では以下の t1 テーブルを使って解説します。
[1]> select * from t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
ロックの観測は performance_schema.data_locks テーブルを参照しますが、前述の t1 テーブルに固定して必要な情報だけアクセスすればよいため、利便性のため専用のビュー t1locks を作成し使用します。この t1locks ビューについて詳しくは 第６回 performance_schema で紹介していますのでそちらの記事もご参照ください。
不在値へのロック読取りによるネクストキーロック
トランザクションを開始し、不在値 k = 5 への共有のロック読み取りを実行してみます。
[1]> start transaction;
[1]> select * from t1 where k = 5 lock in share mode;
Empty set (0.00 sec)
2つ目の端末で k = 6 に対して INSERT します。
[2]> insert into t1 values (6, 2);
^C^C -- query aborted
ブロックされました。t1locks ビューでロックを観察します。
+-----------------+------------+-----------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-----------------+------------+-----------+-----------+
| 408608110669016 | PRIMARY | S,GAP | 10 |
+-----------------+------------+-----------+-----------+
ロック読取りをしたのは k = 5 行でしたが、k = 10 行の共有の前方ギャップロック S,GAP が取得されていることがわかります。
このように不在の行に対してロックを取得してしまった場合、範囲に対してのギャップロックとなり、その意図は無くてもデータの追加が禁止状態になりますので注意が必要です。
前方ギャップロックは ORDER BY の影響を受ける
ネクストキーロックの一部や、単体でのギャップロックは全て行の「前方」側のギャップに対して設置されます。この「前方」は昇順の場合だと数値が小さい方が前になります。SELECT の結果が逆順にソートされている場合はどうなるでしょうか？ あくまで最後の結果表の上でソートされているだけで、直感的にはロックには影響しないように思えます。
まず普通に k > 10 の範囲で排他ロックをかけてみます。
[1]> start transaction;
Query OK, 0 rows affected (0.00 sec)
[1]> select * from t1 where k > 10 for update;
+-----+------+
| k | v |
+-----+------+
| 100 | 0 |
+-----+------+
t1locks ビューでロックを確認すると
[1]> select * from t1locks;
+-------+------------+-----------+------------------------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+------------------------+
| 17173 | PRIMARY | X | supremum pseudo-record |
| 17173 | PRIMARY | X | 100 |
+-------+------------+-----------+------------------------+
２つ X のネクストキーロックが取得されています。再度トランザクション開始からやり直し、今度は ORDER BY k DESC 付で実行します。
[1]> select * from t1 where k > 10 order by k desc for update;
+-----+------+
| k | v |
+-----+------+
| 100 | 0 |
+-----+------+
[1]> select * from t1locks;
+-------+------------+-----------+------------------------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+------------------------+
| 17174 | PRIMARY | X | supremum pseudo-record |
| 17174 | PRIMARY | X | 10 | <=== 増えたネクストキーロック
| 17174 | PRIMARY | X | 100 |
+-------+------------+-----------+------------------------+
ORDER BY k DESC が付いたことで k = 10 行のネクストキーロックが追加されました！
「キーの値が大きい範囲だけをロックし、小さい方は阻害したくない、さらに逆順にソートし LIMIT 1 することで最大値の１行だけを得たい」といった場合に類似のパターンのクエリとなり、条件に記述した境界値より下の範囲にもロックがかかるので注意が必要です。
降順インデックスを試してみる
t1 テーブルで v に k の2倍の値が入るように UPDATE します。
[1]> update t1 set v = k * 2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
[1]> select * from t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 2 |
| 10 | 20 |
| 100 | 200 |
+-----+------+
3 rows in set (0.00 sec)
さらに降順インデックス di を v の列に作成します。
[1]> alter table t1 add index di (v desc);
SHOW INDEX で確認できますが、インデックスのソート順序は Collation として A, D または NULL で記載されます。
[1]> show index from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: k
Collation: A <=== 普通の昇順
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: di
Seq_in_index: 1
Column_name: v
Collation: D <=== 降順インデックス
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
v > 2 で v を SELECT し EXPLAIN でどのインデックスを使うのか確認します。
[1]> select v from t1 where v > 2;
+------+
| v |
+------+
| 200 |
| 20 |
+------+
2 rows in set (0.00 sec)
[1]> explain select v from t1 where v > 2;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | NULL | range | di | di | 5 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
狙い通り di インデックスを使うことが確認できましたので、トランザクションを開始し、 v > 20 への FOR UPDATE でどのようなロックがかかっているか確認します。
[1]> select v from t1 where v > 20 for update;
+------+
| v |
+------+
| 200 |
+------+
[1]> select * from t1locks;
+-------+------------+---------------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+---------------+-----------+
| 17200 | di | X | 20, 10 | キー値、プライマリ値
| 17200 | di | X | 200, 100 |
| 17200 | PRIMARY | X,REC_NOT_GAP | 10 |
| 17200 | PRIMARY | X,REC_NOT_GAP | 100 |
+-------+------------+---------------+-----------+
インデックス di の v = 200, 20 の２行でネクストキーロック X と該当するプライマリキーの２行で排他レコードロック X,REC_NOT_GAP がかかっています。
この状態で k = 101, v = 202 で INSERT してみましょう。降順の v = 200 のネクストキーロックは ∞ > v > 200 の前方ギャップを含むので失敗する筈です。
[2]> insert into t1 values (101, 202);
^C^C -- query aborted
想定通りブロックされました。降順インデックスでの前方方向を ↓ の記号で表示すると以下のようなロックになっています。
+-----+------+
| k | v |
+-----+------+
| 1 | 2 |
| 10 | 20 | v の ↓X と k の X,REC_NOT_GAP
| 100 | 200 | v の ↓X と k の X,REC_NOT_GAP
+-----+------+
インデックスを使うクエリのロックをすり抜ける INSERT
前述からのロックが存在しているトランザクションの状況で k = 101, v = 0 の INSERT はどうでしょうか？
[2]> insert into t1 values (101, 0);
Query OK, 1 row affected (0.00 sec)
追加出来ました！
例えば「プライマリキーの k が auto increment で付与されており、v が時刻データで、関心はもっとも最新時刻の方にあるため v には降順のインデックスが設定されている。というケースを想像してください。
追加データを抑止しつつ最新値付近で作業するために、 v > 20 のように値の大きい方に向かって範囲でロックした場合、INSERT するデータ v が何かの理由で誤っていた場合に、ロックをすり抜けて新しい行の追加が出来てしまいます。
今回のまとめ
★ ORDER BY によってロックのとり方が変わる
★ 降順インデックスの列での 前方ギャップ は値の大きい方
★ 昇順インデックスで値の大きな方に開いた条件 （k > 100 など）で SELECT し、結果を逆順で ORDER BY したロック読取りは、条件外の値の小さい方にもロックがかかる
★ プライマリキーではない列のインデックスを使って範囲ロックした場合、INSERT 時のインデックス相当のデータが誤っていればすり抜けて行は追加出来る
直感的には結果表示にしか関与していないと思われる ORDER BY やインデックスの降順によってロックの挙動も変わってくるところ、いかがでしたでしょうか？ 次回第９回は「共有ロックを使うと発生しやすいデッドロック」について解説いたします！ デッドロックで一番多い原因と思われるところですのでご期待ください！
