1
SQLAdvanced#transactions#concurrency
SQLAdvanced#transactions
What is a deadlock and how do you avoid it?
Two transactions each hold a lock the other needs, so neither can proceed; the engine kills one as the victim. Avoid it by locking resources in a consistent order, keeping transactions short and using proper indexes to lock fewer rows.
Example
-- Txn A: UPDATE accounts SET .. WHERE id = 1; then id = 2; -- Txn B: UPDATE accounts SET .. WHERE id = 2; then id = 1; -> deadlock -- Fix: both update ids in ascending order.