TL; DR

MySQL/Postgres とも，

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

Postgres に関しては，

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

動機

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

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

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

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

検証

問題設定

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

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

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

検証 1

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

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

検証 2

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

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

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

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

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

-- 前半スリープ箇所 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
);
-- 先行者のコミット直前のスリープ箇所

-- 前半スリープ箇所 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 を使い，エラーからのリトライ処理を書きましょう。

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

Subquery WHERE with READ COMMITTED will be broken:

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

MySQL

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

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

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

Subquery WHERE with READ UNCOMMITTED will be broken:

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

Subquery WHERE with READ COMMITTED will be broken:

↑ これは Postgres と同じ動き

Subquery WHERE with REPEATABLE READ will get deadlocks:

↑ ふむ。

Subquery WHERE with SERIALIZABLE will get deadlocks:

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

感想

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

結論 （再掲）

MySQL/Postgres とも，

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

Postgres に関しては，

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

予告

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