SQL Server selects the deadlock victim based on the following criteria:
1. Deadlock priority – the assigned DEADLOCK_PRIORITY of a given session determines
the relative importance of it completing its transactions, if that session is
involved in a deadlock. The session with the lowest priority will always be chosen as
the deadlock victim. Deadlock priority is covered in more detail later in this chapter.
2. Rollback cost – if two or more sessions involved in a deadlock have the same
deadlock priority, then SQL Server will choose as the deadlock victim the session that
has lowest estimated cost to roll back.
3,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:  
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There are circumstances (for example, a critical report that performs a long running
SELECT that must complete even if it is the ideal deadlock victim) where it may be
preferable to specify which process will be chosen as the deadlock victim in the event
of a deadlock, rather than let SQL Server decide based purely on the cost of rollback. As
demonstrated in Listing 7.18, SQL Server offers the ability to set, at the session or batch
level, a deadlock priority using the SET DEADLOCK PRIORITY option.

– Set a Low deadlock priority
SET DEADLOCK_PRIORITY LOW ;
GO
– Set a High deadlock priority
SET DEADLOCK_PRIORITY HIGH ;
GO
– Set a numeric deadlock priority
SET DEADLOCK_PRIORITY 2 ;-10到10

SQL Server 2005 and 2008 however, have three named deadlock priorities; Low, Normal, and High, as well
as a numeric range from -10 to +10, for fine-tuning the deadlock priority of different
operations.
 

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。