Transaction isolation levels and lost updates in PostgreSQL

The problem

Two support agents open the same account balance at the same moment. Both read 100, one adds 30, the other subtracts 20, and both write back. Depending on timing the final balance is 130 or 80, but never the correct 110. Each transaction was individually fine; together they lost an update. Isolation levels are the database's contract about which of these interleavings you may observe.

Anomalies in plain words

• Dirty read: reading data another transaction has written but not committed. PostgreSQL never allows this at any level.
• Non-repeatable read: you read a row twice in one transaction and get different values because another transaction committed a change in between.
• Phantom read: you run the same range query twice and the set of rows differs because someone inserted or deleted matching rows.
• Lost update: two transactions read the same value, compute a new one, and the later write overwrites the earlier without seeing it.
• Write skew: two transactions read overlapping data, each update a different row based on what they read, and together they break an invariant. The classic example is two doctors both going off call because each saw that the other was still on call.

What PostgreSQL does at each level

PostgreSQL uses multi-version concurrency control (MVCC): readers do not block writers and writers do not block readers. The standard names map to these behaviours:

READ COMMITTED gives every statement a fresh snapshot of committed data. Two identical SELECTs inside one transaction can return different results. This is enough for many request-response handlers, but it is the level where read-modify-write cycles silently lose updates.

REPEATABLE READ uses one snapshot for the whole transaction, taken at the first statement. Because it is snapshot isolation, phantoms do not appear in PostgreSQL even though the SQL standard allows them at this level. If your transaction tries to update a row that a concurrent transaction changed after your snapshot, PostgreSQL aborts yours with a serialization failure (SQLSTATE 40001). It does not prevent every anomaly: write skew is still possible.

SERIALIZABLE adds Serializable Snapshot Isolation (SSI). PostgreSQL tracks read-write dependencies between concurrent transactions and aborts one when it detects a pattern that could not occur in any serial order. The application must be ready to receive 40001 (or 40P01 for deadlocks) and retry the whole transaction. Retrying only the failed statement is wrong.

Preventing lost updates

Option 1: make the update atomic in SQL, so there is no read-modify-write in Java:

UPDATE account SET balance = balance + 30 WHERE id = 7;

Option 2: lock the row you read with SELECT ... FOR UPDATE (pessimistic locking):

BEGIN;SELECT balance FROM account WHERE id = 7 FOR UPDATE;-- compute in the applicationUPDATE account SET balance = 110 WHERE id = 7;COMMIT;

A second transaction running the same SELECT ... FOR UPDATE waits until the first commits, then sees the new value under READ COMMITTED. Keep these transactions short, and always lock rows in a consistent order to avoid deadlocks. FOR UPDATE SKIP LOCKED is useful for job queues, because workers skip rows other workers have locked.

Option 3: optimistic locking with a version column. In JPA:

@Entityclass Account {    @Id Long id;    BigDecimal balance;    @Version long version;}

Hibernate adds where id = ? and version = ? to the update and increments the version. If zero rows match, someone else changed the row and Spring throws ObjectOptimisticLockingFailureException. Catch it at a sensible boundary, reload and retry, or return 409 Conflict to the client. Optimistic locking suits low-contention data because nothing is locked while the user thinks; under heavy contention it produces many retries and pessimistic locks are simpler.

Choosing a level in Spring

@Transactional(isolation = Isolation.REPEATABLE_READ)public Report buildReport() { /* several consistent reads */ }

Use REPEATABLE READ for multi-query reads that must agree with each other, such as a report. Use SERIALIZABLE when an invariant spans rows and you cannot express it as a constraint or a single locked row, and wrap the call in a retry mechanism. For many invariants a unique constraint, a check constraint or an exclusion constraint is simpler and stronger than a high isolation level.

Common mistakes

• Believing @Transactional alone prevents lost updates. At READ COMMITTED it does not.
• Choosing SERIALIZABLE and forgetting retries; the first serialization failure then reaches the user.
• Retrying a failed statement inside the same aborted transaction instead of starting a new one.
• Holding a transaction (and its locks) open during an HTTP call to another service.
• Assuming every database behaves like PostgreSQL. MySQL's InnoDB default is REPEATABLE READ with different locking behaviour.

Trade-offs

Higher isolation means more aborted transactions and more retry code, not slower reads in the usual case. Locks give predictable behaviour but reduce concurrency and can deadlock. Optimistic checks scale well when conflicts are rare. Pick the weakest mechanism that protects the invariant you actually have, and write a concurrent test that proves it.

Proving it with a parallel test

Thinking about isolation is only worth something once you test it. Write a test in which two threads update the same row at the same time: each reads the balance, starts together via a CountDownLatch, and writes the new value. Without protection the result will be wrong; after adding @Version, FOR UPDATE or an atomic UPDATE, you should get either the correct sum or the expected ObjectOptimisticLockingFailureException.

Run this test against a real PostgreSQL. Alternatives such as H2 differ in locking and snapshot behaviour, so they can give you false confidence. Keep the test: if someone later "simplifies" the atomic UPDATE back to read-modify-write, the test will catch it.

Which level to choose and when

A practical starting point looks like this. In ordinary CRUD handlers keep READ COMMITTED, but add @Version to the entities that change. For reports where several queries must read from the same snapshot, use REPEATABLE READ with readOnly = true. For a multi-row invariant, first try to express it as a constraint; if that does not work, choose SERIALIZABLE with a retry. Document each choice for the team, because the reason for a level is forgotten later and can be reverted under the name of "simplification".

Checklist

• Know the default (READ COMMITTED) and what it allows.
• Replace read-modify-write with atomic SQL where possible.
• Use @Version or FOR UPDATE for entities edited by several users.
• Retry whole transactions on 40001 and 40P01.
• Prefer database constraints for invariants.

Where to go next

Review how the proxy decides transaction boundaries in Spring transaction pitfalls. Retries are only safe when operations are repeatable, see idempotency patterns. Then measure your understanding with the skills assessment.