principlesqlpostgresqlMajor
Transaction isolation levels: read committed vs repeatable read vs serializable
Viewed 0 times
transaction isolationREAD COMMITTEDREPEATABLE READSERIALIZABLElost updatewrite skewserialization failure
Error Messages
ERROR: could not serialize access due to concurrent updateERROR: could not serialize access due to read/write dependencies among transactionsProblem
Developers use the default READ COMMITTED isolation level and encounter non-repeatable reads, lost updates, and phantom rows in concurrent workloads without understanding why.
Solution
Choose the isolation level that matches the consistency requirement:
-- Default (READ COMMITTED): each statement sees committed data at statement start.
-- Sufficient for simple CRUD but vulnerable to lost updates.
-- REPEATABLE READ: entire transaction sees consistent snapshot from BEGIN.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- concurrent UPDATE sets balance = 800 and commits
SELECT balance FROM accounts WHERE id = 1; -- still 1000 (snapshot)
COMMIT;
-- SERIALIZABLE: full SSI — prevents write skew and phantom reads.
-- Transactions may be aborted with serialization_failure; retry in application.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT; -- may raise ERROR: 40001 serialization failure
-- Default (READ COMMITTED): each statement sees committed data at statement start.
-- Sufficient for simple CRUD but vulnerable to lost updates.
-- REPEATABLE READ: entire transaction sees consistent snapshot from BEGIN.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- concurrent UPDATE sets balance = 800 and commits
SELECT balance FROM accounts WHERE id = 1; -- still 1000 (snapshot)
COMMIT;
-- SERIALIZABLE: full SSI — prevents write skew and phantom reads.
-- Transactions may be aborted with serialization_failure; retry in application.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT; -- may raise ERROR: 40001 serialization failure
Why
PostgreSQL's MVCC gives each transaction a snapshot of the database. READ COMMITTED refreshes the snapshot per statement. REPEATABLE READ fixes it at transaction start. SERIALIZABLE adds predicate locking to detect write skew.
Gotchas
- READ COMMITTED does not prevent lost updates; use SELECT ... FOR UPDATE to lock rows
- SERIALIZABLE transactions must be retried on serialization_failure (SQLSTATE 40001)
- REPEATABLE READ in PostgreSQL also prevents phantom reads (unlike the SQL standard which allows them)
- Long-running REPEATABLE READ transactions hold a snapshot, delaying VACUUM's cleanup
Code Snippets
SELECT FOR UPDATE to prevent lost update under READ COMMITTED
-- Prevent lost update with SELECT FOR UPDATE:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Now locked; concurrent updaters must wait
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Context
Financial transactions, inventory management, and any scenario requiring strict consistency
Revisions (0)
No revisions yet.