SQL Server selects the deadlock victim based on the following criteria:
\n1. Deadlock priority – the assigned DEADLOCK_PRIORITY of a given session determines
\nthe relative importance of it completing its transactions, if that session is
\ninvolved in a deadlock. The session with the lowest priority will always be chosen as
\nthe deadlock victim. Deadlock priority is covered in more detail later in this chapter.
\n2. Rollback cost – if two or more sessions involved in a deadlock have the same
\ndeadlock priority, then SQL Server will choose as the deadlock victim the session that
\nhas lowest estimated cost to roll back.
\n3,when occurs,it selects one of the participants as a victim, cancels that spid’s current batch, and rolls backs his transaction in order to let the other spids continue with their work. The deadlock victim will get a 1205 error:
\nTransaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<\/p>\n
There are circumstances (for example, a critical report that performs a long running
\nSELECT that must complete even if it is the ideal deadlock victim) where it may be
\npreferable to specify which process will be chosen as the deadlock victim in the event
\nof a deadlock, rather than let SQL Server decide based purely on the cost of rollback. As
\ndemonstrated in Listing 7.18, SQL Server offers the ability to set, at the session or batch
\nlevel, a deadlock priority using the SET DEADLOCK PRIORITY option.<\/p>\n
— Set a Low deadlock priority
\nSET DEADLOCK_PRIORITY LOW ;
\nGO
\n— Set a High deadlock priority
\nSET DEADLOCK_PRIORITY HIGH ;
\nGO
\n— Set a numeric deadlock priority
\nSET DEADLOCK_PRIORITY 2 ;-10\u523010<\/p>\n
SQL Server 2005 and 2008 however, have three named deadlock priorities; Low, Normal, and High, as well
\nas a numeric range from -10 to +10, for fine-tuning the deadlock priority of different
\noperations.
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server selects the deadlock victim based on the fol […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2199","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2199"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2199\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}