はじめに

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）