Transaction Isolation Levels
Database settings that control the visibility of concurrent transaction changes, trading off between data consistency and throughput.
Description
SQL defines four transaction isolation levels, each preventing different concurrency anomalies. READ UNCOMMITTED allows dirty reads (seeing uncommitted data from other transactions). READ COMMITTED (PostgreSQL's default) prevents dirty reads but allows non-repeatable reads (re-reading a row may return different data if another transaction committed in between). REPEATABLE READ prevents both dirty and non-repeatable reads but may allow phantom reads (new rows appearing in a range query). SERIALIZABLE prevents all anomalies by ensuring transactions execute as if they ran one at a time.
PostgreSQL implements REPEATABLE READ using snapshot isolation (MVCC), which takes a snapshot of the database at transaction start and sees only data committed before that snapshot. PostgreSQL's SERIALIZABLE level uses Serializable Snapshot Isolation (SSI), which detects dependency cycles between concurrent transactions and aborts one of them with a serialization failure. This means SERIALIZABLE is optimistic -- it does not block but may require retries.
Choosing the right isolation level is a trade-off. READ COMMITTED is sufficient for most web application queries where slight inconsistencies between reads in the same request are acceptable. Use REPEATABLE READ for reports or calculations that must see a consistent snapshot of data. Reserve SERIALIZABLE for operations where correctness depends on the absence of concurrent modifications -- such as financial transfers, seat reservations, or inventory decrements. Always handle serialization failure retries in application code when using REPEATABLE READ or SERIALIZABLE.
Prompt Snippet
Default to READ COMMITTED for standard CRUD operations. For business-critical operations like balance transfers or inventory reservations, elevate to SERIALIZABLE using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE and implement a retry loop (max 3 retries) that catches SQLSTATE 40001 (serialization_failure) and 40P01 (deadlock_detected). For reporting queries that need a consistent snapshot, use REPEATABLE READ. Document isolation level choices per repository method and add integration tests that verify correct behavior under concurrent execution using pg_advisory_lock for deterministic scheduling.
Tags
Related Terms
ACID Transactions
The four guarantees (Atomicity, Consistency, Isolation, Durability) that ensure database transactions are processed reliably even under concurrent access and system failures.
Optimistic vs Pessimistic Locking
Two concurrency control strategies: optimistic locking detects conflicts at write time using version numbers, while pessimistic locking prevents conflicts by acquiring locks at read time.
Deadlock Detection & Prevention
Strategies for detecting when two or more transactions are permanently blocked waiting for each other's locks, and design patterns to prevent this from occurring.