Intermediate LevelSQL & MySQL
How does Pessimistic Locking work using SELECT ... FOR UPDATE?
❓ Interview Prompt: Explain Pessimistic Locking, `SELECT ... FOR UPDATE` (exclusive locks), `SELECT ... FOR SHARE` (shared locks), `NOWAIT`, and `SKIP LOCKED`.
Pessimistic Locking acquires exclusive database locks immediately when reading data, blocking concurrent transactions from modifying selected records:
Locking Variants:
•
'SELECT ... FOR UPDATE': Acquires exclusive locks; blocks concurrent reads ('FOR UPDATE') and writes.
•
'NOWAIT': Throws an error immediately if target rows are already locked.
•
'SKIP LOCKED': Skips locked rows and returns available rows (ideal for high-throughput job queue processing!).
Code Implementation Example
sql
-- High-throughput Job Queue worker processing un-locked jobs
START TRANSACTION;
SELECT id, payload
FROM job_queue
WHERE status = 'PENDING'
LIMIT 1
FOR UPDATE SKIP LOCKED; -- Skips jobs currently being processed by other workers!💡Fetching job queue items safely with SELECT FOR UPDATE SKIP LOCKED.
🎯 Interview Key Takeaway
Use SELECT FOR UPDATE SKIP LOCKED to build high-throughput, lock-free distributed worker queues.
#SQL#Pessimistic Locking#FOR UPDATE#SKIP LOCKED#Intermediate
Back to all SQL & MySQL Questions→