Node.js › Databases
What is a database deadlock, and how might it occur between two concurrent transactions in a Node.js application?
AdvancedAnswer
A deadlock occurs when two (or more) transactions each hold a lock the other needs, and each is waiting for the other to release its lock first, resulting in neither ever proceeding -- commonly arising when two transactions update the same set of rows in a different order (transaction A locks row 1 then waits for row 2, while transaction B locks row 2 then waits for row 1). Most databases detect this situation automatically and forcibly abort one of the transactions with a deadlock error, which the application must catch and retry.
// Deadlock risk: two transfers happening concurrently in opposite directions
// Transaction A: locks account 1, then tries to lock account 2
// Transaction B: locks account 2, then tries to lock account 1
// Mitigation: always acquire locks in a consistent order (e.g., by ID)
const [firstId, secondId] = [accountA, accountB].sort();
await client.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [firstId]);
await client.query('SELECT * FROM accounts WHERE id = $1 FOR UPDATE', [secondId]);Real-world example A money-transfer feature experiencing occasional deadlock errors under concurrent load is fixed by always acquiring row locks on the two involved accounts in a consistent order (sorted by account ID) regardless of which account is the sender or receiver, eliminating the circular wait condition entirely.