Optimistic vs. Pessimistic Locking

📋 Overview

Concurrency control is essential for maintaining data integrity when multiple users attempt to update the same resource simultaneously. Pessimistic Locking takes a "Better Safe Than Sorry" approach, locking the data at the start. Optimistic Locking takes a "Forgiveness over Permission" approach, checking for conflicts only at the end. Choosing between them is a trade-off between Data Safety and System Throughput.

---

🏗️ Core Principles & Characteristics

Pessimistic Locking

• Exclusive Access: The first user to read the data acquires a lock (e.g., SELECT ... FOR UPDATE). No one else can even read the data (for updates) until the first user finishes.
• Database Level: Usually implemented via RDBMS locks (Row-level or Table-level).
• Ideal for High Contention: Best when conflicts are frequent and the cost of "Retrying" is too high.

Optimistic Locking

• Versioning: Every row has a version (integer) or timestamp.
• Check-at-Commit: The update only succeeds if the version in the database still matches the version the user read at the beginning. If not, the update fails.
• Ideal for Low Contention: Best for "Read-Heavy" systems where users rarely update the same row at the exact same millisecond.

---

⚖️ Trade-offs: Pros & Cons

Pessimistic Locking

• Pros: Guarantees 100% consistency; no need for retry logic; safe for long-lived transactions.
• Cons: High risk of Deadlocks; low concurrency (users are blocked); high database resource usage (keeping locks open).

Optimistic Locking

• Pros: High throughput (no blocking); no risk of deadlocks; lightweight on database resources.
• Cons: Requires Retry Logic in the application; performance degrades sharply under high contention (the "Abort-Retry" loop); "Lost Updates" can occur if retry logic isn't handled correctly.

---

🌍 Real-World Implementation

• Banking (Transfer): Uses Pessimistic Locking to ensure that while a balance is being calculated and updated, no other transaction can touch that account.
• E-commerce (Inventory): Often uses Optimistic Locking. A user adds an item to their cart; the system only checks if the stock is still available at the "Place Order" step.
• CMS / Wiki: Use Optimistic Locking via version numbers. If two people edit a page, the second one is told: "This page has changed since you started editing. Please merge your changes."

---

💡 Interview "Gotchas" & Tips

• The "Human in the Loop" Problem: Never use Pessimistic Locking for a web form where a user might open a page and go to lunch. The lock will stay open, blocking everyone else. Use Optimistic locking for UI interactions.
• Deadlocks: If asked how to prevent deadlocks in Pessimistic locking, mention "Consistent Lock Ordering" (always lock Table A then Table B).
• Version Exhaustion: In extremely high-churn systems, an integer version might overflow (though unlikely at 2 billion).

---

📐 Suggested Architecture Primitives

• FOR UPDATE / LOCK IN SHARE MODE: SQL commands for Pessimistic locking.
• @Version (JPA/Hibernate): The standard annotation for implementing Optimistic locking in Java.
• Conditional Updates: UPDATE table SET val = x, version = version + 1 WHERE id = y AND version = old_version.
• Redis WATCH: A primitive for optimistic concurrency control in Redis.