All 7 Deadlocks questions
More in Deadlocks
What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond? How do you capture detailed information about deadlocks that are happening on a server so you can investigate their root cause? What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application? How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock?
Advertisement
SQL Server › Deadlocks

What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks?

Advanced

Answer

You can ensure transactions always access tables and rows in the same consistent order, keep transactions as short as possible to minimize the time locks are held, use an appropriate isolation level such as read committed snapshot to reduce locking altogether, and add appropriate indexes so operations lock only the specific rows they need rather than larger ranges.
-- Always update tables in the same consistent order
-- across every part of the application
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;
Real-world example A development team fixes a recurring deadlock by ensuring every part of their application always updates the accounts table in ascending order by account id, eliminating the conflicting access pattern that had been causing the cycle.

Common follow-ups: How does read committed snapshot isolation specifically help reduce deadlocks?;What role do missing indexes play in causing unnecessary locking that leads to deadlocks?

Isolation & Locking;Indexes
All 7 Deadlocks questions