When two queries read the same row at the same time, make a decision and both try to change it, you can end up with an inconsistent result. Locks solve exactly this problem: they let you read a row "with the intent to change it", and no one else will touch it until you are done.
How PostgreSQL locks rows by default
UPDATE and DELETE automatically take a lock on every affected row. This happens 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, on the other hand, does not lock the row and does not wait — this is the foundation of MVCC (Multi-Version Concurrency Control): a read sees the "committed" version of the row as of the moment the transaction started and is never blocked by a write.
The problem shows up when you need to read, make a decision and update — all as a single atomic operation.
SELECT FOR UPDATE — I read and I intend to change
Imagine a warehouse: two managers look at the stock of a product (10 units) at the same time and both decide "we can sell 8". Both run an UPDATE. As a result, 16 units are sold when only 10 were in stock.
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 will wait. A plain SELECT (without FOR UPDATE) does not wait — 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.
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 anyUPDATE.FOR NO KEY UPDATE— softer: other transactions can doFOR KEY SHAREon 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 takeFOR SHARE.FOR KEY SHARE— the minimal lock, mainly for foreign key checks.
In practice, 95% of tasks are solved by FOR UPDATE.
SKIP LOCKED — a task queue from a table
A classic task: several workers process a task queue in parallel. How do you make each one 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 = :id;
COMMIT;
Several workers can run this query at the same time — each grabs its own row, and none of them waits for the others. This is 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, and if the row is busy — it is better to tell them right away.
FOR UPDATE NOWAIT does not wait — it throws an error immediately if the row is locked:
SELECT * FROM order_doc WHERE id = :id 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 provides convenient methods for all the variants:
// 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();
// FOR NO KEY UPDATE
dsl.selectFrom(PRODUCT)
.where(PRODUCT.ID.eq(productId))
.forNoKeyUpdate()
.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 jOOQ closes the connection after the query and the lock is released immediately — the protection disappears.
Pessimistic vs Optimistic: when to choose which
SELECT FOR UPDATE is the pessimistic approach: "I assume a conflict, I lock in advance". It is simple in code, but with a large number of parallel requests it forms a queue.
The optimistic approach assumes that conflicts are rare and checks this only at the moment of the write. For this you add a version column:
ALTER TABLE order_doc ADD COLUMN version bigint NOT NULL DEFAULT 0;
Reading:
SELECT id, status, version FROM order_doc WHERE id = :id;
Update with a version check:
UPDATE order_doc
SET status = 'PAID', version = version + 1
WHERE id = :id 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");
}
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 — for example, so that a scheduled job runs on only one application instance in a cluster.
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:
- Running a scheduled job just once across multiple application instances.
- Preventing two identical migrations from running in parallel.
- A global queue without duplication.
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);
Additionally: handle CannotAcquireLockException by retrying the query:
@Retryable(
retryFor = CannotAcquireLockException.class,
maxAttempts = 3,
backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional
public void transferMoney(...) { ... }
1–3 retries with a small pause between them resolve most cases.
lock_timeout — limiting the wait time
If a transaction cannot acquire a lock within the allotted time, it is better to get an error than to hang in the queue forever.
SET LOCAL lock_timeout = '5s';
UPDATE order_doc SET ... WHERE id = :id;
This is especially important for migrations. ALTER TABLE takes ACCESS EXCLUSIVE — the heaviest lock. Without a time limit, a migration 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
SELECT FOR UPDATEwithout a transaction — the lock is released immediately, there is no protection. You need@Transactional.FOR UPDATEwithout an index on theWHERE— the database will lock the whole table instead of the needed rows. Check the plan withEXPLAIN.- A long transaction holding a lock — the row is locked for the entire processing time. Take the lock as close to the
UPDATEas possible. - A pessimistic lock on every read — if conflicts are rare, optimistic locking via
versiongives less load. - Locks taken in different orders — deadlock. Order them by
id. FOR UPDATEwithoutLIMITon a large table — for task queues, always add aLIMIT.ALTER TABLEwithoutlock_timeout— under high load a migration can block everything. SetSET LOCAL lock_timeout.
In short
UPDATE/DELETEtake a row-level lock automatically. A plainSELECTdoes not (MVCC).SELECT FOR UPDATElocks rows already at the reading stage — for read-modify-write operations. Only inside@Transactional.SKIP LOCKED— skips busy rows: the standard pattern for parallel queue workers.NOWAIT— an immediate error instead of waiting: suitable for APIs with an instant response.- 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@RetryableonCannotAcquireLockExceptioncovers the rare cases. lock_timeoutis mandatory forALTER TABLE— without it a migration can block all traffic.
What to read next
- Isolation levels and anomalies — when
SELECT FOR UPDATEis not enough and you needSERIALIZABLE. - Spring @Transactional — propagation, isolation and how a transaction interacts with locks.
- Zero-downtime migrations —
lock_timeoutand a safeALTER TABLEunder load.