Part 130: Concurrency Control: SELECT FOR UPDATE vs Versioning

Preventing ticket double-booking or inventory overdrafts.

1. Pessimistic Locking with SELECT ... FOR UPDATE

Acquires an exclusive lock on the selected row at read time, forcing concurrent transactions to wait:

BEGIN;
-- Lock the specific concert seat:
SELECT seat_number, status 
FROM concert_seats 
WHERE seat_id = 450 
FOR UPDATE;

-- Update reservation safely:
UPDATE concert_seats SET status = 'reserved', user_id = 99 WHERE seat_id = 450;
COMMIT;

2. Optimistic Locking with Version Numbers

Instead of database locks, check a version column at update time:

UPDATE tickets 
SET status = 'sold', version = version + 1 
WHERE id = 450 AND version = 2;

If 0 rows are affected, another user modified the record first—abort and notify the user.

Key Takeaways

• Use SELECT ... FOR UPDATE for high-contention, low-latency locks (e.g. inventory deductions).
• Use optimistic versioning for human-paced edits (e.g. editing a long document in a web browser).

In Part 131, we enter Module 14 to master Indexing, Query Optimization & Performance: B-Trees Explained!