Database locking with raw SQL (FOR UPDATE)

Published August 11, 2026・Last updated August 11, 2026・5 min read

Two 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 = 100
                                 reads balance = 100
next = 100 - 10 = 90             next = 100 - 10 = 90
UPDATE SET balance = 90
COMMIT  (balance is now 90)
                                 UPDATE SET balance = 90
                                 COMMIT  (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 balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 10
WHERE 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 balance
FROM accounts
WHERE id = 1
FOR 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, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
UPDATE jobs
SET 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 balance
       FROM accounts
       WHERE id = $1
       FOR UPDATE`,
      [accountId]
    );
    if (!rows[0] || rows[0].balance < amount) {
      throw new Error('Insufficient funds');
    }
    await client.query(
      `UPDATE accounts
       SET balance = balance - $1
       WHERE 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

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 UPDATE outside a transaction is released as soon as the statement finishes; wrap BEGIN/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 / LIMIT so you do not lock a large scan.
• SKIP LOCKED empty set - not always “queue empty”; candidates may all be locked.
• NOWAIT is an error - handle the lock-not-available SQLSTATE in your app.