SQL Server: Deadlock Analysis and Prevention

Learn how to analyze and prevent troublesome deadlocks that can occur in SQL Server, applicable for developers, DBAs, and anyone who uses SQL Server from SQL Server 2005 onwards
Course info
Rating
(287)
Level
Intermediate
Updated
Oct 18, 2012
Duration
2h 38m
Table of contents
Introduction
Locking Overview
Deadlock Detection
Collecting Deadlock Information
Deadlock Analysis
Example Deadlock Scenarios
Handling Deadlocks
Description
Course info
Rating
(287)
Level
Intermediate
Updated
Oct 18, 2012
Duration
2h 38m
Description

Deadlocks can be very problematic when they occur, especially if you do not know how to figure out what's making the deadlocks occur so that you can take steps to prevent them. This course explains how the locking mechanism in SQL Server works and the circumstances that can lead to deadlocks occurring. The course then describes and demonstrates the various methods for detecting deadlocks in SQL Server and collecting information about the deadlocks so they can be analyzed, plus how to perform deadlock graph analysis using XML, Profiler, Management Studio and third-party tools. Common deadlock scenarios are explained and demonstrated, including lock escalation deadlocks, multi-victim deadlocks, reverse object order deadlocks, and more. The course concludes with how to handle deadlocks in Transact-SQL and ADO.NET code, including the implementation of custom retry logic. This course is perfect for those with no experience of deadlock analysis and those with some experience but who want to solidify their understanding of deadlocks and how to analyze and prevent them. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years.

More from the author
SQL Server: Change Data Capture
Intermediate
2h 10m
Mar 27, 2015
More courses by Jonathan Kehayias
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This course is deadlock analysis and prevention this is module 1 which will provide introduction to the rest of the course. So this course applies to all versions of SQL Server starting in 2005 and going onwards and there are going to be a couple of version specific features that we'll take a look at but I'll make sure that I point them out when we get to those as a part of the course or demonstrations that we'll be taking a look at. For me, understanding how to collect and analyze deadlock information is critical to anybody that works with SQL Server. And over the last 6, 7 years, I've answered literally hundreds of questions on the various forms that I take a look at from time to time about how do you collect deadlock information, how do you understand what's being presented to you in that information and then how do you make the right decision to be able to fix the root cause of the deadlock that's occurring. And for a lot of things, deadlocks don't have to be a problem for SQL Server applications. It's very easy to be able to design your database structure or your indexing so that most of the deadlocks that can occur will be prevented. It's also possible to do proper error handling. And this one is a really big one because a little bit of defensive coding early on in application design would prevent deadlocks even if they occur from negatively affecting your end users and giving them a bad experience with your application. There's also a lot of misconceptions about deadlocks that exist online and there's even more bad advice about how deadlocks should be dealt with or ways that you can prevent deadlocks from occurring. So, one of the goals of this course is going to be to look at these misconceptions and look at the bad advice and then show other ways or better alternatives for being able to troubleshoot and diagnose deadlocks inside of SQL Server.

Deadlock Detection
Hi. This is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 3: Deadlock Detection. So in this module we're going to take a look how SQL Server has been designed to be able to detect and handle deadlocks automatically as transactions are executing inside of the server. And manual intervention for deadlocks is not required because of the implementation of deadlock monitoring that exists inside of the database engine. There's a lock monitor thread that is running a background test that is ultimately responsible for deadlock detection inside of SQL Server. And it provides the management for being able to collect deadlock graphs and the database engine as well. Deadlock detection inside of SQL also extends into the SQLCLR-hosted implementation for. NET extensibility inside of the database engine. And the SQL OS actually will help with being able to detect deadlocks that occur and your CLR code if you're using it, and preventing those from tying up the database engine entirely and preventing other transactions from being able to execute. In most cases inside of SQL Server, the deadlock victim for a specific deadlock and how it gets selected is very predictable. And we'll take a look at how deadlock victim selection occurs as a part of this module. So what we're going to look at in this module is deadlock detection, deadlock victim selection, and then deadlock priorities, and how they affect deadlock victim selection inside of the database engine.

Collecting Deadlock Information
Hi. This is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is module four, Collecting Deadlock Information. So the first step for being able to do any kind of diagnostics or analysis of deadlocking inside of SQL Server is being able to collect the information that's generated from the lock monitor thread when a deadlock occurs, and then being able to use that information for your troubleshooting. There are a lot of different methods inside of SQL Server for being able to collect deadlock graphs. And in SQL Server 2005 and onwards, they're almost all going to generate the same information about the deadlock. It's just different ways of being able to consume that. The correct method depends on what your specific requirements are for the environment that you're going to be working in. And we'll take a look at all the different methods and then we'll talk at the end of this module about why you might pick one over the other. All of the methods will give you the necessary information to determine the root cause of the deadlock. And from that information, you'll be able to make determinations about how do you go about handling the deadlock in the most efficient or best way for your specific server. In this module, we're going to look at the trace flags that are available, using trace and profiler to collect deadlock information, using event notifications and WMI inside of the Database Engine to gather the deadlock graph information, and then using extended events for collecting deadlock graphs.

Deadlock Analysis
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is module 5, Analyzing Deadlock Graphs. So in the previous module we took a look at the different methods of being able to collect deadlock information from database engine. In this module we're going to take a look at how to read that information and actually interpret what's going on for the deadlock. And the deadlock graph that gets generated when a deadlock is detected by the lock monitor, it contains all the necessary information for you to be able to understand the cause of the deadlock and then be able to make changes to possibly prevent or reduce the amount of deadlocks that are occurring in your system. Regardless of which way you collected the deadlock, the same information is going to be provided by the deadlock graph. So, whatever way you wanted to collect the deadlock graph that best fits your specific environment we're going to be able to see exactly the same information. The only exception to this is the Extended Events XML graph which has a couple of differences, but they don't affect the actual way that you're going to about reading the data and interpreting it to understand what happened with the deadlock. So in this module, we're going to take a look at reading the deadlock XML, and then reading deadlock graphs graphically and how you can use either or method to be able to actually understand what's going on with deadlocks inside of your SQL Server.

Handling Deadlocks
Hi this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 7, Handling Deadlocks. So in this module, we're going to talk about ways that you can handle deadlocks in your application tier or in your coding doing some defensive coding so that they don't actually create problems for your end-users and when SQL server raises the deadlock, it's going to issue a 1205 error back to the client that was connected to it. And proper handling of that 1205 error will help with reducing your end-user impacts and you maybe able to make some additional design changes that could prevent the deadlock, but if you can't being able to handle that 1205 is really important for reducing the end-user impact that the deadlocks might be having. A good example of good deadlock management in application code or index crawls that reduce by Microsoft SharePoint Server, and SharePoint for many, many releases now has had issues with deadlocks that would occur during the index crawls and they've implemented coding in the application so that it will handle those deadlocks, resubmit the transactions and then log the error in the application tier so that you know that it started to occur and they could track the frequency of the deadlocks that are occurring. So in this module, what we're going to cover is handling deadlocks inside of your Transact-SQL code and then handling deadlocks inside of ADO. NET.