What Is Pessimistic Locking?

Pessimistic locking is a concurrency-control technique that prevents conflicting operations by locking data before it is modified. Other transactions attempting incompatible operations on the same data must wait, fail immediately, or time out until the lock is released.

The approach assumes conflicts are likely enough that preventing them is preferable to allowing concurrent work and detecting conflicts later. Pessimistic locking is commonly used when concurrent updates to the same records could violate important business rules or when retrying failed work would be expensive.

Table of Contents

• The Problem Pessimistic Locking Solves
• How Pessimistic Locking Works
  • SELECT FOR UPDATE
  • Lock Lifetime
  • What Happens to Other Transactions?
• Lock Modes
• Pessimistic vs Optimistic Locking
• Pessimistic Locking and Transactions
• Lock Waiting, Timeouts, and NOWAIT
• Using SKIP LOCKED
• Deadlocks
  • How Deadlocks Happen
  • Reducing Deadlocks
• Lock Scope and Contention
• When Pessimistic Locking Works Well
• When Pessimistic Locking Works Poorly
• Production Design Example
• Common Pessimistic Locking Mistakes
• Production Checklist
• Frequently Asked Questions
  • Does SELECT FOR UPDATE Block Normal Reads?
  • When Are Pessimistic Locks Released?
  • Does Pessimistic Locking Prevent Deadlocks?
  • Should Every Update Use SELECT FOR UPDATE?
  • Is Pessimistic Locking the Same as Distributed Locking?
• Conclusion
  • More Articles to Read

The Problem Pessimistic Locking Solves

Consider an inventory record:

product_id = 42
available_quantity = 1

Two transactions attempt to reserve the last item at approximately the same time.

Transaction A                  Transaction B

READ quantity = 1              READ quantity = 1
       │                              │
item available                 item available
       │                              │
reserve item                   reserve item

If the application performs a separate read followed by a write without sufficient concurrency control, both transactions can make a business decision based on the same old state.

The result can be overselling, duplicate allocation, invalid state transitions, or other race conditions.

Pessimistic locking changes the flow by allowing one transaction to reserve exclusive access to the relevant data while it makes the decision.

Transaction A                  Transaction B

lock product 42
       │
       │                       request same lock
       │                              │
read quantity = 1                    WAIT
       │                              │
reserve item                         WAIT
       │                              │
COMMIT                               WAIT
       │                              │
release lock ────────────────────────→ continue
                                      │
                               read quantity = 0
                                      │
                               reservation rejected

Transaction B cannot make its reservation decision using the state that existed before Transaction A completed.

How Pessimistic Locking Works

Pessimistic locking acquires a database lock before performing work that depends on the protected state.

A typical sequence is:

The important property is that conflicting transactions cannot independently pass through the same critical section at the same time.

SELECT FOR UPDATE

In SQL databases, a common way to explicitly acquire a row-level lock is SELECT ... FOR UPDATE.

BEGIN;

SELECT id, available_quantity
FROM products
WHERE id = 42
FOR UPDATE;

UPDATE products
SET available_quantity = available_quantity - 1
WHERE id = 42;

COMMIT;

The FOR UPDATE clause tells the database that the selected row will participate in an update and should be protected from conflicting modifications.

If another transaction attempts to acquire an incompatible lock on the same row before the first transaction completes, it normally has to wait.

This creates a critical section around the business operation:

LOCK
  │
  ├── read
  ├── validate
  ├── calculate
  └── update
  │
UNLOCK

The validation is performed while the protected state cannot be changed by another conflicting transaction.

Lock Lifetime

A pessimistic database lock is normally tied to the transaction that acquired it.

BEGIN;

SELECT *
FROM accounts
WHERE id = 100
FOR UPDATE;

-- lock is still held

UPDATE accounts
SET balance = balance - 50
WHERE id = 100;

COMMIT;

-- lock released

The lock should therefore be considered part of the transaction boundary.

Long transactions create long lock lifetimes. A transaction that holds a lock for 20 seconds can force other transactions to wait for most of those 20 seconds.

What Happens to Other Transactions?

A conflicting transaction can experience several possible behaviors depending on the SQL operation and database configuration:

• wait until the lock becomes available;
• fail immediately;
• wait until a lock timeout expires;
• skip the locked row;
• be aborted because of a deadlock.

The default behavior for many conflicting operations is waiting.

Transaction A
    │
    ├── LOCK row
    │
    │
    │          Transaction B
    │              │
    │              ├── request lock
    │              │
    │              ↓
    │             WAIT
    │              │
    ├── COMMIT     │
    │              │
    └── unlock ────┘
                   │
                   ↓
              lock acquired

Waiting is not necessarily an error. It is one of the mechanisms through which pessimistic locking serializes conflicting work.

Lock Modes

Databases usually support multiple lock modes rather than one universal lock.

At a high level, locks can distinguish between operations that only need to read data and operations that intend to modify it.

The exact lock modes and compatibility rules vary between database engines.

SQL statements can also acquire locks implicitly. An UPDATE normally needs to protect the rows it modifies even if the application never explicitly executes SELECT ... FOR UPDATE.

Explicit pessimistic locking is most useful when the application must first read state, make a decision, and then modify data while ensuring that another transaction cannot invalidate that decision in between.

Pessimistic vs Optimistic Locking

Pessimistic and optimistic locking represent two different approaches to concurrency conflicts.

Pessimistic locking assumes a conflict is important enough to coordinate access before it happens.

Optimistic locking allows concurrent work and detects whether another writer changed the state before accepting the final update.

Consider two clients modifying the same account.

With pessimistic locking:

Client A: LOCK → calculate → UPDATE → COMMIT
Client B:          WAIT          → LOCK → calculate → UPDATE

With optimistic locking:

Client A: READ v10 → calculate → UPDATE WHERE version = 10 ✓
Client B: READ v10 → calculate → UPDATE WHERE version = 10 ✗

The optimistic approach allows both clients to perform work concurrently, but one can later lose the race.

The pessimistic approach makes the second client wait before it performs protected work based on the record.

Pessimistic Locking and Transactions

Pessimistic locking depends heavily on correct transaction boundaries because locks are typically held until the transaction commits or rolls back.

Consider a money transfer:

BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN (100, 200)
ORDER BY id
FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE id = 100;

UPDATE accounts
SET balance = balance + 100
WHERE id = 200;

COMMIT;

The transaction provides atomicity across both updates. The locks prevent conflicting transactions from modifying the protected account rows while the transfer is being calculated and applied.

Transactions and locks therefore solve related but different problems:

Transaction
    │
    └── defines what commits or rolls back together

Lock
    │
    └── coordinates concurrent access to protected data

Database Transactions covers transaction boundaries, commit, rollback, ACID properties, and concurrency in more detail.

Lock Waiting, Timeouts, and NOWAIT

Waiting indefinitely for a lock is rarely desirable in a production application.

Suppose Transaction A holds a row lock while Transaction B needs the same row:

Transaction A             Transaction B

LOCK row 42               LOCK row 42
    │                          │
work                          WAIT
    │                          │
work                          WAIT
    │                          │
COMMIT                        continue

If Transaction A becomes slow, Transaction B becomes slow as well. Additional requests can queue behind it.

This can create a chain of blocked transactions.

Some databases provide NOWAIT when the application prefers immediate failure:

SELECT id, status
FROM orders
WHERE id = 9001
FOR UPDATE NOWAIT;

The intended behavior becomes:

lock available
    │
    └── acquire immediately

lock unavailable
    │
    └── fail immediately

This is useful when waiting would provide little value and the application already knows how to retry, reject, or defer the operation.

Lock timeouts provide another option:

request lock
    │
    ↓
wait up to configured limit
    │
    ├── lock available → continue
    │
    └── timeout        → abort operation

The correct strategy depends on latency requirements and the cost of retrying the operation.

Using SKIP LOCKED

Some databases support SKIP LOCKED, which can be particularly useful for queue-like worker processing.

Consider a jobs table:

SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

If another worker already locked some pending jobs, those rows are skipped instead of making the new worker wait.

Pending jobs:

1   locked by Worker A
2   locked by Worker A
3   available
4   available
5   available

Worker B uses SKIP LOCKED

        ↓

receives 3, 4, 5

This can allow several workers to safely claim independent jobs concurrently.

The semantics are different from a normal query. Locked rows temporarily disappear from the candidate set, so SKIP LOCKED should be used for workloads where that behavior is intentional, such as job claiming rather than general-purpose data retrieval.

Deadlocks

Pessimistic locking introduces the possibility of deadlocks.

A deadlock occurs when transactions wait on each other in a cycle and none can continue.

How Deadlocks Happen

Consider two accounts:

Transaction A                  Transaction B

LOCK account 1                 LOCK account 2
       │                              │
       ↓                              ↓
LOCK account 2                 LOCK account 1
       │                              │
       ↓                              ↓
     WAIT                           WAIT

Transaction A cannot continue until Transaction B releases Account 2.

Transaction B cannot continue until Transaction A releases Account 1.

Transaction A
    │
    └── waits for B
             │
             ↓
        Transaction B
             │
             └── waits for A

The cycle cannot resolve through waiting alone.

Database engines typically detect such deadlocks and abort one transaction so the other can continue.

Applications using pessimistic locking must therefore be prepared for a transaction to fail even when the SQL and business operation are otherwise valid.

Reducing Deadlocks

One of the most effective techniques is acquiring locks in a consistent order.

Instead of:

Transaction A: lock 1 → lock 2
Transaction B: lock 2 → lock 1

both transactions should use:

Transaction A: lock 1 → lock 2
Transaction B: lock 1 → lock 2

This does not eliminate all possible deadlocks, but it removes a common source.

Other techniques include:

• keeping transactions short;
• locking only required rows;
• accessing tables and records in a predictable order;
• avoiding external network calls inside transactions;
• indexing queries so fewer rows need to be examined or locked;
• retrying transactions aborted because of deadlocks.

Deadlock retries should be bounded and should rerun the entire affected transaction from a valid state.

Lock Scope and Contention

The effectiveness of pessimistic locking depends heavily on how much data is locked and for how long.

Compare two designs:

Design A

LOCK entire table
      │
      ↓
many unrelated requests wait


Design B

LOCK one required row
      │
      ↓
unrelated rows remain available

Narrow lock scope generally improves concurrency.

Query design matters because the database must identify the rows being protected. Missing indexes or broad predicates can cause a locking operation to touch far more data than expected.

Lock duration matters just as much.

Transaction A

LOCK
 │
 ├── query
 ├── calculate
 ├── call remote API ───────── 4 seconds
 ├── more processing
 │
COMMIT

Every conflicting transaction may be blocked while the remote API call is in progress.

A safer design often moves external work outside the locked transaction:

external preparation
       │
       ↓
BEGIN
       │
LOCK
       │
validate current state
       │
update
       │
COMMIT

The final validation still needs to happen after the lock is acquired because state may have changed during the external preparation.

When Pessimistic Locking Works Well

Pessimistic locking is useful when concurrent modifications are expected and allowing conflicting operations to proceed would create expensive failures or complex retries.

Typical examples include:

• inventory reservations;
• financial balance updates;
• resource allocation;
• seat or slot reservation;
• state-machine transitions;
• job claiming;
• workflows where several dependent values must be checked before writing;
• operations where a failed optimistic attempt would waste substantial work.

It is particularly useful when the critical section is short.

BEGIN
  │
LOCK
  │
validate
  │
UPDATE
  │
COMMIT

Duration: milliseconds

A short lock can provide strong coordination with manageable contention.

When Pessimistic Locking Works Poorly

Pessimistic locking becomes expensive when transactions hold locks for long periods or when many operations compete for the same records.

Potential symptoms include:

• high lock-wait latency;
• requests timing out;
• deadlocks;
• low throughput on hot rows;
• large numbers of blocked sessions;
• connection-pool exhaustion;
• latency spikes that propagate through dependent services.

Consider 1,000 requests competing for the same record:

Request 1  → LOCK → work
Request 2  → WAIT
Request 3  → WAIT
Request 4  → WAIT
...
Request 1000 → WAIT

The database has effectively serialized that workload through one resource.

If every transaction takes 100 milliseconds, the problem cannot be solved merely by adding more application servers. The locked resource is the bottleneck.

High-contention systems may require a different design such as atomic database operations, partitioned state, queues, single-writer processing, or reducing the amount of shared mutable state.

Production Design Example

Consider an inventory service that must reserve products without overselling.

The table contains:

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    available_quantity INTEGER NOT NULL
);

The current state is:

product_id = 847
available_quantity = 3

An order requests two units.

The service begins a transaction and locks the inventory row:

BEGIN;

SELECT product_id, available_quantity
FROM inventory
WHERE product_id = 847
FOR UPDATE;

The application verifies:

available_quantity = 3
requested_quantity = 2

3 >= 2

reservation allowed

It then updates the quantity:

UPDATE inventory
SET available_quantity = available_quantity - 2
WHERE product_id = 847;

INSERT INTO reservations (
    order_id,
    product_id,
    quantity
)
VALUES (
    9001,
    847,
    2
);

COMMIT;

The resulting inventory is:

available_quantity = 1

Suppose another transaction attempted to reserve two units while the first transaction held the lock.

Transaction A                    Transaction B

FOR UPDATE product 847
       │
quantity = 3                     FOR UPDATE product 847
       │                                │
reserve 2                              WAIT
       │                                │
quantity = 1                           WAIT
       │                                │
COMMIT ────────────────────────────────→ lock acquired
                                        │
                                  quantity = 1
                                        │
                                  request = 2
                                        │
                                  reject reservation

Transaction B reads the state only after Transaction A's reservation becomes visible.

A Python service can express the same transaction explicitly:

class InsufficientInventoryError(Exception):
    pass


def reserve_inventory(
    connection,
    order_id: int,
    product_id: int,
    quantity: int,
) -> None:
    with connection.transaction():
        row = connection.execute(
            """
            SELECT available_quantity
            FROM inventory
            WHERE product_id = %s
            FOR UPDATE
            """,
            (product_id,),
        ).fetchone()

        if row is None:
            raise ValueError("Product does not exist")

        if row["available_quantity"] < quantity:
            raise InsufficientInventoryError()

        connection.execute(
            """
            UPDATE inventory
            SET available_quantity = available_quantity - %s
            WHERE product_id = %s
            """,
            (quantity, product_id),
        )

        connection.execute(
            """
            INSERT INTO reservations (
                order_id,
                product_id,
                quantity
            )
            VALUES (%s, %s, %s)
            """,
            (order_id, product_id, quantity),
        )

The transaction should contain only the work that needs the protected inventory state.

Calling a payment provider while holding the inventory lock would substantially increase lock duration:

LOCK inventory
      │
      ├── reserve inventory
      ├── call payment provider ─── 3 seconds
      ├── wait
      └── COMMIT

Other reservations wait for the entire period.

External coordination should instead be designed explicitly using short local transactions and appropriate distributed workflow patterns when required.

Production monitoring should include lock-specific metrics:

Transaction p95:          28 ms
Lock wait p95:             6 ms
Lock wait p99:            44 ms
Deadlocks:                 3/hour
Lock timeouts:             1/hour
Transactions > 5 sec:      0

A sudden increase in lock wait time can reveal contention even when individual SQL statements remain fast.

Useful metrics include:

• lock wait duration;
• number of blocked transactions;
• deadlock count;
• lock timeout count;
• transaction duration;
• long-running transactions;
• connection-pool utilization;
• queries responsible for the most waiting;
• hot tables and rows.

Lock contention is therefore both a correctness concern and a performance concern.

Common Pessimistic Locking Mistakes

• Holding locks during external API calls. Network latency directly extends lock duration.
• Starting transactions too early. Locks should not be held while unrelated application work runs.
• Locking more rows than necessary. Broad locking reduces concurrency.
• Ignoring indexes on locking queries. Poor access paths can increase the scope and duration of database work.
• Assuming deadlocks cannot happen. Multiple locks acquired in inconsistent orders can create cycles.
• Not retrying deadlock victims. Databases may abort otherwise valid transactions to break deadlocks.
• Allowing unlimited lock waits. Blocked requests can consume the connection pool and propagate latency.
• Using pessimistic locks when a single atomic UPDATE would be sufficient. Explicit read-before-write locking can sometimes be avoided entirely.
• Locking a record before slow computation. Perform non-state-dependent preparation before acquiring the lock when possible.
• Assuming row locking solves distributed coordination. A database row lock protects operations coordinated through that database, not arbitrary external resources.
• Ignoring contention metrics. Correct transactions can still create an overloaded system.
• Using locks to compensate for unclear business invariants. The protected rule should be understood before selecting the concurrency mechanism.

Production Checklist

• Define the exact invariant that requires protected access.
• Lock only the records required to protect that invariant.
• Acquire locks inside a clear transaction boundary.
• Keep locked transactions as short as possible.
• Do not wait on external services while holding database locks.
• Acquire multiple locks in a consistent order.
• Understand the database engine's lock modes and compatibility rules.
• Choose between waiting, NOWAIT, timeout, and SKIP LOCKED intentionally.
• Handle deadlock errors explicitly.
• Retry deadlock victims only when the complete operation is safe to retry.
• Use bounded retries and backoff where appropriate.
• Ensure locking queries use suitable indexes.
• Prefer atomic SQL operations when they can enforce the invariant without explicit read locking.
• Monitor lock wait duration.
• Monitor deadlocks and lock timeouts.
• Detect long-running transactions.
• Watch connection-pool utilization during contention.
• Load-test concurrent access to hot records.

Frequently Asked Questions

Pessimistic locking is straightforward conceptually, but its behavior depends on transactions, lock compatibility, database isolation, and the exact SQL being executed.

Does SELECT FOR UPDATE Block Normal Reads?

Not necessarily. In databases using MVCC, a normal non-locking SELECT can often read an appropriate committed row version while another transaction holds a row lock.

Operations requesting incompatible locks or attempting conflicting modifications are the ones that typically wait. Exact behavior depends on the database engine and isolation level.

When Are Pessimistic Locks Released?

Transaction-level database locks are generally released when the transaction commits or rolls back.

This is why transaction duration directly affects lock duration.

Does Pessimistic Locking Prevent Deadlocks?

No. Pessimistic locking can create deadlocks when transactions acquire multiple locks in conflicting orders.

Database engines typically detect deadlocks and abort one participant. Applications should be designed to handle that outcome.

Should Every Update Use SELECT FOR UPDATE?

No. An UPDATE already performs the database's required concurrency control for the rows it modifies.

Explicit SELECT ... FOR UPDATE is useful when an application must read protected state and make a decision before issuing subsequent writes. In many cases, a single conditional UPDATE can enforce the same invariant more efficiently.

Is Pessimistic Locking the Same as Distributed Locking?

No. Pessimistic locking describes a concurrency strategy, while a distributed lock coordinates participants across processes or machines through a shared coordination system.

A database row lock can coordinate multiple application instances when they all use the same database transaction protocol, but it does not automatically protect external resources or independent systems.

What Is a Distributed Lock? covers coordination across distributed application instances in more detail.

Conclusion

Pessimistic locking protects shared data by coordinating access before conflicting work proceeds. A transaction acquires a lock, reads and validates the protected state, performs its updates, and releases the lock when the transaction ends.

This approach is effective when conflicts are expected, the protected critical section is short, and allowing multiple transactions to perform conflicting work would be expensive or unsafe.

The trade-off is contention. Locks create waiting, can produce deadlocks, and can turn hot records into serialization bottlenecks. Lock scope, transaction duration, acquisition order, timeouts, and production monitoring therefore matter as much as the locking statement itself.

Key takeaway: pessimistic locking prevents conflicting transactions from independently acting on the same protected state, but the protection is only efficient when locks are narrow, predictable, and short-lived.

More Articles to Read

• Database Concurrency
• Database Locks and Transactions
• What Is MVCC?