Search
4 results for “isolation-levels”
Search results
Database Transactions & Isolation Levels
Why PostgreSQL's default Read Committed isolation still allows non-repeatable reads and phantom reads, and why Repeatable Read and Serializable trade that risk for transactions your application has to be ready to retry.
What is PostgreSQL's default transaction isolation level, and what specific anomaly does it still allow that a stricter level would prevent?
PostgreSQL defaults to Read Committed, where each individual statement within a transaction sees a fresh snapshot of everything committed as of that statement's start, not the transaction's start. This prevents dirty reads (seeing another transaction's uncommitted changes) but still allows non-repeatable reads, running the same SELECT twice in one transaction can return different results if another transaction committed a change in between, because each statement gets its own snapshot rather than the transaction using one snapshot throughout. Repeatable Read fixes this specific anomaly by taking one snapshot at the start of the transaction and using it for every statement within it.
A transaction under Repeatable Read isolation fails with "could not serialize access due to concurrent update." What actually happened, and what is the application expected to do?
Repeatable Read uses snapshot isolation, the transaction sees a consistent snapshot from its own start, but if it then tries to update a row that another, concurrently-committed transaction already modified, PostgreSQL detects the conflict and aborts the transaction with a serialization failure rather than silently applying an update based on stale data. This is not an application bug, it is Repeatable Read (and Serializable) working as designed, both isolation levels explicitly require the application to catch this specific error and retry the transaction from the beginning, trading the guarantee of not overwriting concurrent changes for the operational cost of occasional automatic retries.
What's the practical difference between Repeatable Read and Serializable, given that PostgreSQL's Repeatable Read already prevents phantom reads?
PostgreSQL's Repeatable Read goes beyond the SQL standard's minimum and already prevents phantom reads via snapshot isolation, but it can still allow a specific class of anomaly called a serialization anomaly, where the combined effect of several concurrently-committed transactions is not equivalent to any possible serial (one-at-a-time) ordering of them, even though each transaction individually looks consistent. Serializable adds predicate locking on top of snapshot isolation specifically to detect and prevent that remaining anomaly, guaranteeing that the outcome is always equivalent to transactions having run one at a time in some order. The cost is the same as Repeatable Read's, more serialization failures the application must retry, in exchange for the strongest correctness guarantee available.