Transactions and the lost update

Postgres defaults to READ COMMITTED. Each statement sees a snapshot taken when that statement began — not when the transaction began. Two concurrent transactions can therefore both read the same value, both compute a new one from it, and one of the writes vanishes.

-- session A                        -- session B
begin;
select stock from item            begin;
 where id = 1;  -- reads 10       select stock from item
                                   where id = 1;  -- also reads 10
update item set stock = 9
 where id = 1;                     update item set stock = 9
commit;                            where id = 1;   -- waits for A
                                  commit;          -- writes 9

-- two units sold, stock went from 10 to 9.

The first is to never read-then-write at all. Let the database do the arithmetic in one statement, and the problem cannot occur.

-- 1. atomic. no read, no gap, no lost update.
update item
set    stock = stock - 1
where  id = 1 and stock > 0
returning stock;
-- zero rows returned means it was out of stock. check that.

When you genuinely need to read a value, make a decision in application code, and then write, take a row lock on the read. `FOR UPDATE` makes the second transaction wait until the first commits, and it then re-reads the fresh value.

-- 2. pessimistic lock
begin;
select stock from item where id = 1 for update;  -- blocks concurrent readers-for-update
-- ... application logic ...
update item set stock = $1 where id = 1;
commit;

Or go optimistic: carry a version column, and make the write conditional on the version you read. If it changed underneath you, zero rows update and you retry. No locks, but you must actually handle the retry.

-- 3. optimistic. zero rows affected = someone beat you; retry.
update item
set    stock = $1, version = version + 1
where  id = 1 and version = $2;

A transaction holds its locks until it commits. Anything slow inside one — an HTTP call to a payment provider, an image resize, a `sleep` someone left in — extends that lock hold across the whole operation and turns a working system into a queue.

• Never make a network call inside a transaction.
• Take locks in a consistent order everywhere, or you will deadlock. Ordering by primary key is a fine convention.
• Set `statement_timeout` and `idle_in_transaction_session_timeout` so a stuck transaction dies instead of blocking the database indefinitely.
• A deadlock is not a crash — Postgres kills one transaction and reports it. Catch SQLSTATE 40P01 and retry.