Optimistic Locking and Pessimistic Locking in Databases

1. Overview

To prevent concurrent update conflicts in a database, two main locking strategies are used: Optimistic Locking and Pessimistic Locking. Understanding their differences helps in selecting the right approach for a given scenario.

---

2. Optimistic Locking

Definition

• Detects conflicts at update time and fails the update if a conflict is detected.
• Suitable when concurrent updates are rare.

Mechanism

1. Read the data (SELECT).
2. Check version before update using a version or timestamp column.
3. Update succeeds if versions match, otherwise it fails due to conflict.

SQL Example

-- 1. Read data (Example: Fetching data with ID=1)
SELECT id, name, version FROM users WHERE id = 1;

-- 2. Update only if the version matches
UPDATE users
SET name = 'New Name', version = version + 1
WHERE id = 1 AND version = 3; -- Version must match

Success: If no other updates occur, the update is successful.
Failure: If another update has changed the version, the update fails.

Advantages

No locking required → better performance
No risk of deadlocks
Best for read-heavy, low-contention environments

Disadvantages

Failures require retries, adding complexity
High contention increases retries and reduces performance

---

3. Pessimistic Locking

Definition

• Locks the data to prevent concurrent updates.
• Suitable when concurrent updates are frequent.

Mechanism

1. Lock data when reading (SELECT ... FOR UPDATE).
2. Other transactions must wait until the lock is released.
3. Lock is released after the transaction ends.

SQL Example

-- 1. Read data with lock
SELECT id, name FROM users WHERE id = 1 FOR UPDATE;

-- 2. Update data (Locked until transaction completes)
UPDATE users SET name = 'New Name' WHERE id = 1;

Advantages

Prevents conflicts completely
No need for retry logic

Disadvantages

Locks cause delays for other transactions
Risk of deadlocks if multiple transactions lock the same resources
Lower throughput in high-write environments

---

4. Optimistic vs. Pessimistic Locking

Feature	Optimistic Locking	Pessimistic Locking
Use case	Low contention	High contention
Conflict handling	Update fails, requires retry	Locks prevent conflicts
Performance impact	High (no locks)	Lower (due to locks)
Deadlock risk	None	Possible

Choosing the Right Strategy

• Low contention (mostly reads): Optimistic Locking
• High contention (frequent updates): Pessimistic Locking
• Critical operations where failure is not an option: Pessimistic Locking

---

5. Real-World Applications

Optimistic Locking Use Cases

• User profile updates (infrequent changes)
• Report generation or analytics
• Web form submissions

Pessimistic Locking Use Cases

• Inventory Management: Prevents multiple users from updating the same stock.
• Bank Transactions: Ensures consistency in fund transfers.
• Reservation Systems: Prevents double-booking.

---

6. Summary

Feature	Optimistic Locking	Pessimistic Locking
Conflict Handling	Detects conflicts at update time	Prevents conflicts by locking
Retry Required?	Yes, if update fails	No
Performance Impact	High (no locks)	Lower (locks may cause delays)
Deadlock Risk	None	Possible
Best For	Low-contention systems	High-contention systems (e.g., financial, inventory, reservations)

Key Takeaway:

• Optimistic Locking is preferred for web applications, APIs, and read-heavy systems.
• Pessimistic Locking is used in finance, reservations, and inventory management where data consistency is critical.

Choosing the right strategy ensures both performance and data integrity!