WHERE 条件のフィールドを UPDATE するのって，明示的にロックしてなくても安全？全パターン調べてみました！

# TL; DR

MySQL/Postgres とも，

- MVCC アーキテクチャの恩恵で， `SELECT` と `UPDATE` は基本的には競合しない。
- **単一レコードのシンプルな `UPDATE` でも排他ロックされ**，排他ロック中のレコードへの `UPDATE` での変更操作は **<font color=green><ins>トランザクション分離レベルによらず</ins></font>** ブロックされる。<font color=green><ins>**`UPDATE` 文に含まれる `WHERE` 句での検索もブロックされ**</ins></font>，これはブロックされない `SELECT` による検索とは別扱いになる。 
- <font color=red>**但し `UPDATE` 文の `WHERE` 句上で，更新対象をサブクエリの `SELECT` から自己参照している場合は例外。トランザクション分離レベルを `REPEATABLE READ` 以上にして，競合エラーからの復帰処理を書かなければならない。**</font>

Postgres に関しては，

- `REPEATABLE READ` 以上では， MySQL よりも積極的・予防的に競合エラーを起こすようになっている。上記のように `WHERE` 句に含まれるサブクエリの `SELECT` から自己参照が発生しない場合， **`READ COMMITTED`** にしておくのが最適解。

# 動機

この質問からのツイートがきっかけ。

https://dba.stackexchange.com/questions/175228/is-a-single-update-statement-with-where-transaction-safe/175247

https://twitter.com/mpyw/status/1543116296350748673

そして @zyake さんからのツッコミ。

https://twitter.com/zyake/status/1543128596822167553

Oracle ではそうなんだ！じゃあ MySQL と Postgres は…？と当然気になるわけですよね。結論から言うと **元の StackExchange サイト上の最も投票されている回答が完全に間違っていました**。やっぱり一次ソースが曖昧なときは実験しないとダメですね。

そして，単一の `UPDATE` でもロックが掛かることを完全に知らなかった故に実験できないと思っていたのですが， @zyake さんから検証のスクショを送っていただき，実験可能なことに気づいたので，考えられそうな**全パターンを検証**してみることにしました。

https://twitter.com/zyake/status/1543222822595543040

# 検証

## 問題設定

数量限定商品の在庫管理を考えます。 `products` というテーブルがあり， `remaining_amount` というフィールドで残量を管理しています。 （`CHECK` 制約を使えというのはその通りなのですが），以下のようなクエリで在庫をデクリメントしたとき，マイナスの値になってしまう可能性があるか？という点が非常に気になっていました。

```sql
UPDATE products 
SET remaining_amount = remaining_amount - 1
WHERE id = 1
  AND remaining_amount > 0
```

要するに，以下のような状況があり得るかどうか？というところです。

| A | B |
|:---|:---|
| `id` が 1 のレコードの `remaining_amount` は `> 0` を満たしている！今から更新しよ！ | |
| | `id` が 1 のレコードの `remaining_amount` は `> 0` を満たしている！今から更新しよ！ |
| デクリメントするね，元の在庫が 1 だったから 0 にするよ！| |
| | デクリメントするね，元の在庫が 0 だったから -1 にするよ！|

## 検証 1

シェルを 2 つ起動し， 2 つから `mysql` コマンドでデータベースに接続します。**単一レコードの更新でもロックされることが分かっているので**，ロックの効力を任意タイミングまで延長するために，明示的にトランザクションを開始します。

| A                                                                                         | B                                                                                         |
|:------------------------------------------------------------------------------------------|:------------------------------------------------------------------------------------------|
| `BEGIN;`                            |`BEGIN;`                            |
| `UPDATE products SET remaining_amount = remaining_amount - 1 WHERE remaining_amount > 0;` |                                                                                           |
| Query OK, 1 row affected<br>Rows **matched: 1  Changed: 1**  Warnings: 0                  |                                                                                           |
|                                                                                           | `UPDATE products SET remaining_amount = remaining_amount - 1 WHERE remaining_amount > 0;` |
|                                                                                           | **<font color=green>ここでブロックされます！</font>**                                                                            |
| `COMMIT;`                                                                                 |                                                                                           |
|                                                                                           | Query OK, 0 rows affected<br>Rows **matched: 0  Changed: 0**  Warnings: 0                 |
|                                                                                           | `COMMIT;`                                                                                 |

A がコミットを完了するまで， B からの当該レコードへ `UPDATE` 文でのアクセスがブロックされていることが判明しました。割愛しますが， MySQL はすべてのトランザクション分離レベルで期待通りの動作となりました。

## 検証 2

ここで気になるのが，以下のような点。

- Postgres ではどうなの？
- WHERE 句が持つサブクエリの SELECT から更新対象を選択したらどうなるの？
- SET 句のデクリメントをサブクエリの SELECT から取得した値基準の計算に書き換えたらどうなるの？
- タイミングがもう少しずれたらどうなるの？

全て手作業で調べるのは困難だと思ったので，自動で調査するためのシェルスクリプトを<del>かなり雑ですが</del>整備しました！

https://github.com/mpyw-yattemita/mysql-postgres-update-locking-and-isolation-levels

- まず，先行者は 1 秒，後続者は 2 秒スリープすることでズレを作ります。ズレる場所は以下の3通りとします。
  -  `WHERE` で絞り込む前
  -  `SET` で書き換えを始める前
  -  `SET` で書き換えて確定させる直前
- 最後のコミット直前に，先行者が 2 秒スリープする場合とスリープしない場合を設け，後続者がレコードに触るタイミングを以下の2通りに分岐させます。
  - 先行者がコミットする前
  - 先行者がコミットした後
- 全てのトランザクションモードで試します。
  - MySQL は `READ UNCOMMITTED` `READ COMMITTED` `REPEATABLE READ` `SERIALIZABLE` 
  - Postgres は `READ COMMITTED` `REPEATABLE READ` `SERIALIZABLE` 
- 以下の 3 通りのクエリでを試します。

```sql
-- 前半スリープ箇所 1
UPDATE products
SET 
-- 前半スリープ箇所 2
remaining_amount=remaining_amount-1
-- 前半スリープ箇所 3
WHERE id=1 AND remaining_amount>0;
-- 先行者のコミット直前のスリープ箇所
```

```sql
-- 前半スリープ箇所 1
UPDATE products
-- 前半スリープ箇所 2
SET remaining_amount=remaining_amount-1
-- 前半スリープ箇所 3
WHERE EXISTS(
  SELECT * FROM (
    SELECT * FROM products WHERE id=1 AND remaining_amount>0
  ) tmp
);
-- 先行者のコミット直前のスリープ箇所
```

```sql
-- 前半スリープ箇所 1
UPDATE products
SET
-- 前半スリープ箇所 2
remaining_amount=(SELECT remaining_amount FROM (SELECT * FROM products WHERE id=1) tmp2)-1
-- 前半スリープ箇所 3
WHERE id=1 AND remaining_amount>0;
-- 先行者のコミット直前のスリープ箇所
```

で，これを自動で調べた結果がこちら！

### Postgres

- **シンプルな `UPDATE` には， `READ COMMITTED` を使いましょう。** 自己参照するサブクエリが `SET` で使われていても問題ありません。
- **自己参照するサブクエリが `WHERE` で使われる場合，  `REPEATABLE READ` を使い，エラーからのリトライ処理を書きましょう。**

|                 |        Simple         |    Subquery WHERE     |     Subquery SET      |
|:----------------|:---------------------:|:---------------------:|:---------------------:|
| READ COMMITTED  |           ✅           |     ❌ 5/6 Broken      |           ✅           |
| REPEATABLE READ | ❗ Serialization Error | ❗ Serialization Error | ❗ Serialization Error |
| SERIALIZABLE    | ❗ Serialization Error | ❗ Serialization Error | ❗ Serialization Error |

↑ Postgres くんめちゃ慎重やな！

#### Subquery WHERE with `READ COMMITTED` will be broken:

| [B] Latter ＼ [A] Former | Before-Read Delay | Pre-Write Delay | Post-Write Delay |
|:------------------------|:-----------------:|:---------------:|:----------------:|
| Act before A's commit   |     ❌ Broken      |    ❌ Broken     |     ❌ Broken     |
| Act after A's commit    |         ✅         |    ❌ Broken     |     ❌ Broken     |

↑トランザクション中の「B からの初めてのアクセス」が「A のコミット後」であれば，B はちゃんと最新の値を読み取れる模様…これはまあ当然といえば当然。

### MySQL

8.x 系で調査しましたが， InnoDB である限り 5.7 ぐらいでもほぼ同じ動作になるかと思います。

- **シンプルな `UPDATE` には， どのトランザクションモードを使っても構いません。** こだわりがなければ負荷の軽い `READ UNCOMMITTED` か `READ COMMITTED` にしておくのがおすすめです。自己参照するサブクエリが `SET` で使われていても問題ありません。
- **自己参照するサブクエリが `WHERE` で使われる場合，  `REPEATABLE READ` を使い，デッドロックからのリトライ処理を書きましょう。**

|                  | Simple | Subquery WHERE | Subquery SET |
|:-----------------|:------:|:--------------:|:------------:|
| READ UNCOMMITTED |   ✅    |  ❌ 4/6 Broken  |      ✅       |  
| READ COMMITTED   |   ✅    |  ❌ 5/6 Broken  |      ✅       |
| REPEATABLE READ  |   ✅    | ❗ 1/6 Deadlock |      ✅       |
| SERIALIZABLE     |   ✅    | ❗ 1/6 Deadlock |      ✅       |

↑やっぱり Postgres よりは大雑把！でも思ったよりは乱れてない

#### Subquery WHERE with `READ UNCOMMITTED` will be broken:

| [B] Latter ＼ [A] Former | Before-Read Delay | Pre-Write Delay | Post-Write Delay |
|:------------------------|:-----------------:|:---------------:|:----------------:|
| Act before A's commit   |         ✅         |    ❌ Broken     |     ❌ Broken     |
| Act after A's commit    |         ✅         |    ❌ Broken     |     ❌ Broken     |

↑ MySQL の特権 `READ UNCOMMITTED` にちょっとだけ期待していたが… 書き込み処理に入っている最中（読み取りが完了してしまっているタイミング）では意図的なダーティリードもできない模様。ちょっと残念？

#### Subquery WHERE with `READ COMMITTED` will be broken:

| [B] Latter ＼ [A] Former | Before-Read Delay | Pre-Write Delay | Post-Write Delay |
|:------------------------|:-----------------:|:---------------:|:----------------:|
| Act before A's commit   |     ❌ Broken      |    ❌ Broken     |     ❌ Broken     |
| Act after A's commit    |         ✅         |    ❌ Broken     |     ❌ Broken     |

↑ これは Postgres と同じ動き

#### Subquery WHERE with `REPEATABLE READ` will get deadlocks:

| [B] Latter ＼ [A] Former | Before-Read Delay | Pre-Write Delay | Post-Write Delay |
|:------------------------|:-----------------:|:---------------:|:----------------:|
| Act before A's commit   |         ✅         |        ✅        |        ✅         |
| Act after A's commit    |         ✅         |        ✅        |    ❗ Deadlock    |

↑ ふむ。

#### Subquery WHERE with `SERIALIZABLE` will get deadlocks:

| [B] Latter ＼ [A] Former | Before-Read Delay | Pre-Write Delay | Post-Write Delay |
|:------------------------|:-----------------:|:---------------:|:----------------:|
| Act before A's commit   |         ✅         |        ✅        |    ❗ Deadlock    |
| Act after A's commit    |         ✅         |        ✅        |        ✅         |

↑ ふむ。 `REPEATABLE READ` と `SERIALIZABLE` のデッドロック条件が若干違うのが興味深い

# 感想

- めっちゃ面倒臭かった。スクリプト書いてなかったら発狂してたと思う
- Postgres はお行儀がとてもいい！（ちょっと慎重すぎ…？）
- MySQL のことは Twitter でボロクソ言ってたけど意外とちゃんと動いてて安心しました。ごめんね。

# 結論 （再掲）

> MySQL/Postgres とも，
> 
> - MVCC アーキテクチャの恩恵で， `SELECT` と `UPDATE` は基本的には競合しない。
> - **単一レコードのシンプルな `UPDATE` でも排他ロックされ**，排他ロック中のレコードへの `UPDATE` での変更操作は **<font color=green><ins>トランザクション分離レベルによらず</ins></font>** ブロックされる。<font color=green><ins>**`UPDATE` 文に含まれる `WHERE` 句での検索もブロックされ**</ins></font>，これはブロックされない `SELECT` による検索とは別扱いになる。 
> - <font color=red>**但し `UPDATE` 文の `WHERE` 句上で，更新対象をサブクエリの `SELECT` から自己参照している場合は例外。トランザクション分離レベルを `REPEATABLE READ` 以上にして，競合エラーからの復帰処理を書かなければならない。**</font>
> 
> Postgres に関しては，
> 
> - `REPEATABLE READ` 以上では， MySQL よりも積極的・予防的に競合エラーを起こすようになっている。上記のように `WHERE` 句に含まれるサブクエリの `SELECT` から自己参照が発生しない場合， **`READ COMMITTED`** にしておくのが最適解。

:::note warn
ここでは， 1 レコードに `WHERE` 条件で絞り込める場合のみにフォーカスしましたが，バルクアップデートになってしまう場合はデッドロックが起こるパターンがさらに増えてしまいます。ご注意ください。
:::

https://twitter.com/KentarouTakeda/status/1543199538953207808

https://zenn.dev/tmtms/articles/202103-mysql-deadlock

# 予告

ここで吟味した，在庫管理方式の掘り下げは後ほど青い方のサイトに書く予定です！