Optimistic vs Pessimistic Locking

Two strategies to handle concurrent access to the same data.

• Pessimistic → "Someone WILL conflict with me, let me lock first"
• Optimistic → "Conflicts are RARE, let me just check at the end"

Pessimistic Locking

Acquire a lock before reading/writing. Other transactions must wait until lock is released. Lock is held for the entire duration of transaction.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- locks row immediately
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT; -- lock released

Types of Pessimistic Locks

• Shared Lock (Read Lock): Multiple transactions can READ simultaneously. No one can WRITE while shared lock exists.
• Exclusive Lock (Write Lock): Only ONE transaction can hold it. No one can READ or WRITE.

Optimistic Locking

No locks acquired upfront. Each row has a version number. At commit time → check if version has changed.

-- Step 1: Read with version
SELECT balance, version FROM accounts WHERE id = 1;
-- returns balance=500, version=5

-- Step 2: Update only if version unchanged
UPDATE accounts SET balance = 400, version = version + 1
WHERE id = 1 AND version = 5;
-- if 0 rows updated → conflict detected! retry

Comparison

	Pessimistic	Optimistic
Lock acquired	Upfront	Never (just version check)
Conflict handling	Prevent upfront	Detect at commit
Waiting	Yes, transactions wait	No waiting
Retry needed	No	Yes, on conflict
Throughput	Lower	Higher
Best for	High conflict scenarios	Low conflict scenarios

When to Use Which

• Pessimistic: High contention, can't afford retries, long transactions. Example: Bank transfer, seat booking.
• Optimistic: Low contention, short transactions, high read/low write. Example: Social media likes, profile updates.

System	Approach	Why
Banking	Pessimistic	Can't lose updates
Airline booking	Pessimistic	Last seat conflicts
GitHub (git)	Optimistic	Merge conflicts rare
E-commerce cart	Optimistic	Low contention
Hibernate ORM	Optimistic (default)	General purpose