INSERT ON CONFLICT DO UPDATE が atomic じゃなかった

TL; DR

• PostgreSQL での UPSERT に該当する INSERT ON CONFLICT DO UPDATE が atomic な処理ではない。
  • 1レコードずつ行ロックされる
• 複数レコード追加/更新する際は、主キーなりでソートしておかないと、 multi-thread/process から実行すると dead lock に悩まされる。

環境

PostgreSQL 9.6.9 on AWS Aurora

例

こういうテーブルに

CREATE TABLE foo (
    key1 int primary key,
    key2 text
)

こんな感じでデータを投入しておく。

INSERT INTO foo (
    key1, key2
) VALUES (
    1, 'foo'
), (
    2, 'bar'
), (
    3, 'baz'
);

で、以下のクエリを同時に流し込むと、タイミングによっては dead lock する。

クエリ1：

INSERT INTO foo (
    key1, key2
) VALUES (
    1, 'foobar'
), (
    3, 'qux'
) ON CONFLICT (key1)
DO UPDATE SET
    key2 = excluded.key2
WHERE
    foo.key1 = excluded.key1
;

クエリ2：

INSERT INTO foo (
    key1, key2
) VALUES (
    3, 'qux'
), (
    1, 'foobar'
) ON CONFLICT (key1)
DO UPDATE SET
    key2 = excluded.key2
WHERE
    foo.key1 = excluded.key1

VALUE の順序が逆になってるのがミソなんだけど、たぶん内部的には

1. クエリ1が key1=1 の行を UPDATE してロック
2. クエリ2が key1=3 の行を UPDATE してロック
3. クエリ1が key1=3 の行を更新しようとしてクエリ2のロック解除を待つ
4. クエリ2が key1=1 の行を更新しようとしてクエリ1のロック解除を待つ
5. 無事 dead lock 発生

て感じになってるんだと思う。

半信半疑だったんだけど、試しに key1 でソートしてからクエリ流すようにしてからは1度も dead lock が発生していないので、おそらくそうなんだろうなーと思ってる。