Pessimistic Locking Low-Level Design: SELECT FOR UPDATE, Lock Timeouts, and Deadlock Prevention

What Is Pessimistic Locking?

Pessimistic locking is a concurrency control strategy that assumes conflicts are likely and acquires locks before reading or modifying data. By holding the lock throughout the transaction, it prevents other transactions from modifying the locked data concurrently. This eliminates conflicts entirely at the cost of blocking and reduced concurrency.

Pessimistic locking is implemented at the database level via SELECT FOR UPDATE and related constructs, or at the application level via advisory locks.

SELECT FOR UPDATE

SELECT FOR UPDATE is the primary mechanism for row-level pessimistic locking in SQL databases. It acquires an exclusive lock on the selected rows, preventing other transactions from acquiring any lock on those rows until the current transaction commits or rolls back.

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
-- Row 42 is now exclusively locked
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;
-- Lock released on COMMIT

Key semantics:

The lock is acquired immediately when SELECT FOR UPDATE executes, not when the UPDATE executes.
Other transactions attempting SELECT FOR UPDATE on the same row will block until the lock is released.
Under MVCC (PostgreSQL), SELECT FOR UPDATE reads the latest committed version of the row (not the transaction's snapshot) to avoid reading a stale version before locking it.

SKIP LOCKED: Queue Pattern

SELECT FOR UPDATE SKIP LOCKED skips any rows that are currently locked and returns only unlocked rows. This is the standard pattern for work queues where multiple workers claim jobs concurrently:

BEGIN;
SELECT * FROM jobs
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Worker processes the claimed row
UPDATE jobs SET status = 'PROCESSING' WHERE id = :claimed_id;
COMMIT;

Without SKIP LOCKED, all workers would queue up waiting for the same row's lock. With SKIP LOCKED, each worker immediately claims a different unlocked row, providing highly concurrent job dispatch without blocking.

Lock Timeout Configuration

By default, SELECT FOR UPDATE waits indefinitely for a lock to become available. This can cause request pile-up if the lock holder is slow. Configure lock_timeout to bound wait time:

-- PostgreSQL: abort if lock not acquired within 500ms
SET lock_timeout = '500ms';
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
-- Raises LockNotAvailable if timeout exceeded

The appropriate timeout depends on expected lock hold times. A typical OLTP transaction holds locks for single-digit milliseconds; a timeout of 100-500ms is usually appropriate. Alert on high lock timeout rates as a signal of contention problems.

Row-Level vs. Table-Level Locks

Row-level locks: Only the specific rows selected are locked. Other rows in the same table can be read and modified concurrently. Standard for OLTP.
Table-level locks: Lock the entire table. Used for schema changes (DDL), bulk operations, or explicit LOCK TABLE statements. Block all concurrent row access.

Most SELECT FOR UPDATE usage acquires row-level locks. Table-level locks are rarely appropriate in high-concurrency systems.

Deadlocks

A deadlock occurs when two or more transactions are each waiting for locks held by the other, forming a circular dependency:

T1 holds lock on row A, waiting for lock on row B.
T2 holds lock on row B, waiting for lock on row A.
Neither can proceed — classic circular wait.

Databases automatically detect deadlocks by analyzing the wait-for graph. One transaction (the victim) is chosen and aborted, releasing its locks and allowing the other to proceed. The aborted transaction receives an error and must retry.

Deadlock Prevention: Lock Ordering

The most reliable deadlock prevention technique is to always acquire locks in a consistent global order. If all transactions acquire locks on rows sorted by primary key ascending, circular waits cannot form:

T1 and T2 both need rows 42 and 57.
Both acquire the lock on row 42 first (one blocks), then row 57.
No circular dependency is possible — the first acquirer holds both locks, the second waits for the first.

This requires coordination in application code: sort the IDs of rows to be locked before beginning the transaction.

Advisory Locks

PostgreSQL provides advisory locks: application-defined locks that are not tied to any specific table row. They are identified by an application-chosen integer key and managed explicitly:

SELECT pg_advisory_lock(12345);    -- Acquire exclusive advisory lock
-- Critical section
SELECT pg_advisory_unlock(12345);  -- Release

Advisory locks are useful for distributed mutual exclusion scenarios not tied to a specific row: for example, ensuring only one worker processes a given customer's batch at a time, identified by customer ID as the lock key.

SQL Schema

-- Audit log of lock wait events for performance analysis
CREATE TABLE LockWaitEvent (
    id            BIGSERIAL PRIMARY KEY,
    waiter_txn_id UUID NOT NULL,
    holder_txn_id UUID NOT NULL,
    resource_id   VARCHAR(256) NOT NULL,
    wait_start    TIMESTAMPTZ NOT NULL,
    wait_end      TIMESTAMPTZ,
    wait_ms       INTEGER GENERATED ALWAYS AS (
        EXTRACT(EPOCH FROM (wait_end - wait_start)) * 1000
    ) STORED
);

CREATE INDEX idx_lockwait_resource ON LockWaitEvent(resource_id, wait_start);
CREATE INDEX idx_lockwait_holder ON LockWaitEvent(holder_txn_id);

-- Deadlock events for monitoring
CREATE TABLE DeadlockEvent (
    id            BIGSERIAL PRIMARY KEY,
    txn_ids       JSONB NOT NULL,   -- all transactions in the deadlock
    victim_txn_id UUID NOT NULL,
    detected_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Python Implementation

import contextlib
from typing import Iterator, List, Optional

class PessimisticLockingClient:
    def __init__(self, db):
        self.db = db

    def acquire_row_lock(self, txn_id: str, row_id: str,
                         timeout_ms: int = 500) -> Optional[dict]:
        """
        Acquire exclusive lock on row_id with a timeout.
        Returns the locked row data, or None if timeout exceeded.
        """
        try:
            self.db.execute(f"SET lock_timeout = '{timeout_ms}ms'")
            row = self.db.query_one(
                "SELECT * FROM accounts WHERE id = %s FOR UPDATE",
                (row_id,)
            )
            return row
        except Exception as e:
            if "LockNotAvailable" in str(e) or "lock timeout" in str(e).lower():
                return None
            raise

    def acquire_skip_locked(self, table: str, status_col: str,
                            status_val: str, limit: int = 1) -> List[dict]:
        """
        Claim unlocked rows from a queue table using SKIP LOCKED.
        Returns list of claimed rows (already locked within current transaction).
        """
        rows = self.db.query_many(
            f"""SELECT * FROM {table}
                WHERE {status_col} = %s
                ORDER BY created_at
                LIMIT %s
                FOR UPDATE SKIP LOCKED""",
            (status_val, limit)
        )
        return rows

    def acquire_advisory_lock(self, lock_id: int,
                              shared: bool = False,
                              timeout: bool = False) -> bool:
        """
        Acquire a PostgreSQL advisory lock by integer key.
        Returns True if acquired, False if not available (timeout mode).
        """
        if timeout:
            fn = "pg_try_advisory_lock" if not shared else "pg_try_advisory_lock_shared"
        else:
            fn = "pg_advisory_lock" if not shared else "pg_advisory_lock_shared"

        result = self.db.query_one(f"SELECT {fn}(%s) AS acquired", (lock_id,))
        if timeout:
            return result["acquired"]
        return True  # Blocking variant always acquires (or raises)

    def release_advisory_lock(self, lock_id: int, shared: bool = False) -> None:
        fn = "pg_advisory_unlock" if not shared else "pg_advisory_unlock_shared"
        self.db.execute(f"SELECT {fn}(%s)", (lock_id,))

    @contextlib.contextmanager
    def advisory_lock_context(self, lock_id: int) -> Iterator[None]:
        """Context manager for advisory locks with guaranteed release."""
        self.acquire_advisory_lock(lock_id)
        try:
            yield
        finally:
            self.release_advisory_lock(lock_id)

FAQ

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “What is the difference between SELECT FOR UPDATE and SELECT FOR UPDATE SKIP LOCKED?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “SELECT FOR UPDATE blocks if the target row is already locked, waiting until the lock is released. SELECT FOR UPDATE SKIP LOCKED immediately skips any locked rows and returns only currently unlocked rows. SKIP LOCKED is ideal for work queues where multiple workers claim jobs concurrently — each worker gets a different job without blocking others.”
}
},
{
“@type”: “Question”,
“name”: “How should I configure lock_timeout?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Set lock_timeout based on expected lock hold duration. Typical OLTP transactions hold locks for 1-10ms; a timeout of 100-500ms covers legitimate wait times while preventing pile-ups from stalled transactions. Monitor lock timeout error rates as a contention signal. For interactive user-facing requests, 200-500ms is a reasonable upper bound.”
}
},
{
“@type”: “Question”,
“name”: “How does consistent lock ordering prevent deadlocks?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Deadlocks require a circular dependency: T1 waits for T2's lock while T2 waits for T1's lock. If all transactions acquire locks in the same order (e.g., by primary key ascending), circular waits cannot form — the transaction that holds the first lock in the order always acquires subsequent locks without being blocked by later acquirers.”
}
},
{
“@type”: “Question”,
“name”: “What are advisory locks used for?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Advisory locks provide application-defined mutual exclusion not tied to any table row. Common use cases: ensuring only one process handles a given customer's batch job (lock key = customer ID), distributed cron job election (only one worker runs a scheduled task), and coordinating non-database resources like external API rate limits or file processing.”
}
}
]
}

SELECT FOR UPDATE vs SKIP LOCKED: FOR UPDATE blocks on locked rows; SKIP LOCKED skips them — use SKIP LOCKED for concurrent work queues.
Lock timeout value: 100-500ms for OLTP; monitor timeout error rate as a contention signal.
Deadlock prevention ordering: Sort all row IDs to lock in ascending primary key order before beginning the transaction.
Advisory lock use cases: Distributed mutual exclusion not tied to specific rows — batch job claiming, cron election, external resource coordination.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How does SELECT FOR UPDATE acquire a row lock?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “SELECT FOR UPDATE instructs the database to place an exclusive row-level lock on every row returned by the query, blocking any other transaction that tries to read-for-update, update, or delete those rows until the holding transaction commits or rolls back. In PostgreSQL and MySQL InnoDB this is implemented as a next-key lock in the clustered index, covering both the row and the gap before it.”
}
},
{
“@type”: “Question”,
“name”: “How are lock timeouts configured to prevent indefinite waits?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Most databases expose a session-level timeout parameter (e.g., lock_timeout in PostgreSQL, innodb_lock_wait_timeout in MySQL) that causes a waiting transaction to abort with an error if it cannot acquire the lock within the specified interval. Applications should also set statement-level timeouts and implement retry logic with backoff so a single slow holder does not cascade into a queue of blocked clients.”
}
},
{
“@type”: “Question”,
“name”: “How does lock escalation affect concurrency?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Lock escalation converts many fine-grained row locks into a coarser page or table lock to reduce memory overhead, but the trade-off is that a table lock blocks all concurrent readers and writers on the entire table instead of just the locked rows. SQL Server performs escalation automatically when a transaction holds thousands of row locks; applications can hint ROWLOCK to suppress this, though at the cost of higher lock manager memory.”
}
},
{
“@type”: “Question”,
“name”: “What is the difference between shared and exclusive locks?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A shared (S) lock is acquired for reads and is compatible with other shared locks, so multiple readers can hold S locks on the same resource simultaneously. An exclusive (X) lock is acquired for writes and is incompatible with both S and X locks held by other transactions, ensuring a writer has sole access to the resource until it commits.”
}
}
]
}