hesela.devSerializable Snapshot Isolation
Serializable Snapshot Isolation (SSI) is a concurrency-control technique that adds read-write antidependency tracking to snapshot isolation and aborts transactions to prevent nonserializable committed histories.
PostgreSQL 18 uses SSI for SERIALIZABLE, while REPEATABLE READ remains snapshot isolation. [3]
An rw-antidependency points from a transaction that reads a version or predicate result to a concurrent transaction whose write would change that result but is absent from the reader's snapshot. A snapshot-isolation serialization cycle needs two consecutive rw-antidependencies, but the pair need not form a cycle. PostgreSQL uses commit-order and read-only refinements; false positives remain. [2]
PostgreSQL SIReadLock entries track possible conflicts without blocking writers or causing deadlocks themselves. However, ordinary update locks can still wait. [3]
On SQLSTATE 40001, retry the complete transaction, including its query and value decisions; another retry may also fail. [4]
SERIALIZABLE READ ONLY DEFERRABLE may wait for a safe snapshot, then runs without SSI serialization failures or its normal tracking overhead. This does not prevent other errors. [5] When relying on SSI alone for application consistency, all relevant writes and consistency-sensitive reads must use SERIALIZABLE; changing only a reporting transaction does not upgrade lower-isolation writers. [6]
SSI is a serializability mechanism. Check real-time precedence separately for the specific system and API; SSI's name alone does not establish strict serializability. [2] [8]
Relations
Advantages
- Committed serializable transactions can preserve cross-row invariants without blocking predicate-read locks. [3]
Limitations
- A serialization failure is not proof that an actual dependency cycle was present; conservative detection can produce false positives. [2]
- Retries are part of the application contract, not a guarantee of completion within a fixed number of attempts. [4]
- PostgreSQL SERIALIZABLE reads from permanent user tables remain provisional until successful commit, including ordinary read-only transactions. SERIALIZABLE READ ONLY DEFERRABLE obtains a safe snapshot before returning data. This concerns serialization anomalies, not freshness or all errors. [3, 5]
- Sending a notification after commit still leaves a crash gap before delivery. A transactional outbox records the pending event in the same transaction as the data change; a separate process sends committed events, and duplicate delivery requires idempotent handling. [1]
- Predicate-lock coverage depends on the query plan. Sequential scans take relation-level coverage; memory-driven promotion to coarser coverage can increase serialization failures without introducing writer-blocking locks. PostgreSQL's max_pred_locks_per_transaction sizes shared tracking capacity, not a hard allowance for each transaction. [3, 9]
Citations
- AWS Prescriptive Guidance: Transactional outbox pattern, dual writes and duplicate messages.
- Dan R. K. Ports and Kevin Grittner. Serializable Snapshot Isolation in PostgreSQL. PVLDB 5(12), 1850-1861 (2012), sections 3.1-4.3 and 5.4.
- PostgreSQL 18: Transaction Isolation, sections 13.2.2-13.2.3.
- PostgreSQL 18: Serialization Failure Handling, section 13.5.
- PostgreSQL 18: SET TRANSACTION, DEFERRABLE snapshot acquisition.
- PostgreSQL 18: Data Consistency Checks at the Application Level, section 13.4.1.
- Herlihy and Wing. Linearizability: A Correctness Condition for Concurrent Objects. ACM TOPLAS 12(3), 1990, section 3.3.
- PostgreSQL 18: Lock Management, predicate-lock capacity and granularity promotion.