Optimistic vs Pessimistic Locking

Pessimistic Locking assumes that data conflicts will happen frequently. It prevents concurrency conflicts by acquiring an exclusive lock on the target record in the database before modifying it, blocking all other transactions until the lock is released:

sql
Copy
-- Acquires exclusive write lock on row 10
BEGIN;
SELECT * FROM inventory WHERE item_id = 10 FOR UPDATE;

-- Perform business checks in application code...
UPDATE inventory SET stock = stock - 1 WHERE item_id = 10;
COMMIT; -- Lock is released upon commit

• Pros: 100% collision prevention; guaranteed transactional safety for high-contention rows.
• Cons: Holding database locks reduces concurrency, can cause lock wait timeouts, and risks deadlocks if locks are acquired in inconsistent order.