Database.Middle.What is optimistic vs. pessimistic locking?

Pessimistic and optimistic locks are two strategies for handling concurrent access to shared resources (usually data in a database or memory). They’re mainly used to ensure data consistency in multi-threaded or multi-user environments.

---

🔒 Pessimistic Locking

• Idea: Assume conflict is likely, so prevent others from accessing the data until the current operation is finished.
• It locks the data as soon as it’s read, and holds the lock until the transaction completes.
• Other users/threads must wait or fail when trying to access the locked data.

🧠 Example:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

• The FOR UPDATE clause locks the selected row. Other transactions can’t read/write it until the current one finishes.

✅ Pros:

• Prevents conflicts completely.
• Safer in high-contention environments.

❌ Cons:

• Causes blocking (others have to wait).
• Can lead to deadlocks.
• Higher overhead, especially with many transactions.

☀️ Optimistic Locking

• Idea: Assume conflict is rare, allow multiple operations to proceed concurrently.
• Doesn’t lock data immediately. Instead, checks for conflict only at the time of update.
• Typically uses a version number or timestamp to detect if the data was modified by someone else.

🧠 Example:

Suppose you have a version column in a row:

1. Read the row: version = 5
2. Make some changes.
3. Try to update:

UPDATE accounts
SET balance = 500, version = 6
WHERE id = 1 AND version = 5;

• If version is no longer 5, the update fails — someone else changed it.

✅ Pros:

• Non-blocking — better performance for low-conflict environments.
• Scales well with high read, low write scenarios.

❌ Cons:

• If many conflicts happen, retries are expensive.
• More complex logic (e.g., compare-and-set).

Summary Table:

Feature	Pessimistic Locking	Optimistic Locking
Assumption	Conflicts are likely	Conflicts are rare
Lock Time	Lock at read	Lock/check at write
Performance	Lower under high contention	Better with few conflicts
Risk of Deadlock	Yes	No
Typical Use	Banking, inventory	User forms, most web apps