Locks in PostgreSQL: SELECT FOR UPDATE, SKIP LOCKED and advisory locks

← Back to the section

When two queries read the same row, decide something and both write it back, the result can be inconsistent. Locks solve exactly that: you read a row "with the intent to change it", and no one else touches it until you are done.

How PostgreSQL locks rows by default

UPDATE and DELETE automatically lock every affected row, and they do it invisibly.

-- TX1 updates a row
UPDATE orders SET status = 'PAID' WHERE id = 42;

-- TX2 tries to update the same row at the same time
UPDATE orders SET status = 'CANCELLED' WHERE id = 42;  -- waits for TX1

A plain SELECT neither locks the row nor waits — that is the foundation of MVCC (Multi-Version Concurrency Control): a read sees a committed version of the row and is never blocked by a write. On the default READ COMMITTED level that version is picked when the statement starts, not the transaction: two identical SELECTs in one transaction can see different data if someone committed in between.

The problem shows up when you need to read, decide and update as one atomic operation.

SELECT FOR UPDATE — I read and I intend to change

Imagine a warehouse: two managers look at the same stock of 10 units and both decide "we can sell 8". Both run an UPDATE — 16 units sold out of 10.

SELECT FOR UPDATE locks the rows already at the reading stage:

BEGIN;

SELECT stock FROM product WHERE id = 100 FOR UPDATE;
-- the row is locked; a second query will wait

UPDATE product SET stock = stock - 8 WHERE id = 100;

COMMIT;
-- the lock is released

Until the first transaction finishes, a parallel SELECT FOR UPDATE of the same row waits; a plain SELECT does not — it sees the old value.

Important: FOR UPDATE only works inside a transaction. Without @Transactional the lock is released immediately, and there is no protection.

The same race without a database: the row is a plain field, FOR UPDATE is a lock taken before the read.

live example

import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.locks.ReentrantLock;

public class StockDemo {
    static int stock;
    static final AtomicInteger sold = new AtomicInteger();
    static final ReentrantLock row = new ReentrantLock();

    public static void main(String[] args) throws Exception {
        System.out.println("without lock: " + round(false));
        System.out.println("with lock:    " + round(true));
    }

    static String round(boolean forUpdate) throws Exception {
        stock = 10;
        sold.set(0);
        Thread first = new Thread(() -> sell(8, forUpdate));
        Thread second = new Thread(() -> sell(8, forUpdate));
        first.start();
        second.start();
        first.join();
        second.join();
        return "sold " + sold.get() + ", stock left " + stock;
    }

    static void sell(int amount, boolean forUpdate) {
        if (forUpdate) row.lock();
        try {
            int seen = stock;
            pause();
            if (seen >= amount) {
                stock = seen - amount;
                sold.addAndGet(amount);
            }
        } finally {
            if (forUpdate) row.unlock();
        }
    }

    static void pause() {
        try {
            Thread.sleep(50);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

Without the lock both threads read the same 10 and sold 8 each — 16 units out of 10. With the lock the second one waited, saw the reduced stock and made no sale; SELECT FOR UPDATE holds the read back exactly like that.

Locking variants

In most cases FOR UPDATE is enough. The other variants are for specific situations:

• FOR UPDATE — the standard variant. Locks the row completely against any UPDATE.
• FOR NO KEY UPDATE — softer: other transactions can do FOR KEY SHARE on the same row. Used when you update only non-key fields.
• FOR SHARE — "the row must not change while I work", but others can also take FOR SHARE.
• FOR KEY SHARE — the minimal lock, mainly for foreign key checks.

SKIP LOCKED — a task queue from a table

A classic task: several workers process one queue in parallel, and each must take its own task without getting in the others' way.

FOR UPDATE SKIP LOCKED skips already-locked rows instead of waiting:

BEGIN;

SELECT id, payload
FROM task_queue
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- each worker gets its own row

UPDATE task_queue SET status = 'PROCESSING' WHERE id = 'ord-01';

COMMIT;

Several workers can run this query at once, none waits for the others — the standard pattern for an outbox-relay and distributed queues.

NOWAIT — an error is better than waiting

Sometimes waiting is undesirable: the user expects an instant response, so a busy row is better reported at once. FOR UPDATE NOWAIT throws an error immediately instead of waiting:

SELECT * FROM orders WHERE id = 'ord-01' FOR UPDATE NOWAIT;
-- if the row is busy — an error, not waiting

The application catches the exception and returns "try again in a few seconds" to the user.

jOOQ: how to write locks in code

jOOQ has a builder method for each variant:

// FOR UPDATE
ProductRecord product = dsl
    .selectFrom(PRODUCT)
    .where(PRODUCT.ID.eq(productId))
    .forUpdate()
    .fetchOne();

// FOR UPDATE SKIP LOCKED — task queue
List<TaskRecord> tasks = dsl
    .selectFrom(TASK_QUEUE)
    .where(TASK_QUEUE.STATUS.eq("PENDING"))
    .orderBy(TASK_QUEUE.CREATED_AT)
    .limit(10)
    .forUpdate()
    .skipLocked()
    .fetch();

// FOR UPDATE NOWAIT
dsl.selectFrom(ORDER_DOC)
    .where(ORDER_DOC.ID.eq(orderId))
    .forUpdate()
    .noWait()
    .fetchOne();

A full example with the mandatory @Transactional:

@Transactional
public void reserveStock(long productId, int quantity) {
    var product = dsl.selectFrom(PRODUCT)
        .where(PRODUCT.ID.eq(productId))
        .forUpdate()
        .fetchOne();

    if (product == null) throw new ProductNotFoundException(productId);
    if (product.getStock() < quantity) throw new InsufficientStockException(productId);

    dsl.update(PRODUCT)
       .set(PRODUCT.STOCK, PRODUCT.STOCK.minus(quantity))
       .where(PRODUCT.ID.eq(productId))
       .execute();
}

Without @Transactional the connection closes right after the query, the lock goes with it — no protection.

Pessimistic vs Optimistic: when to choose which

SELECT FOR UPDATE is the pessimistic approach: "I assume a conflict, so I lock in advance". Simple in code, but many parallel requests line up in a queue.

The optimistic approach assumes conflicts are rare and checks that only at the moment of the write. For that you add a version column:

ALTER TABLE orders ADD COLUMN version bigint NOT NULL DEFAULT 0;

Reading:

live example

SELECT id, status, total_amount FROM orders WHERE id = 'ord-01';

Update with a version check:

UPDATE orders
SET status = 'PAID', version = version + 1
WHERE id = 'ord-01' AND version = :version;
-- if someone changed the row — the version differs, 0 rows affected

jOOQ:

int updated = dsl.update(ORDER_DOC)
    .set(ORDER_DOC.STATUS, "PAID")
    .set(ORDER_DOC.VERSION, ORDER_DOC.VERSION.plus(1))
    .where(ORDER_DOC.ID.eq(orderId)
        .and(ORDER_DOC.VERSION.eq(originalVersion)))
    .execute();

if (updated == 0) {
    throw new OptimisticLockException("order " + orderId + " changed concurrently");
}

The whole protection sits in that zero. Where it comes from is easy to see on a plain object: both readers got version 7, the first one wrote, and version = 7 no longer matches for the second.

live example

public class VersionDemo {
    record Order(String status, long version) {}

    static Order stored = new Order("NEW", 7);

    static int update(String status, long expectedVersion) {
        if (stored.version() != expectedVersion) {
            return 0;
        }
        stored = new Order(status, expectedVersion + 1);
        return 1;
    }

    public static void main(String[] args) {
        long readByFirst = stored.version();
        long readBySecond = stored.version();

        System.out.println("first:  rows updated " + update("PAID", readByFirst));
        System.out.println("second: rows updated " + update("CANCELLED", readBySecond));
        System.out.println("in the table: " + stored);
    }
}

The second writer got zero updated rows: the change was rejected, not lost silently. The application re-reads the row with a fresh version and repeats — or tells the user the document has already changed.

When to use which:

• Optimistic — when conflicts are rare (read-heavy): documents, profiles, reference data. Less load on the database, higher throughput.
• Pessimistic — when conflicts are frequent: financial operations, warehouse stock, any "hot" rows with high contention.

Advisory locks — a lock on an arbitrary key

Sometimes you need to lock not a row but a whole operation — say, so that a scheduled job runs on one application instance only.

An advisory lock is a lock on an arbitrary number (bigint). PostgreSQL keeps it in memory rather than on a row:

-- transactional advisory lock (released on COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);

-- try to take the lock without waiting (returns true/false)
SELECT pg_try_advisory_xact_lock(12345);

-- with two numbers: namespace + identifier
SELECT pg_advisory_xact_lock(1001, :tenant_id);

Typical uses are running a scheduled job just once across multiple application instances and keeping two identical migrations from starting in parallel.

jOOQ:

@Transactional
public void runIfNotAlreadyRunning(long jobKey, Runnable job) {
    Boolean acquired = dsl.select(
        DSL.function("pg_try_advisory_xact_lock", Boolean.class, DSL.val(jobKey))
    ).fetchOne(0, Boolean.class);

    if (Boolean.TRUE.equals(acquired)) {
        job.run();
    } else {
        log.info("job {} is already running on another instance, skipping", jobKey);
    }
}

Deadlock — a mutual lock

A deadlock happens when two transactions wait for each other:

TX1: locks the row with id=1
TX2: locks the row with id=2
TX1: tries to lock id=2 — waits for TX2
TX2: tries to lock id=1 — waits for TX1
→ stuck

PostgreSQL detects this via deadlock_timeout (1 second by default) and aborts one of the transactions with error 40P01.

The main cause: transactions take locks in different orders.

The solution — always lock rows in the same order, for example by ascending id:

// Bad: the order depends on the arguments
var from = lockAccount(fromAccountId);
var to   = lockAccount(toAccountId);

// Good: always the smaller id first
long firstId  = Math.min(fromAccountId, toAccountId);
long secondId = Math.max(fromAccountId, toAccountId);
var first  = lockAccount(firstId);
var second = lockAccount(secondId);

On top of that — retry the transaction when a deadlock does happen. The exception is easy to get wrong: PostgreSQL reports a deadlock as 40P01, which Spring turns into DeadlockLoserDataAccessException, while the similar CannotAcquireLockException means an expired lock_timeout or NOWAIT. Their common parent catches both:

@Retryable(
    retryFor = PessimisticLockingFailureException.class, // both 40P01 and timeouts land here
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2, random = true)
)
@Transactional
public void transferMoney(...) { ... }

One to three retries with a small pause are almost always enough.

lock_timeout — limiting the wait time

If a transaction cannot get a lock in the allotted time, an error beats hanging in the queue forever.

SET LOCAL lock_timeout = '5s';
UPDATE orders SET ... WHERE id = 'ord-01';

This matters most for migrations: ALTER TABLE takes ACCESS EXCLUSIVE, the heaviest lock, and without a time limit it can wait for hours behind active queries while a queue of new ones builds up behind it:

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN processed_at timestamptz;
COMMIT;

Common mistakes

• FOR UPDATE without an index on the WHERE — PostgreSQL locks exactly the matching rows, it does not escalate to the whole table. But to find them it reads the whole table, locking everything else that matches on the way. Check the plan with EXPLAIN.
• A long transaction holding a lock — the row stays locked for the whole processing time. Take the lock as close to the UPDATE as possible.
• FOR UPDATE without LIMIT on a large table — for task queues, always add a LIMIT.

In short

• UPDATE/DELETE take a row-level lock automatically, a plain SELECT does not (MVCC); SELECT FOR UPDATE locks the row already at the read, but only inside @Transactional.
• SKIP LOCKED skips busy rows — that is how several workers share one queue; NOWAIT returns an error right away instead of waiting.
• Optimistic (a version column) — for rare conflicts; pessimistic (FOR UPDATE) — for finance and hot rows.
• An advisory lock — a lock on an arbitrary key: for running a job just once in a cluster.
• Deadlock is cured by ordering locks by id; Spring @Retryable on PessimisticLockingFailureException covers the rare cases.
• lock_timeout is mandatory for ALTER TABLE — without it a migration can block all traffic.

What to read next

• Isolation levels and anomalies — when SELECT FOR UPDATE is not enough and you need SERIALIZABLE.
• Spring @Transactional — propagation, isolation and how a transaction interacts with locks.
• Zero-downtime migrations — lock_timeout and a safe ALTER TABLE under load.