Best Practices for SQL Server Deadlocks

What Are SQL Server Deadlocks, And How Can You Avoid Them?

Slow queries can negatively impact your users and systems when retrieving data from the database. SQL Server deadlocks happen when two queries are trying to retrieve the same pieces of information, and each query already has locked a piece of information the other needs. Query optimization can help the with speed and efficiency of queries, but it’s important to understand why deadlocks happen and how to prevent them.

Let’s say you’re doing a comparative study on two different authors writing about the same event in history.

You go to the library and decide on two different history authors. You find the book by the first author, but when you find where the second author’s book is supposed to be, it’s gone. Now you have to wait until that book is back on the shelf to complete your assignment. However, another person who needs the book you have to finish their assignment has already taken the second book you need off the shelf and is waiting for you to return yours.

This scenario is essentially what a SQL Server Deadlock is.

SQL Server detects when two queries each have a piece of information the other  wants, and kills the query with the lower priority, or if you haven’t assigned one, lower use of resources. SQL Server assigns a cost based on the query’s execution plan, and if one query has a higher cost, the server kills the other.

To relate this concept of prioritization to the library example, let’s say the student who is waiting for your book has a study that is due tomorrow, while yours is due next week. The librarian – or SQL Server – will take your book away and allow the other student to finish his study. However, just as you can go to the library again and check out both books when they’re available, the query that was killed can be run again after the first query is complete.

SQL Server deadlocks can be avoided or reduced, however, by following some best practices:

1. Set deadlock priority
   When a deadlock happens, SQL Server kills the query with the lowest cost. To ensure that an important query isn’t rolled back, you can update the deadlock priority. This will not prevent deadlocks, but will ensure that the queries you decide is most important will not be killed. For example, use the following code to set the deadlock priority for a query:
   
   SET DEADLOCK_PRIORITY LOWor:

   SET DEADLOCK_PRIORITY HIGH

2. Use NOLOCK Hint
   Using NOLOCK Hint allows SQL Server to ignore locks set in place by previous queries, therefore letting it read the data necessary for a query, even if it has been locked by another. One drawback is the possibility of reading “dirty data” if data is updated while the query is running.
3.  Restrict user input during transaction processing
   You can reduce stress (and related query run times) on the database and the number of deadlocks by reducing the amount of simultaneous activity in the database. This allows SQL Server to use more of the available resources for query activity, but is not always either desirable or practical, as SQL Server is designed to allow many activities to take place at the same time and we typically want to accomplish as much as possible in the shortest amount of time. SQL Server performance tuning is not a trivial pursuit and DBAs can spend years developing their expertise in this area.