{"id":1828,"date":"2013-06-29T11:00:59","date_gmt":"2013-06-29T15:00:59","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1828"},"modified":"2017-09-25T22:22:59","modified_gmt":"2017-09-26T02:22:59","slug":"the-accidental-dba-day-29-of-30-troubleshooting-deadlocks","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/","title":{"rendered":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks"},"content":{"rendered":"<p><em>This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0<a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/ie0\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\">Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0<a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/immersion-events-schedule\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\">several times each year<\/a>. You can find all the other posts in this series at\u00a0<a href=\"https:\/\/www.SQLskills.com\/help\/accidental-dba\/?utm_source=accidentaldba&amp;utm_medium=blogs&amp;utm_campaign=training\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n<p>Deadlocks occur in SQL Server whenever two processes attempt to acquire locks on the same resources in reverse order creating a persistently blocked condition, where neither of the sessions can continue to execute until the other session releases its locks.\u00a0 In SQL Server, the Lock Monitor background task is responsible for detecting and resolving deadlocks whenever they occur (basically terminating the session that has performed the least amount of work so far), and the resulting 1205 error can be a sign of problems that require further evaluation.\u00a0 In a third-party vendor application, it may not be possible to make the changes necessary to eliminate deadlocks, but you can still collect information about the deadlocks to assist the third-party vendor in analysis and possibly identifying a solution to the problem.<\/p>\n<h2>Collecting Information<\/h2>\n<p>Prior to SQL Server 2008, collecting deadlock information from SQL Server required enabling a trace flag, configuring a SQL Trace, Event Notifications, or using a WMI Alert.\u00a0 Trace Flags 1222, 1205, or 1204 write the deadlock information as text into the ERRORLOG.\u00a0 SQL Trace, Event Notifications and\u00a0 WMI Alerts allow collection of the deadlock information as XML. Since the introduction of Extended Events and the new system_health event session in SQL Server 2008, deadlock information has been captured by default in SQL Server and no longer requires enabling additional data collection for analysis.<\/p>\n<h2>Analysis<\/h2>\n<p>The definitive source for understanding the output from trace flag 1222 is a series of blog posts\u00a0written by\u00a0Bart Duncan. His three-part series uses the output\u00a0from trace flag 1222\u00a0to demonstrate how to read the XML deadlock graph information,\u00a0starting with <a href=\"https:\/\/blogs.msdn.microsoft.com\/bartd\/2006\/09\/08\/deadlock-troubleshooting-part-1\/\" target=\"_blank\" rel=\"noopener noreferrer\">Deadlock Troubleshooting, Part 1<\/a>. The same method of analysis applies to deadlock graphs collected by SQL Trace, Event Notifications, WMI, and even Extended Events.\u00a0 The format of the deadlock graph defines the deadlock victim(s), each of the processes involved in the deadlock (within the <em>process-list<\/em> node), and the resources contributing to the deadlock (within the <em>resource-list<\/em> node).\u00a0 The processes each have an assigned <em>processid<\/em> that is used to uniquely identify each of the processes and the resources being locked or requested by the process in the graph.\u00a0 Within each of the process&#8217; information, the execution <em>tsql_stack<\/em> will show the most recently deadlocked statement backwards to the start of the execution call stack.<\/p>\n<p>One of the key areas of focus for deadlock analysis is the <em>resource-list<\/em> portion of the graph, which contains all the information about the resources involved and the lock types being held and requested by each of the processes.\u00a0 This will also contain the index and object names, or the allocation unit associated with the object, which can be used to determine the name of the object and index.\u00a0 Understanding the locking order between the processes is essential for deadlock troubleshooting.<\/p>\n<p>In addition to viewing the raw XML or text information for the deadlock, it is also possible to view the information graphically as explained in my blog post <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/graphically-viewing-extended-events-deadlock-graphs\/\">Graphically Viewing Extended Events Deadlock Graphs<\/a>.\u00a0 The graphical view in Management Studio will not show all of the same details as the XML or text, but can be a fast start for understanding the type of deadlock and locking order.\u00a0 It may be necessary to look at the text or XML for further information in some scenarios, or you can also open the graph graphically in SQL Sentry&#8217;s excellent <a href=\"http:\/\/greg.blogs.sqlsentry.net\/2012\/10\/it-time-to-go-pro.html\" target=\"_blank\" rel=\"noopener noreferrer\">Plan Explorer Pro<\/a> to get the full output parsed as a table as well.<\/p>\n<h2>Possible solutions<\/h2>\n<p>There are a lot of potential solutions to prevent deadlocks occurring, and the correct one will depend on the specific deadlock condition that is occurring. In some deadlock scenarios an index change to cover one of the queries executing may be all that is necessary to prevent the deadlock condition from being possible. \u00a0In other scenarios, it may be necessary to change isolation levels, or use locking hints to force a blocking lock that is incompatible with other locks to prevent the deadlock condition from being encountered. \u00a0Proper analysis of the deadlock graph will help with determining the appropriate solution to the problem, but in most cases simple error handling logic in Transact-SQL or .NET application code to handle the 1205 error and attempt to resubmit the victim transaction can prevent end users from being negatively affected by deadlocks occurring.<\/p>\n<h2>Summary<\/h2>\n<p>Troubleshooting deadlocks in SQL Server starts off with first collecting the deadlock graph information using one of the available methods. \u00a0Extended Events in SQL Server 2008 collect the information by default and eliminate the need to enable further collection and then waiting for the deadlocks to reoccur to gather the information. \u00a0Full details of how to configure deadlock graph collection and analysis of specific scenarios can be found in my SimpleTalk article <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/handling-deadlocks-in-sql-server\/\">Handling Deadlocks in SQL Server<\/a>\u00a0and in my Pluralsight online training course <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-deadlocks\" target=\"_blank\" rel=\"noopener noreferrer\">SQL Server: Deadlock Analysis and Prevention<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,20],"tags":[],"class_list":["post-1828","post","type-post","status-publish","format-standard","hentry","category-database-administration","category-deadlock"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-29T15:00:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-09-26T02:22:59+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks\",\"datePublished\":\"2013-06-29T15:00:59+00:00\",\"dateModified\":\"2017-09-26T02:22:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/\"},\"wordCount\":875,\"commentCount\":10,\"articleSection\":[\"Database Administration\",\"Deadlock\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/\",\"name\":\"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2013-06-29T15:00:59+00:00\",\"dateModified\":\"2017-09-26T02:22:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Administration\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/database-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/","og_locale":"en_US","og_type":"article","og_title":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias","og_description":"This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs &#8216;keep the SQL Server lights on&#8217;. It&#8217;s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-06-29T15:00:59+00:00","article_modified_time":"2017-09-26T02:22:59+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks","datePublished":"2013-06-29T15:00:59+00:00","dateModified":"2017-09-26T02:22:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/"},"wordCount":875,"commentCount":10,"articleSection":["Database Administration","Deadlock"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/","name":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2013-06-29T15:00:59+00:00","dateModified":"2017-09-26T02:22:59+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-accidental-dba-day-29-of-30-troubleshooting-deadlocks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Database Administration","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/database-administration\/"},{"@type":"ListItem","position":3,"name":"The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1828","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1828"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1828\/revisions"}],"predecessor-version":[{"id":2113,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1828\/revisions\/2113"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1828"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}