Best Practices for SQL Server Deadlocks

Slow queries can negatively impact your users and systems when retrieving data from the database. Deadlocks in SQL Server can cause inefficiency, frustration and poor performance. 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. In order to troubleshoot them, it is important to understand what a deadlock in SQL Server and how to prevent them.

Database optimization is a nuanced task. For additional resources on increasing the speed and efficiency of queries, check out our blog on query optimization.

Understanding SQL Server Deadlocks

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 an accurate representation of what happens during a SQL Server Deadlock.

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

Prioritization in SQL Server

To tie the concept of prioritization into the library example, let’s expand the scenario. The student who is waiting for your book has an assignment that is due tomorrow. Your assignement is due next week. The librarian (or SQL Server) will take your book, and give it to the other student, allowing them to finish because of the higher priority of their need.

While the library scenario is a helpful framework to understand how deadlocks occur, there are nuances that the example does not capture. Deadlocks in SQL Server have many nuances. For example, 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. Just because a query has been killed does not mean it can never be performed. Once the resources are available, you should be able to run the query again with success.

Avoiding SQL Server Deadlocks

In order to avoid SQL Server deadlocks, begin by becoming familiar with the following best practices:

1. 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 LOW or SET DEADLOCK_PRIORITY HIGH
   2. Use NOLOCK HintUsing NOLOCK Hint allows SQL Server to ignore locks set in place by previous queries. This technique allows it to  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. For more information on benefits and drawbacks, read our article on NOLOCK.
   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.  SQL Server is designed to allow many activities to take place at the same time, so this solution is not practical for every scenario.