A deadlock occurs when two user processes each have a lock on a separate page or table and each wants to acquire a lock on the same page or table held by the other process. The transaction with the least accumulated CPU time is killed and all of its work is rolled back.
There are ways to avoid deadlocks. These are general good practices:
- Write transactions to acquire locks in the same order on the system. This will help to reduce both lock contention and deadlocks.
- Write only those statements which must be committed or rolled back as a group within a transaction. This will help reduce lock contention and deadlocks.
- Avoid hot spots. Hot spots are those points in the data or index where many queries access. If too many queries are accessing the same pages for Selects and DML operations, then lock contention and/or frequent deadlocks could result. For example, use a clustered index on the table to avoid heap.
- Avoid heap tables (i.e. tables with no clustered index). They may cause many issues, including deadlocks, because of the hot spot. That is a "hot spot" on the last page of the table leading to lock contention and frequent deadlocks.
- Applications that are not impacted by dirty reads (isolation level 0), may have better concurrency and reduced deadlocks when accessing the same data by setting transaction isolation level 0 at the beginning of each session. An example is an application that finds the momentary average balance for all savings accounts stored in a table. Since it requires only a snapshot of the current average balance, which probably changes frequently in an active table, the application should query the table using isolation level 0.
- Those tables commonly involved in deadlocks are candidates for row-level locking (datarows or datapages).
- Use stored procedures, as opposed to dynamic SQL, where feasible. There are many advantages to using stored procedures including: minimize blocks and deadlocks. it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Furthermore, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally, rather than dispersed amongst a number of applications.
- On OLTP systems, avoid creating a clustered index on a sequential key on a transaction busy table. This would create a hot spot on the last page of the table. In addition, despite row-level locking, this can still cause frequent deadlocks and blocks.
- Avoid select alter #table, create #tables with constraints and defaults.
This was first published in May 2006