Pessimistic Locking

TL;DR

Explanation

SELECT ... FOR UPDATE locks the selected rows until the transaction commits or rolls back. No other transaction can modify those rows. High-contention scenario: inventory reservation, seat booking, financial transfers. MySQL: SELECT ... FOR UPDATE, SELECT ... FOR SHARE (read lock). PostgreSQL: similar plus SKIP LOCKED (skip already-locked rows — useful for queue workers). Trade-offs: prevents races completely but serialises updates, reducing throughput. Long transactions increase deadlock risk. FOR SKIP LOCKED is excellent for queue workers — each worker takes uncontested rows.

Common Misconception

Why It Matters

Common Mistakes

• Using pessimistic locking for low-contention scenarios — unnecessary performance cost.
• Holding pessimistic locks across slow I/O (HTTP calls, file writes) — deadlock risk.
• Not using SKIP LOCKED for queue workers — causes lock contention between workers.

Code Examples

-- Long-running pessimistic lock across slow operation:
START TRANSACTION;
SELECT * FROM payments FOR UPDATE;
-- Calls external payment API (2 seconds) while holding lock
UPDATE payments SET status = 'processed';

-- Queue worker with SKIP LOCKED:
START TRANSACTION;
SELECT * FROM jobs
WHERE status = 'pending'
LIMIT 1
FOR UPDATE SKIP LOCKED; -- Each worker gets a different row

-- Seat booking:
START TRANSACTION;
SELECT * FROM seats WHERE id = ? FOR UPDATE;
-- Check if available, then book — all under lock

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Tags

Related Terms