Database Reference
In-Depth Information
Wait on
Resource 1
SPID1
SPID2
Locks Held:
-Resource1
Locks Held:
-Resource2
Locks Requested
-Resource2
Locks Requested
-Resource1
Wait on
Resource 2
Figure 21-1.
A deadlock scenario
Deadlocks also frequently occur when two processes attempt to escalate their locking mechanisms on the same
resource. In this case, each of the two processes has a shared lock on a resource, such as an
RID
, and each attempts to
promote the lock from shared to exclusive; however, neither can do so until the other releases its shared lock. This too
leads to one of the processes being chosen as a deadlock victim.
Finally, it is possible for a single process to get a deadlock during parallel operations. During parallel operations,
it's possible for a thread to be holding a lock on one resource, A, while waiting for another resource, B; at the same
time, another thread can have a lock on B while waiting for A. This is as much a deadlock situation as when multiple
processes are involved, but instead involving multiple threads from one process. This is a rare event, but it is possible.
Deadlocks are an especially nasty type of blocking because a deadlock cannot resolve on its own, even if given an
unlimited period of time. A deadlock requires an external process to break the circular blocking.
SQL Server has a deadlock detection routine, called a
lock monitor
, that regularly checks for the presence of
deadlocks in SQL Server. Once a deadlock condition is detected, SQL Server selects one of the sessions participating
in the deadlock as a
victim
to break the circular blocking. The victim is usually the process with the lowest estimated
cost since this implies that process will be the easiest one for SQL Server to roll back. This operation involves
withdrawing all the resources held by the victim session. SQL Server does so by rolling back the uncommitted
transaction of the session picked as a victim.
Choosing the Deadlock Victim
SQL Server determines the session to be a deadlock victim by evaluating the cost of undoing the transaction of the
participating sessions, and it selects the one with the least estimated cost. You can exercise some control over the
session to be chosen as a victim by setting the deadlock priority of its connection to
LOW
.
SET DEADLOCK_PRIORITY LOW;
This steers SQL Server toward choosing this particular session as a victim in the event of a deadlock. You can
reset the deadlock priority of the connection to its normal value by executing the following
SET
statement:
SET DEADLOCK_PRIORITY NORMAL;
