What is database locking and how do Shared vs Exclusive locks prevent race conditions?
Locking is a concurrency control mechanism that prevents concurrent transactions from corrupting shared data.
• Shared Lock (S): Acquired during read operations. Multiple transactions can hold shared locks on the same resource concurrently to read data, but no write is allowed. • Exclusive Lock (X): Acquired during write operations (`UPDATE`, `DELETE`, `INSERT`). Only one transaction can hold an exclusive lock; all other reads and writes are blocked until it releases.
Deadlocks occur when two transactions hold locks and each waits for the other to release, which the engine resolves by aborting one transaction.
-- Pessimistic Locking: Prevent race conditions when reserving limited stock
BEGIN;
SELECT inventory_count
FROM Products
WHERE product_id = 999
FOR UPDATE; -- Acquires an Exclusive lock on this specific row
-- Safe from concurrent checkouts
UPDATE Products
SET inventory_count = inventory_count - 1
WHERE product_id = 999;
COMMIT;`SELECT ... FOR UPDATE` acquires an exclusive lock on product 999 immediately. Any concurrent checkout attempt will pause until this transaction commits, eliminating inventory over-selling.
Ready for Database Design Basics?
Take a quick test to evaluate yourself, or move straight to the next module.