Pessimistic Locking
In one line
Pessimistic locking holds locks on rows — blocks other transactions from modifying them until commit/rollback.
In simple words
Pessimistic locking assumes conflicts will happen. It acquires locks on rows before reading or modifying them, preventing other transactions from accessing those rows until the lock is released (commit or rollback).
Types:
- Shared lock (read lock) — multiple transactions can read, none can write
- Exclusive lock (write lock) — only one transaction can access (read or write)
SQL: SELECT ... FOR UPDATE (exclusive), SELECT ... FOR SHARE (shared)
Benefits:
- Strong consistency — no conflicts possible while locked
- Predictable — once you have the lock, you're guaranteed exclusive access
Drawbacks:
- Reduced concurrency — other transactions wait
- Deadlock risk — multiple locks can deadlock
- Long waits — if a transaction holds a lock for a long time
Best when conflicts are common or data consistency is critical (banking, inventory).
Example
-- Pessimistic locking with FOR UPDATE
BEGIN;
-- Lock the row for this transaction
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions trying to FOR UPDATE on this row will WAIT
-- Safely update (no one else can modify this row)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Lock released, other transactions can proceed
-- Shared lock (multiple readers, no writers)
SELECT * FROM products WHERE category = 'electronics' FOR SHARE;
-- Other transactions can also FOR SHARE, but not FOR UPDATEFOR UPDATE locks the row exclusively. Other transactions wait. This prevents conflicts but reduces concurrency. Use when conflicts are likely.
Common confusions
Confused with: optimistic locking
The difference: Pessimistic locking holds locks during the transaction, blocking others. Optimistic locking doesn't lock — it checks versions at update time. Pessimistic is better when conflicts are common; optimistic is better when conflicts are rare.
Related terms
Related Resources
Related Lessons
- Concurrency Control
Pessimistic locking.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.