AUTOINCREMENTが入れ替わる例。

postgres.md

システム間で差分連携しているとき、差分抽出にシリアル値を使うと取りこぼします。

RDBのシリアル値(MySQLのAUTOINCREMENT、PostgreSQLのSERIAL)はトランザクション分離をまたいでカウントアップするけれど、トランザクションがロールバックされるとそのシリアル値は消滅するし、コミット時刻が遅くなる場合、シリアル値でのソートとコミットされた時刻ソートは異なる結果になるよ、という例です。

graph LR;
    ST(start) --> I1
    ST(start) --> I2
    I1 --> I2
    subgraph TR1[transaction 1]
       I1[insert ID=1] ==> C1[commit]
    end
    subgraph TR2[transaction 2]
       I2[insert ID=2] --> C2[commit]
    end
    subgraph diff
      ST --> D1 --> D2
      C2 --> D1[diff 1回目]
      D1 --> C1
      C1 --> D2[diff 2回目]
    end

Loading

再現手順

PostgreSQLを起動する

docker run -d -e POSTGRES_PASSWORD=password postgres
d79b

一つ目のターミナルでトランザクション内でレコードを作成

docker exec -it d79b psql -U postgres
postgres=!# CREATE TABLE dual (id SERIAL PRIMARY KEY, name VARCHAR);
postgres=!# \set AUTOCOMMIT off
postgres=!# INSERT INTO dual (name) SET ('AAA');  ← ID1が発番される

別のターミナルでレコードを作成

docker exec -it d79b psql -U postgres
postgres=!# INSERT INTO dual (name) SET ('BBB');   ← ID2が発番され、コミットされる

バッチ処理 1回目の差分抽出

postgres=!# SELECT * FROM dual;
2 | BBB
(ここで差分抽出すると ID2まで取り出したことになる)

元のターミナルで、しかかり中のトランザクションをコミット

postgres=!# COMMIT;
postgres=!# SELECT * FROM dual;
1 | AAA
2 | BBB

バッチ処理 2回目の差分抽出

postgres=!# SELECT * FROM dual WHERE id > 2;
(ID1のレコードは抽出されない)