TIL: pessimistic vs optimistic locking

Two ways databases handle "what if two transactions touch the same row at once":

Pessimistic locking assumes a conflict is likely, so it locks the row the moment it's read, and everyone else has to wait until the transaction releases it:

BEGIN;
 
SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;              -- locks this row; other transactions trying to
                          -- SELECT ... FOR UPDATE or UPDATE it will block
                          -- and wait here
 
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
 
COMMIT;                  -- lock released

Drop the FOR UPDATE and it's just a normal read — no lock, nothing blocks. FOR UPDATE is exactly the clause that turns "reading" into "reading with intent to update, so hold a lock until commit."

Optimistic locking assumes conflicts are rare, so it doesn't lock anything. Other transactions can freely read and write the row. Instead, you read a version column alongside the data, and when you update, you fold that version into the WHERE clause:

-- read
SELECT balance, version FROM accounts WHERE id = 1;
-- app gets balance = 500, version = 3
 
-- update, guarded by the version just read
UPDATE accounts
SET balance = 400, version = version + 1
WHERE id = 1 AND version = 3;

Check the affected-row count after: 1 row means nobody else touched it since your read; 0 rows means the version has moved on, so re-fetch and tell the user to retry instead of silently overwriting.

That guard only works if the version is actually part of the WHERE. Leave it out —

UPDATE accounts SET balance = 400 WHERE id = 1;

— and you get a lost update: the write always succeeds, unconditionally clobbering whatever's there, with no signal a conflict ever happened.

Why the plain SELECT never blocks: databases use MVCC (multi-version concurrency control) — instead of overwriting a row in place, an UPDATE creates a new row version tagged with the transaction that made it, and old versions stick around until nothing needs them. Every transaction reads a consistent snapshot of the data as of when it started, so reads never wait on writes and vice versa — only two writers touching the same row actually contend. FOR UPDATE is how you opt out of "just read a snapshot" and into "lock the current version for me." See MVCC — why reads don't block writes for the full breakdown.