【 SQLpassion Performance Tuning Training Plan】- Week 20: LockEscalations

Hello and welcome back to the SQLpassion Performance Tuning Training Plan. Today we will complete the 5th month by talking about Deadlocking in SQL Server. A deadlock occurs when 2 queries are waiting for each other, and no query can continue its work anymore. In the first step I want to give you an overview how SQL Server handles deadlocks. And finally I will show you some specific deadlock types in SQL Server, and how you can avoid and resolve them.

Deadlock Handling

 

The nice thing about deadlocks is that SQL Server automatically detects(v.发现，发觉，查明/检测) and resolves them. To resolve a deadlock, SQL Server has to rollback the cheapest of the 2 transactions. In the context of SQL Server, the cheapest transaction is the transaction that has written the fewer bytes to the transaction log.

SQL Server implements the deadlock detection in a background process called theDeadlock Monitor. This background process runs every 5 seconds and checks the current locking situation for deadlocks. In the worst case, a deadlock should therefore not last longer than 5 seconds. The query which gets rolled back receives the error number 1205. The "nice" thing about deadlocks is that you can fully recover from that error situation without any user interaction(n.互动;一起活动;合作;互相影响). A smart developer must do the following steps to recover from a deadlock:

• Check for error number 1205, when an exception is thrown
• Pause the application briefly(adv.简单地;短暂地;简略地;略略) to give the other query time to complete its transaction and release its acquired locks
• Resubmit the query, which was rolled back by SQL Server

After the resubmission of the query, the query should continue without any problems, because the other blocking query will have already finished its transaction. Of course you should keep track of reoccuring(发生( occur的现在分词 );被想起;被发现;) deadlocks, so that you do not retry your transaction over and over again.

You can troubleshoot a deadlock in multiple ways. SQL Server Profiler provides you theDeadlock Graph event, which occurs as soon as a deadlock was detected. If you are on SQL Server 2008 and higher, you can also use Extended Events to troubleshoot deadlock situations. Extended Events provides you the system_health event session, which tracks historical(adj.历史的，历史上的;) deadlocks since the last restart of SQL Server. And with the enabled trace flag 1222, SQL Server will log deadlock information into the error log.

Deadlock Types

There are multiple types of deadlocks that can occur in SQL Server. In this section I want to talk a little bit more about the most common ones.

A typical deadlock that I see at almost every SQL Server installation is the famousBookmark Lookup Deadlock, which occurs when you have concurrent read and writeactivity to Clustered- and Non-Clustered Indexes. It's mainly a deadlock that occurs because of a bad indexing strategy. In my day-to-day life as a SQL Server troubleshooter I can say that at least 90% of all deadlocks can be avoided by applying a better indexing strategy to your workload. A Bookmark Lookup Deadlock can be eliminated（v.消除;排除） very easily by providing a Covering Non-Clustered Index as we discussed in week 8 of the SQLpassion Performance Tuning Training Plan.

Another common deadlock is the so-called Cycle Deadlock, where your individual queries have accessed tables in different orders. To avoid that specific deadlock, you always have to make sure that queries access tables in the same order. And the "nicest" deadlock that can occur in SQL Server is the so-called Intra-Parallelism Deadlock, where a parallelism operator (Distribute Streams, Gather Streams, Repartition（n.& v.重新分配） Streams) has deadlocked internally among its individual（adj.个人的;个别的;独特的） threads. The following picture shows a typical deadlock graph.

The graph itself is pure art, and it occurred because you hit a bug in SQL Server. Unfortunatelity such bugs are not going to be fixed by Microsoft, because of the possibility of introduced regressions. Therefore you have to make sure that the queries which caused this deadlock, are running single-threaded in SQL Server. You can achieve single-threaded execution plans through multiple options:

• Work on your indexing strategy, so that the query costs are below the current Cost Threshold( n. 门槛，入口，开始; ) for Parallelism (by default 5)
• Hint SQL Server to run your problematic query single-threaded with the query hintMAXDOP 1

Another miracle cure to deadlocks is to enable optimistic concurrency, especially Read Committed Snapshot Isolation (RCSI), because it's completely transparent to your application as discussed 2 weeks ago. With optimistic concurrency, S locks are gone, which means that you can eliminate a huge amount of typical deadlock situations in SQL Server.

 

Summary
Deadlocks are automatically handled by SQL Server by rolling back the cheapest transaction. Nevertheless you have to make sure that you minimize deadlocks as much as possible, because every rolled back transaction influences your end-users in a negative way. Deadlocks can be avoided by a good indexing strategy, and using optimistic concurrency can be also a miracle cure for them.

Next week we will start off the final month of the SQLpassion Performance Tuning Training Plan, which is all about performance monitoring and troubleshooting in SQL Server. Expect an intensive(adj. 加强的，强烈的; ) final month with me!