Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Deadlocks in SQL transactions:
In Simple Words: Occurs when Transaction A holds Lock 1 and waits for Lock 2, while Transaction B holds Lock 2 and waits for Lock 1.
- Detection: The database detects the cycle and terminates one transaction.
- Prevention: Always acquire locks in the exact same alphabetical or numerical order across all code!
sql
-- Transaction A (locks user 1, then user 2):
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
COMMIT;
-- Prevention: Sort IDs before locking in application code:
-- const ids = [idA, idB].sort();