As part of Quest’s Database Training Days Fall Series, Brent Ozar, Microsoft Certified Master presented a tutorial on “Avoiding Deadlocks with Query Tuning.” The program focused on the three concurrency issues that occur in SQL Server, three ways to fix them and one way that appears to fix them, but really doesn’t.
Concurrency issues: Locking, blocking and deadlocks in SQL Server
What are concurrency issues? They happen when queries try to avoid conflict with each other over database objects like tables. They are:
- Locking – Queries do this all the time to prevent other queries from using a table at the same time. This is a normal database operation.
- Blocking – This occurs when one query has a normal lock, but another query tries to acquire the same lock. The second query has to wait as long as needed for the first query to release the lock. Depending on the nature of the first query, the second one could be waiting a very short time or a very long time. It’s those long waits that really affect performance.
- Deadlocking – Deadlocks occur when one query takes a lock, another query takes a different lock, and then each wants to acquire the other’s lock. SQL Server resolves this by designating one of the queries as the victim and killing it to break the standoff. Even though one of the queries is able to proceed, this also has an impact on performance.
Fixing concurrency issues
Regardless of whether you’re experiencing blocks or deadlocks in SQL Server, there are ways to fix concurrency issues. Brent presented these three methods, and spent most of the remainder of the session focusing on the second one – fixing bad code.
- Have enough indexes to make your queries fast, but not so many that they slow things down by making queries hold more locks for longer amounts of time
- Tune your transactional code so that queries work through tables in the same predictable order each time
- Use the right isolation level for your application’s needs
As he jumped into the hands-on part of the program, Brent commented on using NOLOCK statements for blocking and deadlocking. He cautioned that NOLOCK doesn’t really fix these problems because it relies on “dirty reads” – essentially, it ignores other queries’ row locks.
In his demonstration of this using the Stack Overflow database, he created a simple query that looked for and counted people named “Alex.” Then, he created another query that would run an update on people who are not named Alex—no insertions or deletions of records. One query should have nothing to do with the other. But, running them together leads to different results in the number of people named Alex. This is because NOLOCK lets you see data that wasn’t committed, leading to random results that you can’t predict. It only happens under concurrency.
Clearly, a better fix is needed for blocking and deadlocking in SQL Server that doesn’t lead to random and unpredictable results.
A better solution for SQL deadlocks
Brent then demonstrated how to fix a deadlock by changing the code that causes it. His first demo showed a simple situation involving two tables so the audience could see a deadlock in slow motion while it happened. Since SQL Server looks for deadlocks every 5 seconds and kills the query that’s the easiest to roll back, we were able to see the deadlock victim emerge.
In a simple situation, the most general advice applies, and that is to touch the tables in the same order each time when constructing queries. This will generally keep queries from deadlocking each other.
What about more complex queries? For this scenario, Brent used a more realistic situation that could easily arise on Stack Overflow where two people are upvoting each other’s questions. Because the same users are involved in both transactions, this causes a deadlock.
Here, it’s not enough to work through each table in the same order each time, but it’s also necessary to minimize the number of times each table is touched. As Brent explained it, the fix can involve some ugly code that causes the queries to block, but at least not deadlock. In this case, a short duration block that allows both queries to run to completion is better than a deadlock that terminates one of them.
No one wants to change code in hundreds of queries, so focus on the ones that are constantly deadlocking, remove any unnecessary lines from the transaction, and don’t be afraid to introduce a block to avoid a deadlock.
