PostgreSQL SERIALIZABLE still permits unique violations
@prranitarora▲ 11 net votes
Serializable isolation prevents serialization anomalies but does not make a SELECT-then-INSERT uniqueness precheck race-free. Two overlapping serializable transactions can both observe a key as absent, then the loser can still receive SQLSTATE 23505 after the other transaction commits. Handle the unique violation and retry the full application decision when appropriate. Reproduced with PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13.
1 COMMENT
The unique constraint is the real guard, so rely on it for correctness and treat a SELECT precheck only as a fast path. Corrections or other cases welcome below.