🗄️ Fix a Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order
A deadlock forms when one procedure locks a table and then needs a lock on a second table, while at the same moment a different procedure running concurrently has already locked that second table and now needs a lock on the first one – each is waiting on a resource the other is holding, and neither can proceed until the deadlock monitor picks one of them as a victim and rolls it back. Under light traffic, the timing rarely lines up closely enough for this to actually happen, which is exactly why it can pass most testing and only start appearing once concurrent load is heavy enough to create that overlap regularly.
🔎 The Problem
-- Procedure A: BEGIN TRAN; UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1; UPDATE Ledger SET LastTouched = GETDATE() WHERE AccountId = 1; COMMIT; -- Procedure B, running concurrently: BEGIN TRAN; UPDATE Ledger SET LastTouched = GETDATE() WHERE AccountId = 1; UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 1; COMMIT; -- Under load: A holds Accounts and waits on Ledger, while -- B holds Ledger and waits on Accounts - deadlock, one -- transaction is chosen as victim and rolled back.
✅ Fix: Make Both Procedures Lock the Shared Tables in the Same Order
- Capture the actual deadlock graph, through an extended events session on the deadlock report event or the error log if the right trace flag is enabled, to see precisely which resources and procedures were involved rather than guessing from the error alone.
- Reorder the statements in one of the two procedures so both acquire locks on the shared tables in the same consistent sequence, which removes the possibility of the two ever forming a cycle in the first place.
- Consider whether additional indexing on the filtered columns can reduce lock duration or scope as a complementary improvement, understanding this lowers the odds of overlap without fixing the underlying ordering issue by itself.
🚨 Why This Is Dangerous
- Catching the resulting deadlock error and simply retrying the transaction is a reasonable short-term mitigation for availability, but treating retry-on-deadlock as the actual fix leaves the real lock-ordering conflict in place, where it keeps consuming a small, growing cost in retried transactions as load increases.
A deadlock isn’t bad luck on a bad day – it’s two procedures that were always going to collide, just waiting for enough concurrent traffic to actually prove it.
