How does the Serializable isolation level work in PostgreSQL, and what must the application do to use it correctly?

PostgreSQL implements Serializable with Serializable Snapshot Isolation (SSI), and the application must retry transactions that fail.

How it works:

• Transactions read from a snapshot (a fixed view of the data), like in Repeatable Read.
• PostgreSQL also tracks read/write dependencies between transactions that run at the same time. It uses predicate locks for this.
• Predicate locks do not block anyone. They only record what each transaction read.
• When PostgreSQL sees a pattern that could give a result no one-after-another order would give, it aborts one transaction with SQLSTATE 40001 (serialization_failure).
• Some of these aborts are false positives: there was no real problem.

What the application must do:

• Wrap the whole transaction in a retry loop.
• On 40001, roll back and run everything again from the beginning, including the reads. Decisions based on old reads are no longer valid.
• Limit the number of retries, and wait a little before each one (a small backoff).
• Keep transactions short to reduce conflicts.
• Declare read-only transactions as READ ONLY.

Repeatable Read can also raise 40001 when you update a row that another transaction changed at the same time. So it needs the same retry logic.