はじめに
MySQL8.0からSKIP LOCKEDが追加されたので色々試してみました。
SKIP LOCKEDを使うとロック対象の行をスキップしてクエリが走ります。
バージョン
$ mysql --version
mysql Ver 8.0.28 for Linux on x86_64
検証
samplesテーブルを作成して試してみます。
実際に使いたいのはis_trueカラムのようなBooleanですが、なんかBooleanだけだと変な感じなので文字列のカラムも確かめます。
CREATE TABLE samples (id int AUTO_INCREMENT PRIMARY KEY, name varchar(10), is_true tinyint);
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| is_true | tinyint(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
普通のクエリ
SELECT * FROM samples;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name3 | 0 |
+----+-------+---------+
FOR UPDATEを試す
id=1をロックする
BEGIN;
SELECT * FROM samples WHERE id=1 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
+----+-------+---------+
この状態で、
UPDATE samples set name="name5" WHERE id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
id=1のレコードを変更しようとしたら、タイムアウトになりました。
ちなみにupdate samples set is_true=false where id=1;と、is_trueカラムを変更しようとしても同じです。（行ロックの確認）
UPDATE samples set name="name5" WHERE id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ロックしていないid=4のレコードはupdateできました。
id=1のレコードだけがちゃんとロックされています。
また、SELECT文でレコード確認すると、
SELECT * FROM samples FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
タイムアウトしますが、FOR UPDATEを外すと、
SELECT * FROM samples;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name5 | 0 |
+----+-------+---------+
全て表示されます。
SKIP LOCKED
SKIP LOCKEDでロックされたレコードがどうなるか確認します。
BEGIN;
SELECT * FROM samples WHERE id=1 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
+----+-------+---------+
ここで、SELECT文で全件検索すると、先程はタイムアウトしていましたが、SKIP LOCKEDをつけると、
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name5 | 0 |
+----+-------+---------+
このようにロックされてるid=1の行以外の行が表示されます。
SKIP LOCKED + LIMIT
他の条件も試してみます。
まずは、is_true=trueをロックします。
SELECT * FROM samples WHERE is_true=true FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
| 2 | name2 | 1 |
+----+-------+---------+
この状態で、SKIP LOCKEDつきで、全件クエリすると、
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
空で返ってきます。
これはインデックスがない列であるため全件検索が走り、走査されたレコードは全てロックされるからだと思います。多分 （細かい仕様とかはちょっと後で調べてみます）
ここでLIMIT使ってみます。
BEGIN;
SELECT * FROM samples WHERE is_true=true LIMIT 1 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
+----+-------+---------+
この状態で、、SKIP LOCKEDつきで、全件クエリすると、
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name5 | 0 |
+----+-------+---------+
最初の1件を除いたレコードが取得できます。
LIMIT使うと、LIMITの分のレコードが見つかるとそこで検索が止まるから最初のレコード以外はロックされていないからですかね。
ちょっと検証の続きを行います。
該当レコードない場合
LIMIT使ったとして、最初のクエリで該当するレコードがない場合はどうなるのか確かめました。
BEGIN;
SELECT * FROM samples WHERE name="not_exist_name" LIMIT 1 FOR UPDATE;
Empty set (0.00 sec)
存在しない名前でクエリしたので空で返ってきます。
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
存在しないレコードでクエリした場合には全ての列がロックされるようです。
これも全レコードに走査が走ったからですかね。
該当レコード数がLIMIT以下の場合
一回今のレコードを確認します。
SELECT * FROM samples；
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name5 | 0 |
+----+-------+---------+
name2が2つあるのでLIMIT3でクエリしてみます。
BEGIN;
SELECT * FROM samples WHERE name="name2" LIMIT 3 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 2 | name2 | 1 |
| 3 | name2 | 0 |
+----+-------+---------+
2個しかないのでレコードは2つだけ返ってきます。
この状態で全件確認します。
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
レコードが空でした。
これもLIMITが3だと結局最後までレコードを走査するので、全てロックしてしまうのですかね。
該当レコードがLIMITと同じの場合
name2をLIMIT 2でクエリしてみます。
BEGIN;
SELECT * FROM samples WHERE name="name2" LIMIT 2 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 2 | name2 | 1 |
| 3 | name2 | 0 |
+----+-------+---------+
今までの検証では、LIMIT 2でname2がid=3のところで全て見つかるので、最後のレコードはロックされないと思います。
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 4 | name5 | 0 |
+----+-------+---------+
予想通りですね。
色々検証しましたが、インデックスはってない列をクエリすると全件検索がはしるので、そのレコードに到達するまでの行を全てロックしてしまうようです。
要は、SELECT * FROM samples WHERE name="name5" LIMIT 1 FOR UPDATE;このクエリを実行すると
id=1 => Locked => continue query
id=2 => Locked => continue query
id=3 => Locked => continue query
id=4 => Locked => stop query
という感じになり、該当のレコードが全て発見される場合にはそれまでに調べたレコードは全て、つまり今回の場合は全レコードがロックされます。
SELECT * FROM samples WHERE name="name2" LIMIT 2 FOR UPDATE;の場合には、
id=1 => Locked => continue query
id=2 => Locked => continue query
id=3 => Locked => stop query
id=4
LIMITで件数が決まっており、その件数を見つけた時点で走査が止まるので、走査されていないレコードはロックされていない状態になるようです。
今回の場合は主キーのidがincrementなので順番に走査されてますが、UUIDの場合はランダムで走査される可能性があります（この辺の検索の流れの仕様は分かっていないので、また調べたいと思います。）
SKIP LOCKED + LIMIT + ORDER BY
order byを追加してみます。
また、もう一回全レコード確認します。
SELECT * FROM samples;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 1 | name1 | 1 |
| 2 | name2 | 1 |
| 3 | name2 | 0 |
| 4 | name5 | 0 |
+----+-------+---------+
インデックスがない場合
インデックスがないnameでorder byしてみます。
BEGIN;
SELECT * FROM samples ORDER BY name desc LIMIT 1 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 4 | name5 | 0 |
+----+-------+---------+
このときはどうなるでしょう。
SELECT * FROM samples ORDER BY name desc LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
SELECT * FROM samples FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
最初の1つだけでも全部でも空です。
これはorder byするのに全レコード走査したからかと思います。
そのためDESCでもASCでも結果は同じでした。
インデックスがある場合
インデックスがはってあるid列で確認します。
BEGIN;
SELECT * FROM samples ORDER BY id desc LIMIT 1 FOR UPDATE;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 4 | name5 | 0 |
+----+-------+---------+
これの場合はどうなるでしょう。
SELECT * FROM samples ORDER BY id desc LIMIT 1 FOR UPDATE SKIP LOCKED;
+----+-------+---------+
| id | name | is_true |
+----+-------+---------+
| 3 | name2 | 0 |
+----+-------+---------+
id=4のレコードだけロックされてるようですね。
Primaryキー以外
nameカラムにインデックスを追加し、同じことを試しましたがレコードが空で返ってきました。
Primaryキー以外はインデックスはっても結局全レコードを走査するのかもしれません。
あとはPrimaryキーというだけでなく、Auto Incrementも影響してるかもです。。。
この辺は注意が必要です。
まとめ
インデックスない列を単純にSELECT FOR UPDATEすると全件検索されて全てのレコードがロックされちゃうので気をつける。（LIMITつけてその件数内で確定すればOK）