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
More
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
More
Introduction
6m 58s
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.

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Table of Contents
Introduction
6m 58s
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.
Exercise Files

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial
Transcript

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial
Discussion

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial
Knowledge Check

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Course info
Level
Intermediate
Rating
(258)
Duration
2h 38m
Released
18 Oct 2012
Course authors

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.

Share course