PostgreSQLにおける、削除行に対するロック獲得時の挙動

直感とは異なる挙動が観察されたため、記事として残します

環境

• RDB

  • PostgreSQL 13.3

  • トランザクション分離レベル read committed (デフォルト)

• 実験環境

  • IntelliJ IDEA 2024.3 (Ultimate Edition)

TLDR

PostgreSQLにおいて、トランザクション内で同一キーの行を delete - insert する場合、同時に実行される他のトランザクションからは当該の行が参照できない場合があります。

現象の説明

次のようなSQLを考えます。

SQL1

begin;

-- delete-insertによってデータを更新する
delete from users where id = 1;  -- id が p_key
insert into users (id, name) values (1, 'jane doe');

commit;

実験のためIntellijを使い、2つのセッション分のクエリコンソールを開きます。 これら2つをそれぞれセッション１、セッション２とします。

1. セッション１で5行目まで実行する。この時 delete によってロックが獲得される

2. セッション２で4行目まで実行する。するとセッション１で行がロックされているため、待ち状態になる

3. セッション１で7行目の commit まで実行する。これによりセッション１で獲得されていたロックが解放される

4. セッション１のロックが解放されたことでセッション２の delete が実行される

この時、4 の結果セッション２では delete が空振りし、insert を実行するもすでに id = 1 の行が存在するため、 [23505] ERROR: duplicate key value violates unique constraint "users_pkey" となります。

比較実験

MySQLの場合

MySQL 8.0.28 で同様の実験を行ったところ、エラーは発生しませんでした。データの更新結果はcommitを後に実行している、セッション２の結果が保存されます。 MySQLのトランザクション分離レベルは repeatable read(デフォルト) です。

PostgreSQLでトランザクション分離レベルをrepeatable readとした場合

ひょっとしたら分離レベルに依存した挙動なのでは？ということでPostgreSQLにて分離レベルをrepeatable readとして実験を行いました。 結果前述4のステップで、以下のようなエラーが返されました。

[40001] ERROR: could not serialize access due to concurrent update

これはPostgreSQLの仕様によるもので、 リピータブルリードトランザクションでは、トランザクションが開始された後に別のトランザクションによって更新されたデータは変更またはロックすることができないため とあります。 https://www.postgresql.jp/docs/9.4/transaction-iso.html

MySQLでトランザクション分離レベルをread committedとした場合

MySQLではrepeatable readの場合と挙動は変わらず、1行返却されました。

select for updateによるロック獲得の場合

PostgreSQLにおいて、delete 文ではなくselect for updateでロックを獲得した場合の挙動についても確認しました。

SQL2

begin;

-- for update により、行ロックを取得する
select * from test where id = 1 for update;

-- delete-insertによってデータを更新する
delete from test where id = 1;
insert into test (id, name) values (1, 'jane doe');

commit;

この場合、セッション２は select for update の行で待ち状態となり、セッション１のcommit後、セッション2の select for update が実行され 0行 の返却となりました。

終わりに

今回、別の問題を調査していく中でたまたまこの現象に遭遇しました。 個人的には全く想定していない挙動でした。

PostgreSQLでは削除データはすぐには物理削除されませんが、この辺りが関係しているような気もします。 https://www.postgresql.jp/docs/9.4/sql-vacuum.html

改めてこちらの記事はPostgreSQLでの挙動になります。 前述の通りMySQLでは異なる挙動となりましたので、RDBMSの実装に依存するようです。

ご興味があれば、ぜひお手持ちの環境でも試してみてください。