Database Reference
In-Depth Information
from
sys.dm_tran_locks tl with (nolock) left outer join sys.dm_os_waiting_tasks wt with (nolock) on
tl.lock_owner_address = wt.resource_address and tl.request_status = 'WAIT'
where
tl.request_session_id <> @@SPID and tl.resource_type = 'KEY'
order by
tl.request_session_id
Figure 19-5.
Deadlock due to the scans. Lock requests at the time of the deadlock
As you see, the session with
SPID 51
was blocked by the session with
SPID 52
and vice versa. It does not matter
that in the end the sessions were not going to include those rows to the count calculation, SQL Server is unable to
evaluate the
CustomerId
filter until shared (S) locks were acquired and rows were read.
■
You will have such deadlock in any transaction isolation level where readers acquire shared (s) locks. It would
not deadlock in reaD UNCoMMItteD transaction isolation level where shared (s) locks are not used. although you can
still have the deadlocks in reaD UNCoMMItteD transaction isolation level due to the writer's collision. For example,
if you replace select statements with updates that introduce scan operations.
Note
As you can guess, to fix deadlocks caused by the scans and non-optimized queries you need to get rid of the
scans. In the case above, you can solve the problem by adding non-clustered index on
CustomerId
column.
This would change the plan for the select statement and replace clustered index scan with non-clustered index seek.
As a result, the session would not need to read the rows that have been modified by another session.
Deadlock Due to Simultaneous Read/Update Statements
In some cases you can have the deadlock when the multiple sessions are trying to read and update the same row
simultaneously. Let's assume that you have the non-clustered index on the table and one session wants to read the
row using this index. If the index is not covering and the session needs some data from the clustered index, you would
have the execution plan with the non-clustered index seek and key lookup operations. The session would acquire
shared (S) lock on the row in the non-clustered index first and then on the row in the clustered index. Meanwhile,
if you have another session that updates one of the columns that is part of the non-clustered index based on the
clustered key value, that session would acquire exclusive (X) locks in the opposite order; that is, on the clustered index
row first and on the non-clustered index row after that.
Figure
19-6
shows what happens after the first step. Both sessions successfully acquired the lock on the rows in
the clustered and non-clustered indexes.
