CodePark/ questions / Locking, concurrency & deadlocks
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Locking, concurrency & deadlocks

At PostgreSQL's default Read Committed level, which approach prevents lost updates when many requests decrement a balance concurrently?

UPDATE accounts SET balance = balance - 10 WHERE id = 1

The right approach is the single statement UPDATE accounts SET balance = balance - 10 WHERE id = 1. This UPDATE locks the row. A second update at the same time waits for the lock. When the first one commits, the second update re-reads the newly committed row and subtracts from that value. So no decrement is lost.

"Read the balance with
SELECT, then write the new value, both inside one BEGIN ... COMMIT" does not help at Read Committed. The plain SELECT takes no lock, so both transactions can read the same old value and one write overwrites the other.