一意性は、宣言したときだけ存在する

各接続は BEGIN → SELECT count(*) = 0 AS absent FROM users WHERE email = 'kazu@example.com' → 2秒待つ → absent なら INSERT → 2秒待つ → COMMIT を実行します。B は A の1秒後に始めます。既定の READ COMMITTED で各2回、2回とも同じ結果でした。

まず制約なし(CREATE TABLE users (email text, name text))の B です。

B checked: absent = t
INSERT INTO users VALUES ('kazu@example.com', 'B');
INSERT 0 1
Time: 0.513 ms
COMMIT;
COMMIT
Time: 1.431 ms
--- final ---
    email       | name 
------------------+------
kazu@example.com | A
kazu@example.com | B
(2 rows)

B の INSERT は 0.5 ms で通り、同じ email が2行残りました。分離レベルを REPEATABLE READ にしても2回とも2行でした(SERIALIZABLE は試していません)。2つのトランザクションはどちらも正しく、全部か無しかでコミットしています。 原子性の失敗ではありません。

次に email text UNIQUE と宣言した場合です。

B checked: absent = t
INSERT INTO users VALUES ('kazu@example.com', 'B');
psql:/tmp/b.sql:7: ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(kazu@example.com) already exists.
Time: 983.227 ms
SELECT pg_sleep(2);
psql:/tmp/b.sql:9: ERROR:  current transaction is aborted, commands ignored until end of transaction block
Time: 0.121 ms
COMMIT;
ROLLBACK
Time: 0.112 ms
--- final ---
    email       | name 
------------------+------
kazu@example.com | A
(1 row)

1行目は制約なしと同じく absent = t です。確かめは今回も「無い」と答えています。 違いは INSERT で、983 ms 待ってから拒否されました(2回目は 992 ms)。B が INSERT したのは t≈3、A がコミットしたのは t≈4 なので、A のコミットを待っていた計算です。B の COMMIT は ROLLBACK と報告されました。

待った理由は、PostgreSQL の索引の実装者向けの章(第63章)に書かれています。

If a conflicting row has been inserted by an as-yet-uncommitted transaction, the would-be inserter must wait to see if that transaction commits. If it rolls back then there is no conflict.

A が巻き戻していれば B は通ったはずです(この分岐は実行していません)。

同じ形はデータベースの外にもあります。Linux でプログラムがファイルを扱うときは、OS の関数(システムコール)を呼びます。access(2) はファイルの有無や権限を確かめ、open(2) はファイルを開きます。

「確かめてから開く」の危うさを、access(2) の man ページ自身が警告しています。

Warning: Using these calls to check if a user is authorized to, for example, open a file before actually doing so using open(2) creates a security hole, because the user might exploit the short time interval between checking and opening the file to manipulate it.

警告しているのは権限の確認と open の間です。存在の確認そのものを名指ししてはいません。ただ構造は同じで、確かめる呼び出しと使う呼び出しの間に短い時間があり、そこを突かれます。一般に TOCTOU(time-of-check to time-of-use)と呼ばれる形ですが、この語は man ページには出てきません。

「無ければ作る」を隙間なく行う手段は open(2) にあります。

O_EXCL Ensure that this call creates the file: if this flag is specified in conjunction with O_CREAT, and path already exists, then open() fails with the error EEXIST.

確かめと作成を1回の呼び出しに入れ、既にあれば失敗させます。UNIQUE と同じ設計です。しかも man ページは、NFS で O_EXCL が支えられない環境ではこれに頼るロックに a race condition が残ると書きます。宣言が効くのは、その下の実装が約束を支えているときだけです。