Databases & System Design•Part 3: NoSQL & Document Databases (MongoDB, DynamoDB) (26 - 40)
Optimistic vs Pessimistic Locking:
In Simple Words:
- Optimistic Locking: Records carry a
versionfield. Updates checkWHERE version = 5; if changed, update fails and retries. - Pessimistic Locking: Locks records explicitly (
SELECT ... FOR UPDATE), preventing anyone else from reading/writing until committed.
sql
-- Optimistic Locking:
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 42 AND version = 5;
-- If 0 rows updated, another user modified the stock; retry!
-- Pessimistic Locking:
BEGIN;
SELECT * FROM products WHERE id = 42 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;