SELECT ... FOR UPDATE: Pessimistic Row Locking in SQL

SELECT ... FOR UPDATE takes a pessimistic lock on the rows it returns: while your transaction is open, nobody else can update or delete them. It is the go-to tool when you read a value, compute something from it, and write it back, and races between transactions are unacceptable.

Why lock rows at all

The classic bug is read-modify-write: read a balance, compute a new value in application code, write it back. Between the read and the write, another transaction does the same thing, and one update is silently lost.

FOR UPDATE fixes this by locking the rows at read time. The lock is held until COMMIT or ROLLBACK.

BEGIN;
SELECT id, amount FROM orders WHERE user_id = 42 AND status = 'pending' FOR UPDATE;
-- rows are now locked until COMMIT
UPDATE orders SET status = 'paid' WHERE user_id = 42 AND status = 'pending';
COMMIT;

Any other transaction trying to take FOR UPDATE on the same rows blocks until your COMMIT. A plain SELECT without locking still sees the old row version (MVCC) and does not block.

FOR UPDATE vs FOR SHARE

PostgreSQL offers several lock strengths:

• FOR UPDATE is exclusive: nobody else can lock for read or modify the row.
• FOR SHARE is shared: many transactions can hold it at once, but nobody can UPDATE/DELETE. Handy to freeze a parent row while you insert children.
• FOR NO KEY UPDATE and FOR KEY SHARE are weaker variants that conflict less with foreign-key checks.

BEGIN;
-- freeze the user row while we add an order for them
SELECT id FROM users WHERE id = 42 FOR SHARE;
INSERT INTO orders (user_id, amount, status) VALUES (42, 200, 'pending');
COMMIT;

NOWAIT and SKIP LOCKED

By default a locked row makes you wait. Two modifiers change that:

• NOWAIT does not wait at all: if a row is busy, the query fails immediately with an error. Good for interactive operations where returning "try again" fast beats hanging.
• SKIP LOCKED skips busy rows and returns only the free ones. This is the workhorse of job queues: each worker claims its own batch without stepping on others.

-- worker pulls the next free job, ignoring rows other workers already took
BEGIN;
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- process it, then mark done
UPDATE orders SET status = 'processing' WHERE id = :id;
COMMIT;

Money transfer and lock ordering

A transfer touches two rows, and that is where deadlocks hide: transaction A locks row 1 then row 2; transaction B does the reverse. Each waits for the other forever.

The cure is simple: always lock rows in the same order, for example ascending id. An IN (1, 2) with ORDER BY guarantees a deterministic order.

BEGIN;
-- always lock in a deterministic order to avoid deadlocks
SELECT id, amount FROM orders
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE orders SET amount = amount - 200 WHERE id = 1;
UPDATE orders SET amount = amount + 200 WHERE id = 2;
COMMIT;

Gotchas and fine print:

• FOR UPDATE cannot be used with aggregates or GROUP BY — there are no concrete rows to lock.
• With a JOIN, by default rows from all tables are locked; use FOR UPDATE OF orders to lock just the one you mean.
• A long transaction holds its locks the whole time — never call an external API between FOR UPDATE and COMMIT.
• SKIP LOCKED may return fewer rows than LIMIT: that is the price of concurrency, and for queues it is expected.

When to go optimistic instead

Pessimistic locks have a cost: transactions queue up behind each other. If conflicts are rare, an optimistic approach is cheaper — add a version column and check it on write.

-- optimistic update: succeeds only if nobody changed the row meanwhile
UPDATE orders
SET amount = 300, version = version + 1
WHERE id = 1 AND version = 7;

If the UPDATE reports 0 rows, somebody beat you to it: re-read and retry. Rule of thumb: high contention on the same rows favors FOR UPDATE; rare conflicts under heavy parallelism favor the optimistic version column.

Engine notes: MySQL (InnoDB) supports FOR UPDATE, FOR SHARE, NOWAIT, and SKIP LOCKED, but its locking semantics (gap locks) differ. ClickHouse is an analytical database with no row-level locks or transactions in the usual sense, so these clauses do not exist there.