Two people, one row

Transactions promised that a transfer either happens completely or not at all. That promise is easy with one user. The difficulty is everyone else.

The lost update

Two clerks both apply a discount to the same order, worth 100.

clerk A: read amount        -> 1000
clerk B: read amount        -> 1000
clerk A: write 1000 - 100   -> 900
clerk B: write 1000 - 100   -> 900

Two discounts applied, one discount visible. Nothing errored. This is the lost update, and it is the reason read then write is dangerous whenever anyone else can act between the two.

Locks

A shared lock allows other readers and blocks writers. An exclusive lock blocks everyone. A row being written is exclusively locked until the transaction ends, which is why long transactions cause queues.

SELECT ... FOR UPDATE takes the exclusive lock at read time, which closes the window above:

BEGIN;
SELECT amount FROM orders WHERE id = 7 FOR UPDATE;  -- locked now
UPDATE orders SET amount = amount - 100 WHERE id = 7;
COMMIT;

This is pessimistic control: assume a conflict and prevent it.

The optimistic alternative

Assume conflicts are rare, and detect one instead of preventing it. Keep a version number and make the write conditional:

UPDATE orders SET amount = 900, version = version + 1
WHERE id = 7 AND version = 3;

If another transaction got there first, version is no longer 3, zero rows are updated, and the application retries with fresh data. No locks held, no queueing, at the cost of occasional retries.

Deadlock

Transaction A holds row 1 and wants row 2. Transaction B holds row 2 and wants row 1. Neither can proceed.

Databases detect this and abort one with an error, which the application must be prepared to retry. The way to avoid it is to always take locks in a consistent order, which is the same rule operating systems use.