20th 24-week deadlock (deadlocking)

Hello everyone, respond back to performance tuning training. today's discussion of the deadlock in SQL Server (deadlocking), the 5th month of training is over. A deadlock occurs when 2 queries wait for each other, and no query can continue its work. The first step is to summarize how SQL Server handles deadlocks. Finally, I'll show you some of the specific deadlocks in SQL Server, and how you can avoid and resolve them.

Deadlock handling (Deadlock hanging)

The benefit of deadlocks is that SQL Server automatically detects and resolves them. To resolve a deadlock, SQL Server needs to roll back the cheapest 2 transactions. In a SQL Server context, the cheapest transaction is a transaction that writes to the transaction log, with fewer bytes.

SQL Server uses the so-called deadlock Monitor to implement deadlock detection in a background process. This background process runs every 5 seconds, checking the current deadlock condition for deadlocks. The worst case, so the deadlock should not be more than 5 seconds. The rollback query received error 1205th. The "benefit" of deadlocks is that you can completely restore from that error situation without requiring user interaction. A smart developer must do the following to recover from a deadlock:

• When an exception is thrown, check the next 1205 error number
• Pause the application to give other queries some time to complete its transaction and release the locks it has acquired
• Resubmit a query that was rolled back by SQL Server

After the query is resubmitted, the query should continue without problems because the other blocked queries have completed its transactions. Of course, you should keep track of the deadlock again, so that you don't have to retry your business repeatedly.

You can troubleshoot deadlocks in different ways. SQL Server Profiler provides you with the Deadlock Graph event, which is detected as soon as the deadlock occurs. If you are in SQL Server 2008 and later, you can also use the Extended events (Extended events) to troubleshoot the deadlock situation. The extended time provides you with a System_health event Session, which is more always a deadlock since the last SQL Server restart. With the Enable 1222 trace flag, SQL Server writes deadlock information to the error log.

Deadlock Type

Multiple types of deadlocks can occur in SQL Server. In this section I would like to elaborate on the most common of the few.

Almost every SQL Server will see a classic deadlock is the famous bookmark lookup deadlock (Bookmark lookup Deadlock), which occurs when you read and write both clustered and nonclustered indexes. This is mainly because of the bad index design of the deadlock. In my daily life, as a troubleshooter for SQL Server, I can say that at least 90% of all deadlocks can be avoided by applying a better index design to your work. Bookmark Lookup deadlocks can be easily removed by providing coverage of nonclustered indexes described in the 8th Week performance Tuning training.

Another common deadlock is the so-called cyclic deadlock (cycle Deadlock), which occurs in different sequential access tables of your individual queries. To avoid that particular deadlock, you always have to make sure that the query accesses the table in the same order. In addition the "best" deadlocks that can occur in SQL Server are the so-called internal parallel deadlocks (intra-parallelism Deadlock), in parallel operators (distribute Streams, Gather Streams, Repartition Streams) has been deadlocked within the respective threads. A typical deadlock diagram is shown.

The picture itself is a very elegant artwork (pure art), because you hit 1 bugs in SQL Server to happen. Unfortunately, Microsoft is not going to fix this bug because it will introduce the possibility of regression (regressions). So you want to make sure that the query that caused the deadlock is single-threaded in SQL Server. There are several options you can use to get execution plan single-threaded execution:

• Work on the index design, such that the query cost is lower than the current parallel cost threshold (default 5)
• Using Query hints MAXDOP 1 Let SQL Server run your query with a single thread

Another deadlock-specific therapy (miracle cure) is to enable optimistic concurrency control (optimistic concurrency), especially read-commit snapshot Isolation (read Committed Snapshot Isolation (RCSI)) , which has been discussed 2 weeks ago, is completely transparent to your program (completely transparent). With optimistic concurrency control, the shared lock (S) disappears, which means that you can eliminate a large number of typical deadlocks in SQL Server.

Summary

Deadlocks are automatically processed by rolling back the cheapest transactions through SQL Server. But you have to make sure that the deadlock is as small as possible, because each rollback transaction negatively affects your end users. Deadlocks can be avoided by good index design, and using optimistic concurrency control can also be a special effect therapy.

Next week, we'll start the last one months of performance Tuning training , and we'll discuss everything in SQL Server for performance tuning and troubleshooting. Please join me in anticipation of the last one months of full fire !

20th 24-week deadlock (deadlocking)