In this lesson, you will learn how to proactively prevent update conflicts by using explicit row-level locks to manage high-contention resources.
The Philosophy of Pessimism
In many high-traffic systems, multiple transactions frequently attempt to modify the same row simultaneously. While MVCC handles conflicts by failing or retrying transactions (Optimistic Locking), Pessimistic Locking assumes a conflict will happen. Instead of waiting to see if a collision occurs, we explicitly lock the specific rows we intend to modify at the start of our read operation.
This forces other transactions to wait their turn, ensuring that once you select a row, no one else can change it until you finish your work.
Implementing Explicit Locks
In SQL, we implement this using the SELECT ... FOR UPDATE syntax. When a transaction executes this statement, the database engine places a lock on the retrieved rows, preventing other transactions from acquiring a lock or modifying those specific rows until the current transaction commits or rolls back.
Consider an inventory system where two users might try to "buy" the last item in stock at the exact same moment:
BEGIN;
-- Lock the specific item row immediately
SELECT stock_count
FROM inventory
WHERE item_id = 501
FOR UPDATE;
-- Perform business logic (calculate new count)
-- UPDATE inventory SET stock_count = stock_count - 1 WHERE item_id = 501;
COMMIT;
When to Use This Pattern
You should use pessimistic locking when:
- Contention is high: You expect many users to compete for the exact same resource.
- Operations are "Read-Modify-Write": You need the current value to calculate the next value (like decrementing stock or incrementing a sequence).
- Efficiency is critical: You want to avoid the performance cost of repeatedly failing and retrying transactions that keep colliding under heavy load.
By locking the row early, you serialize access to the resource. This does create a queue, but it provides a predictable, stable outcome compared to the constant rollbacks seen in highly contested optimistic systems.
Key Takeaway: Use SELECT ... FOR UPDATE to explicitly lock rows before modifying them, effectively preventing concurrent update conflicts in high-contention scenarios by forcing competing transactions to queue for access.