[MySQL8.0] SKIP LOCKEDを試してみる

## はじめに
MySQL8.0からSKIP LOCKEDが追加されたので色々試してみました。
SKIP LOCKEDを使うとロック対象の行をスキップしてクエリが走ります。

バージョン
```cli
$ 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    |                |
+---------+-------------+------+-----+---------+----------------+
```


### 普通のクエリ

```sql
SELECT * FROM samples;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
|  2 | name2 |       1 |
|  3 | name2 |       0 |
|  4 | name3 |       0 |
+----+-------+---------+
```

### FOR UPDATEを試す

id=1をロックする

```sql
BEGIN;
SELECT * FROM samples WHERE id=1 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
+----+-------+---------+

```

この状態で、

```sql
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カラムを変更しようとしても同じです。（行ロックの確認）

```sql
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文でレコード確認すると、

```sql
SELECT * FROM samples FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
タイムアウトしますが、`FOR UPDATE`を外すと、

```sql
SELECT * FROM samples;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
|  2 | name2 |       1 |
|  3 | name2 |       0 |
|  4 | name5 |       0 |
+----+-------+---------+
```

全て表示されます。

### SKIP LOCKED

SKIP LOCKEDでロックされたレコードがどうなるか確認します。

```sql
BEGIN;
SELECT * FROM samples WHERE id=1 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
+----+-------+---------+

```

ここで、SELECT文で全件検索すると、先程はタイムアウトしていましたが、`SKIP LOCKED`をつけると、
```sql
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をロックします。

```sql
SELECT * FROM samples WHERE is_true=true FOR UPDATE;
+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
|  2 | name2 |       1 |
+----+-------+---------+
```

この状態で、`SKIP LOCKED`つきで、全件クエリすると、
```sql
SELECT * FROM samples FOR UPDATE SKIP LOCKED;

Empty set (0.00 sec)
```
空で返ってきます。
これはインデックスがない列であるため全件検索が走り、走査されたレコードは全てロックされるからだと思います。多分 （細かい仕様とかはちょっと後で調べてみます）

ここでLIMIT使ってみます。

```sql
BEGIN;
SELECT * FROM samples WHERE is_true=true LIMIT 1 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
+----+-------+---------+
```
この状態で、、`SKIP LOCKED`つきで、全件クエリすると、

```sql
SELECT * FROM samples FOR UPDATE SKIP LOCKED;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  2 | name2 |       1 |
|  3 | name2 |       0 |
|  4 | name5 |       0 |
+----+-------+---------+
```
最初の1件を除いたレコードが取得できます。
LIMIT使うと、LIMITの分のレコードが見つかるとそこで検索が止まるから最初のレコード以外はロックされていないからですかね。
ちょっと検証の続きを行います。

#### 該当レコードない場合
LIMIT使ったとして、最初のクエリで該当するレコードがない場合はどうなるのか確かめました。

```sql
BEGIN;
SELECT * FROM samples WHERE name="not_exist_name" LIMIT 1 FOR UPDATE;

Empty set (0.00 sec)
```
存在しない名前でクエリしたので空で返ってきます。

```sql
SELECT * FROM samples FOR UPDATE SKIP LOCKED;

Empty set (0.00 sec)
```
存在しないレコードでクエリした場合には全ての列がロックされるようです。
これも全レコードに走査が走ったからですかね。

#### 該当レコード数がLIMIT以下の場合
一回今のレコードを確認します。
```sql
SELECT * FROM samples；

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
|  2 | name2 |       1 |
|  3 | name2 |       0 |
|  4 | name5 |       0 |
+----+-------+---------+
```

name2が2つあるのでLIMIT3でクエリしてみます。

```sql
BEGIN;
SELECT * FROM samples WHERE name="name2" LIMIT 3 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  2 | name2 |       1 |
|  3 | name2 |       0 |
+----+-------+---------+
```
2個しかないのでレコードは2つだけ返ってきます。
この状態で全件確認します。

```sql
SELECT * FROM samples FOR UPDATE SKIP LOCKED;

Empty set (0.00 sec)
```
レコードが空でした。
これもLIMITが3だと結局最後までレコードを走査するので、全てロックしてしまうのですかね。

#### 該当レコードがLIMITと同じの場合
name2をLIMIT 2でクエリしてみます。
```sql
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を追加してみます。
また、もう一回全レコード確認します。

```sql
SELECT * FROM samples;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  1 | name1 |       1 |
|  2 | name2 |       1 |
|  3 | name2 |       0 |
|  4 | name5 |       0 |
+----+-------+---------+
```

#### インデックスがない場合

インデックスがないnameでorder byしてみます。

```sql
BEGIN;
SELECT * FROM samples ORDER BY name desc LIMIT 1 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  4 | name5 |       0 |
+----+-------+---------+
```

このときはどうなるでしょう。

```sql
SELECT * FROM samples ORDER BY name desc LIMIT 1 FOR UPDATE SKIP LOCKED;

Empty set (0.00 sec)
```
```sql
SELECT * FROM samples FOR UPDATE SKIP LOCKED;

Empty set (0.00 sec)
```
最初の1つだけでも全部でも空です。
これはorder byするのに全レコード走査したからかと思います。
そのためDESCでもASCでも結果は同じでした。

#### インデックスがある場合
インデックスがはってあるid列で確認します。

```sql
BEGIN;
SELECT * FROM samples ORDER BY id desc LIMIT 1 FOR UPDATE;

+----+-------+---------+
| id | name  | is_true |
+----+-------+---------+
|  4 | name5 |       0 |
+----+-------+---------+
```
これの場合はどうなるでしょう。

```sql
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）