Database Concurrency 101: Optimistic vs Pessimistic Locking

Optimistic locking assumes conflicts are rare and checks before committing, while pessimistic locking assumes conflicts are frequent and locks data upfront. This article explains both strategies, their trade-offs, and how to choose between them for your application.

Formula

The calculator estimates two metrics: retries per second (optimistic locking) and average lock wait time per transaction (pessimistic locking).

• Retries per second = Transactions per second × (Conflict rate / 100). This approximates how many transactions would fail and need a retry under optimistic locking.
• Pessimistic lock wait (seconds per transaction) = (Transaction duration in ms / 1000) × (Conflict rate / 100). This estimates the average time a transaction spends waiting for a lock, assuming the conflict rate equals the probability of waiting.

Variables: t = transactions per second, c = conflict rate (%), r = average transaction duration (ms).

Worked Example

Using the defaults: 100 transactions per second, 5% conflict rate, 50 ms average duration.

1. Retries per second = 100 × (5/100) = 5 retries/s. That means 5 out of 100 transactions would need a retry.
2. Lock wait per transaction = (50/1000) × (5/100) = 0.05 × 0.05 = 0.0025 seconds (2.5 ms). That is the average wait per transaction under pessimistic locking.

The calculator rounds to 2 decimals, so retries = 5.00, wait = 0.00 (since 0.0025 rounds to 0.00). For better precision, use more decimals or higher conflict rates.

Background: Why Concurrency Control Matters

When multiple users or processes access the same database record simultaneously, you risk lost updates, inconsistent reads, or corrupted state. Concurrency control mechanisms ensure transactions execute in a way that preserves data integrity. Two primary approaches dominate: optimistic and pessimistic locking. The choice affects performance, user experience, and system complexity.

Pessimistic locking has been around since the early days of relational databases. It locks a row (or table) when a transaction starts, preventing others from modifying it until the lock is released. Optimistic locking gained popularity with web applications and high-read, low-write workloads, where holding locks for long periods would degrade performance.

How Pessimistic Locking Works

In pessimistic locking, a transaction acquires a lock before reading or modifying data. The lock can be shared (multiple readers) or exclusive (one writer). Other transactions attempting to access the locked data either wait or fail, depending on the timeout settings.

• Shared lock (S): Allows multiple transactions to read but not write.
• Exclusive lock (X): Allows only one transaction to read/write; others must wait.
• Lock duration: Typically held until the transaction commits or rolls back.
• Implementation: SQL uses SELECT ... FOR UPDATE to acquire exclusive locks.

Example: User A opens a record for editing. The database locks that row. User B tries to edit the same row and waits until User A commits or times out. This guarantees no lost updates but can lead to long wait times and deadlocks.

How Optimistic Locking Works

Optimistic locking does not lock data during the transaction. Instead, it checks for conflicts at commit time. Each record has a version number (or timestamp) that increments on every update. When a transaction commits, it compares the version it read with the current version. If they match, the update proceeds; if not, the transaction aborts and must retry.

• Version column: Add an integer column (e.g., version) to the table.
• Update condition: UPDATE table SET ... WHERE id = ? AND version = ?
• On conflict: The affected row count is 0, indicating the version changed.
• Retry logic: The application re-reads the data, applies changes, and tries again.

Example: User A reads a record with version 3. User B reads the same record, updates it, and commits (version becomes 4). User A tries to commit with version 3 – the update fails. User A must refresh and reapply changes.

Choosing Between Optimistic and Pessimistic

The choice depends on your workload and conflict frequency.

• Use pessimistic locking when: conflicts are frequent, data is sensitive, or transactions are short and predictable. Examples: banking transfers, inventory decrements where overselling is unacceptable.
• Use optimistic locking when: conflicts are rare, reads dominate, or transactions are long (e.g., user editing a form). Examples: content management systems, social media posts, profile edits.
• Consider the cost: Pessimistic locking adds overhead for every transaction (lock acquisition/release). Optimistic locking adds overhead only on conflict (retry).
• Think about user experience: Pessimistic locking can cause users to wait, but ensures immediate feedback. Optimistic locking lets users work freely but may surprise them with a conflict at the end.

In practice, many systems use a hybrid: pessimistic for critical writes, optimistic for reads and less critical updates.

Common Problems and Misconceptions

Both approaches have pitfalls that developers often overlook.

• Deadlocks (pessimistic): Two transactions wait for each other’s locks, causing a deadlock. Databases detect and abort one, but this wastes resources. Mitigation: consistent lock ordering, shorter transactions, and timeouts.
• Lost updates (optimistic): If two transactions read the same version and both commit, the last write wins, losing the first update. Mitigation: always check the version in the WHERE clause.
• Long transactions (pessimistic): Holding locks for long periods blocks other users, reducing concurrency. Mitigation: keep transactions short, or use optimistic locking.
• Retry storms (optimistic): When conflict rate is high, retries increase load and may cause more conflicts. Mitigation: exponential backoff, or switch to pessimistic.
• Misconception: “Optimistic is always faster.” Not true – if conflicts are frequent, retries waste more time than locking.
• Misconception: “Pessimistic guarantees no conflicts.” It prevents lost updates but can still cause deadlocks and does not handle all concurrency issues (e.g., phantom reads).

Performance and Scalability Comparison

Pessimistic locking reduces concurrency because transactions wait. Optimistic locking allows full concurrency but risks retries. The calculator above gives a rough estimate of retry overhead versus lock wait.

For high-throughput systems (e.g., e-commerce), optimistic locking often scales better because it avoids lock contention. However, if the conflict rate exceeds ~10-20%, retries can degrade performance significantly. Pessimistic locking may be more predictable under high contention, but it does not scale well beyond a few nodes because locks require coordination.

In distributed databases, optimistic locking is often preferred because it avoids distributed lock management. But it requires careful handling of retries and idempotency.

Safety and Data Integrity Considerations

Both methods prevent lost updates, but they differ in other aspects.

• Isolation levels: Pessimistic locking works with READ COMMITTED or REPEATABLE READ. Optimistic locking typically uses READ COMMITTED and relies on version checks.
• Phantom reads: Neither fully prevents phantom reads unless you use SERIALIZABLE isolation. Optimistic locking with version checks on individual rows does not prevent new rows from appearing.
• Application-level vs database-level: Optimistic locking is often implemented in the application (e.g., ORM). Pessimistic locking is usually database-native.
• Retry safety: When retrying an optimistic transaction, ensure the operation is idempotent or that you re-read the latest data to avoid double-applying changes.

Alternatives to Locking

Sometimes locking is not the best tool. Consider these alternatives:

• Atomic operations: Use UPDATE table SET count = count - 1 instead of read-modify-write. This avoids locks entirely.
• Conditional updates: UPDATE ... WHERE count > 0 ensures you don’t go negative without locking.
• Queueing: Serialize writes through a message queue (e.g., Kafka) to avoid concurrent access.
• Event sourcing: Store events instead of state; conflicts become mergeable.

Frequently Asked Questions

What is the difference between optimistic and pessimistic locking?

Optimistic locking does not lock data during the transaction; it checks for conflicts at commit time using a version number. Pessimistic locking locks data at the start and holds it until commit, preventing others from accessing it.

Which locking strategy is better for high-traffic web apps?

Optimistic locking is usually better for read-heavy, low-conflict workloads because it allows more concurrency. Pessimistic locking can cause bottlenecks if many users try to edit the same record simultaneously.

Can I use both optimistic and pessimistic locking in the same system?

Yes, you can mix them. For example, use pessimistic locking for critical financial transactions and optimistic for user profile edits. Many ORMs allow per-operation locking strategies.

How do I implement optimistic locking in SQL?

Add a version column to your table. When updating, include WHERE version = ?. If the update affects 0 rows, the version has changed, and you need to retry.

What causes a deadlock and how can I avoid it?

A deadlock occurs when two transactions hold locks and wait for each other’s locks. Avoid by acquiring locks in a consistent order, keeping transactions short, and using timeouts.

Does optimistic locking work with NoSQL databases?

Yes, many NoSQL databases support optimistic concurrency via version fields or compare-and-swap operations (e.g., MongoDB’s findAndModify with a version check).

Related

• Llama.cpp vs Ollama: Workflow, API, and Control
• Second-Level Domain: What It Means Before the TLD
• Google Play Music to YouTube Music Transfer Tool

in t | Transactions per second | tps | 100 | 0 | 100000
in c | Conflict rate (%) | % | 5 | 0 | 100
in r | Avg transaction duration (ms) | ms | 50 | 0 | 60000
out retry | Retries per second | retries/s | t * (c/100) | 2
out overhead | Pessimistic lock wait (s per txn) | s | (r/1000) * (c/100) | 3