How does PostgreSQL stop two transactions from changing the same row at the same time? With locks.
UPDATE and DELETE lock rows
When a transaction changes a row, it takes a row lock. It keeps the lock until it commits or rolls back. Another transaction that wants to change the same row waits:
| Time | Session A | Session B |
|---|---|---|
| 1 | BEGIN; Ava - 100 (locks Ava's row) | |
| 2 | Ava + 50: waits | |
| 3 | COMMIT; (the lock is released) | |
| 4 | the UPDATE runs on 400, gives 450 |
This is why SET balance = balance + 50 never loses an update: it waits, and then it reads the newest value.
A plain SELECT does not wait for row locks. It reads the last committed version of the row.
SELECT … FOR UPDATE
A withdrawal reads the balance, checks that it is enough, then changes it. Between the read and the change, another transaction could take the money. Lock the row when you read it:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- locks Ava's row
-- the application checks the balance here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- releases the lockFOR UPDATE locks every row the SELECT returns, as if the transaction had changed them. Outside a transaction the lock ends with the statement, so FOR UPDATE is useful only after BEGIN.
Deadlock
Two transactions can wait for each other:
| Time | Session A | Session B |
|---|---|---|
| 1 | locks Ava's row | |
| 2 | locks Liam's row | |
| 3 | wants Liam's row: waits for B | |
| 4 | wants Ava's row: waits for A |
Neither can continue. This is a deadlock. PostgreSQL checks for it after a short wait and stops one of the transactions:
ERROR: deadlock detectedThe other transaction gets the lock and continues. To prevent deadlocks, make every transaction lock rows in the same order, for example the lowest id first. When one still happens, run the stopped transaction again.