Locks, FOR UPDATE and deadlocks

Part of the free LogicWiz SQL & Databases course, Module 13: Transactions and concurrency. The queries and practice run in the app on real Postgres.

Two UPI payments from Kabir's account arrive in the same millisecond. Each one checks "balance ≥ amount", both pass, and both go through. How do you make the second payment wait until the first is done?

You'll learn to

• Lock rows you are about to change with SELECT … FOR UPDATE
• Explain how a deadlock happens and how to avoid it with a fixed lock order
• Hand out work or seats safely with SKIP LOCKED
• Use a version column for optimistic locking

Learn

Row locks

Every UPDATE and DELETE locks the rows it changes until its transaction ends. Another transaction that wants to change the same row waits.

Plain SELECTs take no row locks and never wait (MVCC gives them a snapshot).

But often you need to read first, decide, then write: "is the balance enough? then debit". Between your read and your write, someone else can change the row. SELECT … FOR UPDATE reads the row and locks it as if you were about to update it, so nobody else can change or lock it until you commit.

FOR UPDATE makes the second payment wait

The pattern: lock, check, change, commit

BEGIN;
SELECT balance
FROM accounts
WHERE account_id = 5
FOR UPDATE;
UPDATE accounts SET balance = balance - 700 WHERE account_id = 5;
UPDATE accounts SET balance = balance + 700 WHERE account_id = 3;
COMMIT;
SELECT account_id, balance FROM accounts WHERE account_id IN (3, 5) ORDER BY account_id;

In a real app the code reads the locked balance, decides, and then sends the UPDATEs or a ROLLBACK. Keep this transaction short: everyone else wanting Kabir's row is waiting.

A deadlock: each waits for the other

Lock in the same order everywhere. The deadlock above happened because A locked 1 then 2, and B locked 2 then 1. If every transfer locks the lower account_id first, the second transfer waits at its first lock and no cycle can form:

SELECT account_id FROM accounts WHERE account_id IN (1, 2) ORDER BY account_id FOR UPDATE;

Tatkal rush: SKIP LOCKED hands out different seats

Optimistic locking: a version number

FOR UPDATE is pessimistic: it locks early, assuming a clash. That's bad when a user may stare at an edit screen for two minutes.

Optimistic locking takes no lock while the user thinks. Each row has a version column. The app remembers the version it read, and the UPDATE only succeeds if nobody changed it since:

UPDATE … SET …, version = version + 1 WHERE id = 4 AND version = 7;

1 row updated means you won. 0 rows means someone else saved first: reload and try again.

Optimistic locking in action

-- Riya's app read her account at version 1
UPDATE accounts
SET balance = balance - 60,
    version = version + 1
WHERE account_id = 4
  AND version = 1
RETURNING balance, version;
-- a second app that also read version 1 tries to save
UPDATE accounts
SET balance = balance - 430,
    version = version + 1
WHERE account_id = 4
  AND version = 1
RETURNING balance, version;

The first save returns the new balance and version 2. The second returns no rows: its version is stale, so it must reload and retry.

Exams ask for the lock compatibility matrix: a shared (S) lock (for reading) is compatible with other S locks; an exclusive (X) lock (for writing) is compatible with nothing. S+S ok, S+X wait, X+X wait. In Postgres, FOR SHARE is like S and FOR UPDATE is like X on a row. Also know the four deadlock conditions: mutual exclusion, hold and wait, no preemption, circular wait.

In one line

Writes lock rows until commit; FOR UPDATE locks what you read, SKIP LOCKED skips busy rows, a fixed lock order prevents deadlocks, and a version column gives optimistic locking.

Interview corner

What is SELECT … FOR UPDATE and when do you use it?

It reads rows and locks them like an UPDATE would, until the transaction ends. Use it for read-check-write logic (check balance, then debit; check seat free, then book) so nobody changes the rows between your check and your write. Use it inside BEGIN … COMMIT and keep the transaction short.

What is a deadlock and how do you prevent one?

Two or more transactions each hold a lock the other needs, so they wait in a cycle. Postgres detects it and aborts one. Prevent it by locking rows in a consistent order (e.g. by primary key), keeping transactions short, and retrying the victim.

Optimistic vs pessimistic locking?

Pessimistic locks rows up front (FOR UPDATE): best when clashes are frequent and transactions short. Optimistic takes no lock; it checks a version column at write time (WHERE id = ? AND version = ?) and retries on 0 rows: best when clashes are rare or the user holds the data for a long time, like an edit form.

How would you build a job queue in Postgres?

Workers run SELECT … FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED, process the job, mark it done and commit. SKIP LOCKED makes each worker grab a different job without waiting on the others.