DEV Community

tejaswipandava
tejaswipandava

Posted on

Optimistic Locking vs. Pessimistic Locking — Handling Concurrent Updates in SQL Databases

#sql #database #architecture

What happens when two users try to update the same database row at exactly the same time? Without proper concurrency control, one update can silently overwrite the other, leading to inconsistent or incorrect data (the classic lost update problem). SQL databases solve this with two main concurrency control strategies: Optimistic Locking and Pessimistic Locking.

✅ Optimistic Locking (Version-Based)

Instead of locking the row up front, each transaction reads the current version number along with the data. When updating, the database verifies the version hasn't changed since it was read:

UPDATE accounts
SET balance = ?, version = version + 1
WHERE id = ? AND version = 5;
Enter fullscreen mode Exit fullscreen mode

If another transaction already modified the row (and bumped the version), this UPDATE affects zero rows — the version check fails immediately, and the application detects the conflict via the affected-row count and can retry.

Key characteristics:

  • ✔ No row-level lock held while "thinking" — the row stays fully available to others.
  • ✔ Better throughput for read-heavy systems, since nothing blocks concurrent readers/writers.
  • ✔ Conflicts are detected at UPDATE time, not up front.
  • ✔ The application is responsible for retrying on failure (re-read the row, reapply the change, try again).

Mental model: "Try first. If someone already changed the data, fail fast and retry."

🔒 Pessimistic Locking (Row-Level Lock)

Before modifying data, the transaction explicitly locks the row so no one else can touch it:

SELECT * FROM accounts
WHERE id = 101
FOR UPDATE;
Enter fullscreen mode Exit fullscreen mode

While one transaction holds the lock:

  • Other transactions requesting the same row wait in a queue rather than failing.
  • They execute only after the lock is released (commit/rollback).
  • No version conflict ever occurs, because updates to that row are forced to happen sequentially.

Key characteristics:

  • ✔ Row locked immediately — guarantees exclusive access for the duration of the transaction.
  • ✔ Other requests wait instead of failing outright.
  • ✔ Strong consistency, even under heavy contention on the same rows.
  • ✔ Lower concurrency (more waiting/blocking) but highly predictable execution order.

Mental model: "Wait your turn before making changes."

📌 When Should You Use Each?

Choose Optimistic Locking when:

  • Conflicts are rare.
  • The application is read-heavy.
  • High throughput matters more than avoiding occasional retries.
  • Retries are acceptable at the application layer.

Examples: e-commerce product catalogs, user profiles, content management systems.

Choose Pessimistic Locking when:

  • Data integrity is critical and lost updates are unacceptable.
  • Many users frequently update the same records (high contention).
  • Predictable, sequential execution is more valuable than raw throughput.

Examples: banking transactions, digital wallets, inventory management, ticket booking systems.

💡 Key Takeaway

There isn't a universally "better" approach. The right choice depends on your application's contention level, consistency requirements, throughput goals, and user experience. Understanding this trade-off is essential for designing scalable, reliable, high-performance backend systems — it's a decision made per use case (or even per table/operation), not a single global choice for the whole system.

In practice, each strategy carries a distinct failure mode worth watching for in production:
retry storms under optimistic locking when contention is higher than expected, and lock contention or deadlocks under pessimistic locking when transactions hold locks longer than intended.

Top comments (0)