# Trade‑Offs Between Optimistic and Pessimistic Locking in Databases: A Complete Guide

> Explore optimistic vs pessimistic locking trade-offs in databases. Learn how to prevent concurrent modifications and detect collisions for optimal performance and consistency. Maximize throughput with version checks.

- Repository: [ByteByteGoHq/system-design-101](https://github.com/ByteByteGoHq/system-design-101)
- Tags: deep-dive
- Published: 2026-02-28

---

**Pessimistic locking prevents concurrent modifications by acquiring database locks before any changes occur, ensuring strict consistency but risking contention, whereas optimistic locking assumes conflicts are rare and detects collisions at commit time using version checks, trading immediate isolation for higher throughput.**

Managing concurrent access is fundamental to database reliability in multi‑user systems. The ByteByteGoHq/system‑design‑101 repository provides a comprehensive analysis of the trade‑offs between optimistic and pessimistic locking in databases, detailing how each strategy anticipates conflicts and impacts performance. According to the guide in [`data/guides/pessimistic-vs-optimistic-locking.md`](https://github.com/ByteByteGoHq/system-design-101/blob/main/data/guides/pessimistic-vs-optimistic-locking.md), your choice depends on conflict probability, consistency requirements, and the complexity your application can handle.

## Fundamental Assumptions and Mechanics

### Pessimistic Locking: Conflict is Inevitable

Pessimistic locking operates on the assumption that conflicts are **likely** and pre‑emptively blocks other transactions. As documented in the system‑design‑101 guide, this approach acquires a lock—typically row‑level—before any modification occurs, forcing concurrent sessions to wait until the transaction commits and the lock is released.

In SQL, this is implemented using `SELECT … FOR UPDATE` or explicit `LOCK` statements. The lock is held for the entire transaction duration, guaranteeing that no other transaction can read or modify the data while the current operation is in progress.

### Optimistic Locking: Conflict is Rare

Optimistic locking assumes conflicts are **rare** and allows multiple transactions to proceed concurrently without blocking. Instead of locks, it relies on a **version column** or timestamp read alongside the data. At commit time, the system compares the current version against the value read earlier; a mismatch indicates a conflict, triggering a rollback and requiring client‑side retry logic.

This approach shifts complexity to the application layer but eliminates the blocking behavior and lock queues that characterize pessimistic strategies.

## Performance Impact and Scalability Trade‑Offs

The trade‑offs between optimistic and pessimistic locking in databases become most apparent under varying load conditions.

**Pessimistic locking** guarantees data integrity but can cause **high contention** and blocking, especially under heavy write loads. As noted in [`data/guides/pessimistic-vs-optimistic-locking.md`](https://github.com/ByteByteGoHq/system-design-101/blob/main/data/guides/pessimistic-vs-optimistic-locking.md), lock queues grow when many concurrent writers target the same rows, creating scalability bottlenecks and potential deadlocks.

**Optimistic locking** reduces lock contention and improves **throughput** for read‑heavy or write‑light workloads. However, it incurs **retry overhead** when conflicts do occur, requiring additional CPU cycles and application logic for repeated attempts. The system‑design‑101 repository emphasizes that this strategy scales better for web‑scale services where concurrent reads dominate and write collisions are statistically infrequent.

## Implementation Examples

### Pessimistic Locking with `SELECT FOR UPDATE`

The following PostgreSQL example demonstrates row‑level pessimistic locking. The `FOR UPDATE` clause explicitly locks the selected row until the transaction commits, preventing any concurrent modifications.

```sql
-- Begin a transaction
BEGIN;

-- Acquire an exclusive lock on the row you intend to update
SELECT * FROM accounts
WHERE account_id = 123
FOR UPDATE;   -- ← this locks the selected row

-- Perform the update while the lock is held
UPDATE accounts
SET balance = balance - 50
WHERE account_id = 123;

-- Commit the transaction, releasing the lock
COMMIT;

```

This implementation matches the description in the ByteByteGoHq/system‑design‑101 guide: pessimistic locking "assumes conflicts will occur and locks the data before any changes are made."

### Optimistic Locking with Version Columns

First, add a version column to your table:

```sql
ALTER TABLE accounts
ADD COLUMN version BIGINT NOT NULL DEFAULT 0;

```

Then implement the retry logic in application code. This Node.js example using the `pg` driver follows the pattern described in the repository:

```javascript
const { Client } = require('pg');
const client = new Client();
await client.connect();

async function transferFunds(accountId, amount) {
  while (true) {
    // 1️⃣ Read the current balance and version
    const { rows } = await client.query(
      'SELECT balance, version FROM accounts WHERE account_id = $1',
      [accountId]
    );
    const { balance, version } = rows[0];

    // 2️⃣ Compute new balance
    const newBalance = balance - amount;

    // 3️⃣ Try to update using the version as a check
    const res = await client.query(
      `UPDATE accounts
       SET balance = $1, version = version + 1
       WHERE account_id = $2 AND version = $3`,
      [newBalance, accountId, version]
    );

    // 4️⃣ If no rows were updated, a conflict occurred → retry
    if (res.rowCount === 1) {
      // Success
      break;
    }

    // Optional: exponential back‑off before retrying
    await new Promise(r => setTimeout(r, 100));
  }
}

```

The `WHERE … version = $3` clause ensures the row has not changed since it was read. If `res.rowCount` equals zero, another transaction modified the data, and the loop retries. This implements the optimistic principle of checking "for conflicts when changes are committed" as specified in the system‑design‑101 documentation.

## Decision Framework: When to Use Each Strategy

Choose **pessimistic locking** when:

- **Consistency is non‑negotiable**, such as in financial transactions where data integrity outweighs latency.
- You face **high write contention**, like inventory updates where multiple users simultaneously decrement stock levels.
- The cost of a rollback or application‑level retry exceeds the performance penalty of waiting for a database lock.

Choose **optimistic locking** when:

- **Conflicts are statistically rare**, such as user profile updates where simultaneous edits are uncommon.
- You require maximum read/write **throughput** for web‑scale services with many concurrent readers.
- Your application can implement robust **retry mechanisms** with exponential back‑off to handle occasional collision scenarios.

## Summary

- **Pessimistic locking** pre‑emptively acquires database locks (e.g., `FOR UPDATE`), assuming conflicts are likely; it ensures strict consistency but risks high contention and scalability limits under heavy concurrent write loads.
- **Optimistic locking** uses version columns or timestamps to detect conflicts at commit time, assuming collisions are rare; it maximizes throughput and scales better for read‑heavy workloads but requires application‑level retry logic.
- According to the ByteByteGoHq/system‑design‑101 repository, hold pessimistic locks for the **minimum possible time** and at the **most granular level** (row‑level rather than table‑level) to reduce blocking.
- For optimistic locking, maintain a dedicated **version column** and implement **retry logic** with back‑off strategies to handle version mismatches gracefully.

## Frequently Asked Questions

### What is the main difference between optimistic and pessimistic locking?

Pessimistic locking assumes conflicts will occur and locks data resources immediately using database mechanisms like `SELECT FOR UPDATE`, preventing other transactions from accessing the data until the lock is released. Optimistic locking assumes conflicts are rare, allows concurrent access without locks, and verifies data integrity at commit time using version numbers or timestamps, rolling back and retrying only if conflicts are detected.

### When should I use pessimistic locking over optimistic locking?

Use pessimistic locking when data consistency is critical and cannot tolerate retries, such as in financial transactions or high‑contention inventory systems where multiple users simultaneously modify the same records. This approach is preferable when the computational cost of handling rollbacks exceeds the performance penalty of waiting for locks, as noted in the system‑design‑101 guide.

### How do I implement optimistic locking in SQL?

Add a **version column** (typically an integer or timestamp) to your table, read this value when fetching the record, and include it in the `WHERE` clause of your `UPDATE` statement (e.g., `WHERE account_id = ? AND version = ?`). If the update affects zero rows, a conflict occurred; your application must then retry the read‑modify‑write cycle with the updated version number, incrementing the version only on successful commits.

### Does optimistic locking improve database performance?

Yes, optimistic locking generally improves **throughput** and scalability for read‑heavy workloads by eliminating lock contention and allowing concurrent readers and writers without blocking. However, under high‑conflict scenarios where many transactions target the same rows, the overhead of frequent transaction retries and rollbacks can degrade performance compared to pessimistic locking.