« All posts

Deadlocks, detection and handling using AimBetter

A deadlock in SQL Server occurs when two or more sessions inside of the database engine end up waiting for access to locked resources held by each other. In a deadlock situation, none of the sessions can continue to execute until one of those sessions releases its locks, so allowing the other session(s) access to the locked resource. Multiple processes persistently blocking each other, in an irresolvable state, will eventually result in a halt to processing inside the database engine. A common misconception is that DBAs need to intervene to kill one of the processes involved in a deadlock. In fact, SQL Server is designed to detect and resolve deadlocks automatically, through the use the Lock Monitor, a background process that is initiated when the SQL Server Instance is started. You can monitor your locks using AimBetter in a much easier way, immediately see your deadlock victims and also see your deadlock survivors in the drilldown screen. 1 2 Using AimBetter, you may see the codes causing deadlocks and handle them in a much simpler way than using trace flags and XML Deadlock Graphs, also, unlike the conventional way, you can easily see historical deadlocks and analyze your system for long term trends, therefore you could see the effects of your deadlocks handling policy.

SQL Profiler XML Deadlock Graph event

In SQL Server 2005 and up, the Deadlock Graph event in SQL Trace captures the deadlock graph information, without writing it to the SQL Server Error Log. The Deadlock Graph event is part of the Locks event category and can be added to a SQL Server Profiler trace by selecting the event in Profiler’s Trace Properties dialog, as shown in the following screenshot. 3 SQL Profiler can be configured to save the deadlock graphs separately, into XDL files, as shown in the following screenshot. 4 AimBetter would tell you the same information as the profiler but in a much easier way, all you need to do is to click on SQL Tools and then on DeadLocks, then you see all the details. 5

Handling Deadlocks to Prevent Errors

In most cases, the same issues that cause severe blocking in the database, such as poor database design, lack of indexing, poorly designed queries, inappropriate isolation level and so on, are also the common causes of deadlocking. In most cases, by fixing such issues, we can prevent deadlocks from occurring. Unfortunately, by the time deadlocks become a problem, it may not be possible to make the necessary design changes to correct them. Therefore, an important part of application and database design is defensive programming; a technique that anticipates and handles exceptions as a part of the general code base for an application or database. Defensive programming to handle deadlock exceptions can be implemented in two different ways:

  • database-side, through the use of T-SQL TRY…CATCH blocks
  • application-side, through the use of application TRY…CATCH blocks.

Since AimBetter lets you view and edit the code you won’t need the try and catch blocks, you’ll find the deadlock causes immediately: 6

Summary

This article has covered how to capture and interpret deadlock graph information in SQL Server to troubleshoot deadlocking. Most often, deadlocks are the result of a design problem in the database or code that can be fixed to prevent the deadlock from occurring. However, when changes to the database are not possible to resolve the deadlock, adding appropriate error handling in the application code reduces the impact caused by a deadlock occurring. The information included in this article should allow rapid and efficient troubleshooting of most deadlocks in SQL Server. AimBetter is the best tool in the market that helps you handle deadlocks and doesn’t require you to use complex code in order to troubleshoot deadlocks, additionally it monitors all of your database operations for you automatically, therefore, it saves you a lot of time and effort and is the best practice for your db maintenance.