SQL Server: Deadlock Analysis and Prevention
Pluralsight
Course Summary
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 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.
-
+
Course Syllabus
Introduction- 6m 58s
—Introduction 1m 50s
—Misconceptions 2m 52s
—Course Structure 2m 16sLocking Overview- 23m 21s
—Introduction 2m 24s
—Terminology 1m 43s
—Lock Granularity 2m 48s
—Lock Hierarchy 0m 56s
—Shared and Update Locks 2m 34s
—Exclusive and Intent Locks 2m 50s
—Schema and Key-Range Locks 2m 19s
—Lock Compatibility 2m 7s
—Isolation Levels and Locking Hints 1m 52s
—Lock Escalation 2m 6s
—Summary 1m 42sDeadlock Detection- 11m 50sCollecting Deadlock Information- 25m 22sDeadlock Analysis- 20m 21sExample Deadlock Scenarios- 51m 56sHandling Deadlocks- 18m 17s