PostgreSQL interview question · Most asked
How do you prevent lost updates in PostgreSQL under READ COMMITTED?
Summary
Do not read a value, compute in the application and write it back. Use an atomic statement (UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1), lock the row first with SELECT ... FOR UPDATE, use optimistic concurrency with a version column, or run the transaction at REPEATABLE READ or SERIALIZABLE and retry on serialization failures.
Longer Answer
Under READ COMMITTED two transactions can both read balance 100, both subtract 30 in the application, and both write 70: one update is lost. The atomic UPDATE is simplest and fastest; row locks suit multi-step logic; higher isolation levels detect the conflict but require retries.
Key Points
- Read-modify-write loses updates.
- Atomic UPDATE with a guard.
- FOR UPDATE, version columns, or higher isolation + retry.
Deep dive: Transactions, Locking & Concurrency in Code
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?