Locks, Row-Level Locking, and Deadlocks
Locks coordinate conflicting operations. SELECT ... FOR UPDATE locks selected rows so competing transactions cannot modify them in conflicting ways until the transaction ends. This is useful for workflows that read a row and then make a decision based on it. Use row locks carefully and keep the transaction short.
A deadlock occurs when transactions wait on each other's locks in a cycle. PostgreSQL detects deadlocks and aborts one transaction. Applications should handle the error and may retry appropriate transactions. Consistent lock ordering can reduce deadlock risk, but retry logic is still valuable.
Lock a row before updating
BEGIN;
SELECT quantity
FROM inventory
WHERE product_id = 42
FOR UPDATE;
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;
COMMIT;💡 The row lock coordinates concurrent access to the inventory row. Production code should check whether the update affected a row and should ensure the transaction handles insufficient stock correctly.
Key Takeaways
- FOR UPDATE locks selected rows for conflicting operations.
- Deadlocks can cause PostgreSQL to abort one transaction.
- Use consistent lock ordering and bounded retry logic where appropriate.
Knowledge Check
What can PostgreSQL do when it detects a deadlock?