しおしお

IntelliJ IDEAのことなんかを書いてます

PostgreSQLでREAD COMMITEDとdelete->insertの組み合わせの問題のメモ

PostgreSQLのREAD COMMITEDでdelete->insertを使った場合、あるはずのレコードに対する削除がされずにinsertで一意制約違反が発生するらしい。。。

テーブルの状態

postgres=> select * from hoge;
 id
----
  1

2つのトランザクションで以下のSQLを実行

delete from hoge where id = 1;
insert into hoge values (1);

結果

先に実行されたトランザクションの処理は当然成功する。

postgres=> begin
postgres-> ;
BEGIN
postgres=> delete from hoge where id = 1;
DELETE 1
postgres=> insert into hoge values (1);
INSERT 0 1
postgres=> commit;
COMMIT

後に実行されたトランザクションは、deleteがロックを解除されるのを待ち、delete -> insertが成功されると思っていたが異なる結果となった。
結果は以下のログのようにdelete処理では何も削除されずに、insertで一意制約違反となる。

postgres=> begin;
BEGIN
postgres=> delete from hoge where id = 1;
DELETE 0
postgres=> insert into hoge values (1);
ERROR:  重複キーが一意性制約"hoge_pkey"に違反しています
DETAIL:  キー (id)=(1) はすでに存在します

原因

以下に詳しく書いてありますが、delete対象のレコードに対するロックの取得待ちをしていて、そのレコードがなくなったためにこのような挙動になるようです。
https://dba.stackexchange.com/questions/27688/locking-issue-with-concurrent-delete-insert-in-postgresql

READ COMMITEDでdelete->insertは選択しちゃダメなのですね。