Pessimistic Locking with SELECT FOR UPDATE

1. What pessimistic locking is

Pessimistic locking assumes write conflicts are likely, and therefore locks a row already at read time, not only at write time. The central statement for this is SELECT ... FOR UPDATE, which exclusively reserves one or more rows for the duration of the current transaction. Any other transaction that tries to read or change the same row, also with FOR UPDATE, must wait until the locking transaction commits or rolls back.

The difference from optimistic locking with version columns is fundamental: instead of detecting a conflict after the fact at write time, pessimistic locking prevents the conflict from the outset by not letting competing transactions access the same row concurrently in the first place. This makes pessimistic locking especially suitable for scenarios with a high probability of conflict, where repeated retries under optimistic locking would cause more overhead than a direct lock.

The following sections show the exact syntax of SELECT FOR UPDATE, the difference between row-level and table locks, the SKIP LOCKED pattern for queue processing, and how to handle lock timeouts and deadlocks.

2. SELECT FOR UPDATE: syntax and behavior

The basic syntax of SELECT ... FOR UPDATE is anchored in the SQL standard and supported by all major relational databases, though with slightly different extensions. The statement must be inside an explicit transaction, otherwise the lock would be released again immediately after the single statement and defeat its purpose. Every row the SELECT returns is locked exclusively until the transaction ends with COMMIT or ROLLBACK.

-- Basic pessimistic locking with SELECT FOR UPDATE
BEGIN;

SELECT stock, price
FROM inventory
WHERE product_id = 42
FOR UPDATE;
-- This row is now exclusively locked,
-- other transactions using FOR UPDATE must wait

-- Compute in application code based on values known to be current
UPDATE inventory
SET stock = stock - 3
WHERE product_id = 42;

COMMIT;
-- Lock is released automatically on COMMIT

A common misunderstanding is that a plain SELECT without FOR UPDATE also protects a row. That is wrong: a normal SELECT reads data consistently according to the current isolation level, but does not prevent a concurrent transaction from changing the same row. Only FOR UPDATE enforces the exclusive lock that blocks other writing transactions and other transactions also reading with FOR UPDATE.

3. Row-level locks vs. table locks

Modern relational databases implement pessimistic locking at the row level, not the table level, which is crucial for concurrency. A SELECT FOR UPDATE on a single row only blocks access to exactly that row, not the entire table. Other transactions can continue to read and change other rows of the same table without restriction, as long as they do not touch the same locked row.

Technically, databases typically manage these row-level locks via an in-memory lock table that points to the physical location of the row, not via a marker within the row itself. For range queries with FOR UPDATE, for example SELECT ... WHERE status = 'pending' FOR UPDATE, the database locks all returned rows individually. MySQL/InnoDB can, under certain conditions, additionally set gap locks on gaps between index values to prevent phantom reads from subsequent INSERTs, which can extend the effective lock range beyond the rows actually returned.

4. SKIP LOCKED for concurrent queue processing

The pattern FOR UPDATE SKIP LOCKED solves a common problem in queue-based systems: multiple concurrent worker processes each want to grab the next available task from a table, without blocking each other or processing the same task twice. Without SKIP LOCKED, a worker trying to read a row already locked by another worker would simply wait, which leads to unnecessary wait times with several concurrent workers.

-- Queue processing with SKIP LOCKED, multiple workers safely concurrent
BEGIN;

-- Each worker grabs the next task that is NOT locked
SELECT job_id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Rows already locked by other workers are skipped
-- instead of this worker waiting on them

UPDATE job_queue
SET status = 'processing', worker_id = 'worker-7'
WHERE job_id = 1042;

COMMIT;
-- On successful processing: set status to 'done'
-- On failure: set status back to 'pending' for a retry

SKIP LOCKED has been supported by PostgreSQL since version 9.5, by MySQL/InnoDB since version 8.0, and by Oracle for a long time. Microsoft SQL Server has no direct SKIP LOCKED clause but achieves similar behavior via the READPAST hint. This pattern is the standard approach for hand-built job queues on top of a relational table, without introducing an additional message queue system.

5. NOWAIT and lock timeout handling

By default, a transaction trying to access an already-locked row with SELECT FOR UPDATE waits until the locking transaction releases the lock, or until a configured timeout is reached. For use cases where waiting is not an acceptable option, for example a user action that needs immediate feedback, the NOWAIT clause offers an alternative: instead of waiting, the database immediately raises an error if the row is already locked.

-- NOWAIT: immediate error instead of waiting
BEGIN;
SELECT * FROM seats
WHERE seat_id = 15 AND event_id = 200
FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row, if already locked
-- Application code catches the error and immediately shows
-- "This seat is currently being reserved by someone else"
COMMIT;

-- Alternative: bounded wait time instead of an immediate error (PostgreSQL)
SET LOCAL lock_timeout = '3s';
BEGIN;
SELECT * FROM seats
WHERE seat_id = 15 AND event_id = 200
FOR UPDATE;
-- Waits at most 3 seconds, then errors instead of waiting indefinitely
COMMIT;

-- MySQL: configure timeout globally or per session
SET SESSION innodb_lock_wait_timeout = 5;

A blanket, unbounded wait for locks is almost always the wrong choice in interactive applications, because a user should not wait forever on a hanging request. NOWAIT fits cases where immediate feedback matters more than an automatic success, while a moderate lock timeout of a few seconds offers a good trade-off for most other cases.

6. Deadlocks: cause, detection, avoidance

A deadlock arises when two transactions block each other: transaction A holds a lock on row 1 and waits for row 2, while transaction B simultaneously holds row 2 and waits for row 1. Neither transaction can ever proceed without external intervention. Relational databases detect this situation automatically via an internal deadlock detector and forcibly abort one of the two transactions with an error, so the other can proceed.

-- Deadlock scenario: inconsistent access order
-- Transaction A:
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- ... some time passes ...
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- waits, because transaction B already holds account_id 2

-- Transaction B, at the same time:
BEGIN;
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- ... some time passes ...
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- waits on transaction A, classic deadlock

-- The database detects the cycle and aborts one transaction:
-- ERROR: deadlock detected
-- Application code must catch this error and restart the
-- affected transaction entirely

-- AVOIDANCE: consistent access order across the whole application
-- Always lock in ascending account_id order, never mixed
SELECT * FROM accounts WHERE account_id IN (1, 2)
ORDER BY account_id FOR UPDATE;

The most reliable strategy against deadlocks is a consistent, application-wide access order: if every transaction always locks rows in the same order, for example ascending by primary key, the circular wait cycle that constitutes a deadlock can never form in the first place. In addition, every transaction using SELECT FOR UPDATE should be prepared for a deadlock error and automatically restart the entire transaction, similar to the retry logic for serialization failures under SERIALIZABLE.

7. Pessimistic locking compared across databases

Although SELECT FOR UPDATE is part of the SQL standard, the extensions and detail behavior differ noticeably between major databases. The table below summarizes the most important differences.

8. When to use pessimistic instead of optimistic locking

Pessimistic locking is the right choice when the probability of conflict on a row is high and the transaction duration can be kept short. Classic examples are queue processing with many concurrent workers, seat reservations for a limited allotment right before a sale starts, or financial transactions where an account balance is read, checked, and updated within a single short transaction.

Pessimistic locking is unsuitable, on the other hand, when a long time span dependent on user interaction lies between reading and writing, for example a form a user leaves open for minutes. A row lock held over that time would unnecessarily block other users and, in the worst case, lead to an effective system standstill if many users leave forms open simultaneously. For such cases, optimistic locking with a version column is the far better choice, as described in the deep-dive article on optimistic locking.

9. Performance implications and best practices

Every lock held with SELECT FOR UPDATE reduces effective concurrency for exactly the affected rows, which is why transaction duration under pessimistic locking should be kept as short as possible. All slow operations that do not directly involve the locked row, such as calls to external APIs, should happen outside the transaction to minimize lock time.

A proven best practice is to always acquire locks in a fixed, application-wide order to structurally rule out deadlocks, rather than relying on the database's deadlock detector as the sole safeguard. In addition, every application using SELECT FOR UPDATE should work with a sensible lock timeout and catch deadlock errors with bounded retry logic instead of passing them through to the user unhandled.

-- Best-practice combination: lock timeout plus retry logic
-- Pseudocode for application logic

-- function transferWithLock(fromId, toId, amount, maxAttempts = 3) {
--   for (let attempt = 1; attempt <= maxAttempts; attempt++) {
--     try {
--       db.execute("BEGIN");
--       db.execute("SET LOCAL lock_timeout = '2s'");
--
--       // Always lock in ascending order to structurally avoid deadlocks
--       const ids = [fromId, toId].sort();
--       db.query(
--         "SELECT balance FROM accounts WHERE account_id = ANY(?) " +
--         "ORDER BY account_id FOR UPDATE", [ids]
--       );
--
--       db.execute("UPDATE accounts SET balance = balance - ? WHERE account_id = ?", [amount, fromId]);
--       db.execute("UPDATE accounts SET balance = balance + ? WHERE account_id = ?", [amount, toId]);
--       db.execute("COMMIT");
--       return { success: true };
--     } catch (error) {
--       db.execute("ROLLBACK");
--       if ((error.code === 'DEADLOCK' || error.code === 'LOCK_TIMEOUT') && attempt < maxAttempts) {
--         continue;  // full restart with a short, randomized backoff
--       }
--       throw error;
--     }
--   }
-- }

10. Summary

Pessimistic locking with SELECT FOR UPDATE locks a row already at read time, thereby preventing conflicts instead of detecting them afterward. SKIP LOCKED turns this into a reliable pattern for concurrent queue processing, where multiple workers do not block each other. NOWAIT and lock timeouts prevent unbounded waiting in interactive use cases, while a consistent access order is the most reliable strategy against deadlocks.

Pessimistic locking is best suited for short transactions with a high probability of conflict, while optimistic locking is the better choice for longer time spans dependent on user interaction. Both approaches are not mutually exclusive and can be combined deliberately depending on the table and access profile, always embedded in an isolation level appropriate for the use case.

11. FAQ: Pessimistic Locking with SELECT FOR UPDATE