Database locking with raw SQL (FOR UPDATE)
August 11, 2026Two concurrent transactions that each SELECT a row, change it in memory, then UPDATE can lose an update or claim the same resource twice. Postgres does not serialize that read-modify-write path unless you ask for a row lock.
SELECT … FOR UPDATE takes a pessimistic row lock until the transaction commits or rolls back. Other transactions that try to lock the same row wait, fail immediately (NOWAIT), or move on (SKIP LOCKED).
This post covers the race, FOR UPDATE / NOWAIT / SKIP LOCKED in raw SQL, the same flow with node-pg, a short contrast with optimistic locking, and pitfalls.
Prerequisites
- Node.js version 26
- PostgreSQL (demo uses Docker Compose, or see Postgres and Redis containers with Docker Compose)
npm i pg
The race without a lock
Account balance starts at 100. Two workers each intend to debit 10. Done one after the other, that is 100 → 90 → 80.
What goes wrong is read → compute in the app → write an absolute value, with no row lock:
Worker A Worker B-------- --------reads balance = 100reads balance = 100next = 100 - 10 = 90 next = 100 - 10 = 90UPDATE SET balance = 90COMMIT (balance is now 90)UPDATE SET balance = 90COMMIT (balance still 90)
Both workers applied “set to 90” based on the same stale read. The second debit never subtracted from 90, so the stored balance is 90 instead of the intended 80.
UPDATE … SET balance = balance - 10 in SQL would avoid this particular race. The bug appears when the app does read → compute → write an absolute value. Same pattern shows up when two workers claim one pending job with SELECT … WHERE status = 'pending' LIMIT 1 and then UPDATE … SET status = 'claimed'.
SELECT … FOR UPDATE
Lock the row inside an explicit transaction. The lock lives only until COMMIT or ROLLBACK.
BEGIN;SELECT balanceFROM accountsWHERE id = 1FOR UPDATE;UPDATE accountsSET balance = balance - 10WHERE id = 1;COMMIT;
While the first transaction holds the lock, a second SELECT … FOR UPDATE on the same row waits. After commit, the second transaction sees the updated balance.
NOWAIT
Fail immediately if the row is already locked instead of waiting:
SELECT balanceFROM accountsWHERE id = 1FOR UPDATE NOWAIT;
Postgres raises an error (could not obtain lock on row). Useful when you prefer to retry or return “busy” rather than block a request thread.
SKIP LOCKED
Skip rows that other transactions already locked. Typical for job queues: each worker claims a different pending row without waiting.
BEGIN;SELECT id, payloadFROM jobsWHERE status = 'pending'ORDER BY idFOR UPDATE SKIP LOCKEDLIMIT 1;UPDATE jobsSET status = 'claimed', claimed_at = now()WHERE id = $1;COMMIT;
An empty result can mean “no pending jobs” or “every pending job is locked by another worker right now.” Decide whether to treat that as idle or as a short retry.
With node-pg
Use one client per transaction (pool.connect()), not interleaved queries on a shared pool client:
import pg from 'pg';const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });async function debit(accountId, amount) {const client = await pool.connect();try {await client.query('BEGIN');const { rows } = await client.query(`SELECT balanceFROM accountsWHERE id = $1FOR UPDATE`,[accountId]);if (!rows[0] || rows[0].balance < amount) {throw new Error('Insufficient funds');}await client.query(`UPDATE accountsSET balance = balance - $1WHERE id = $2`,[amount, accountId]);await client.query('COMMIT');} catch (error) {await client.query('ROLLBACK');throw error;} finally {client.release();}}
Claim a job with SKIP LOCKED the same way: BEGIN → SELECT … FOR UPDATE SKIP LOCKED LIMIT 1 → UPDATE → COMMIT on that client.
Optimistic vs pessimistic
| Approach | Idea |
|---|---|
Pessimistic (FOR UPDATE) | Lock the row before changing it; others wait or skip |
Optimistic (version column) | UPDATE … WHERE id = $1 AND version = $2; retry if zero rows updated |
Reach for FOR UPDATE when contention is expected and you must not double-apply (money, inventory, exclusive claims). Optimistic locking avoids holding locks during long work but needs a retry loop.
Redis locks and Postgres advisory locks are out of scope here - different tools for different coordination problems.
Pitfalls
- No transaction, no lock -
FOR UPDATEoutside a transaction is released as soon as the statement finishes; wrapBEGIN/COMMIT. - Keep the critical section short - do not hold the lock while calling external APIs; claim/update, then work after commit when you can.
- Lock only the rows you need - filter with indexed
WHERE/LIMITso you do not lock a large scan. SKIP LOCKEDempty set - not always “queue empty”; candidates may all be locked.NOWAITis an error - handle the lock-not-available SQLSTATE in your app.
