Database Reference
In-Depth Information
If it happens in exactly the same moment, you would have the deadlock and session that reads the data would be
chosen as the deadlock victim. Solution here is to make non-clustered index covering and avoid key lookup operation.
Unfortunately, as we discussed in Chapter 4, “Special Indexing and Storage Features,” that solution would increase the
size of the leaf rows in the non-clustered index and introduce additional overhead during data modification and index
maintenance. Alternatively, we can use optimistic isolation levels and switch to READ COMMITTED SNAPSHOT mode. We
will cover this approach in greater detail in Chapter 21, “Optimistic Isolation Levels.”
Deadlock Due to Multiple Updates of the Same Row
Another similar deadlock pattern can be introduced by multiple updates of the same row if the subsequent update
accesses or changes the columns in the different non-clustered indexes. This could lead to a deadlock situation
similar to what you saw above when another session places the lock to the non-clustered index row in between
the updates. One of the most common implementation scenarios when it happens are the AFTER UPDATE triggers
updating the same row.
Let's look at the situation when you have a table with clustered and non-clustered indexes and the AFTER UPDATE
trigger defined. Let's have Session 1 update the column that does not belong to the non-clustered index. This step is
shown in Figure 19-8 . It acquires exclusive (X) lock on the row from the clustered index only.
Figure 19-8. Deadlock due to the multiple updates of the same row, Step 1
The update forces the AFTER UPDATE trigger to fire. Meanwhile, let's assume that you have another session, which
is trying to select the same row using the non-clustered index. This session successfully acquires the shared (S) lock
on the non-clustered index row during the INDEX SEEK operation, although the session would be blocked when
trying to obtain the shared (S) lock on the row from the clustered index during the KEY LOOKUP operation,
as shown in Figure 19-9 .
 
Search WWH ::




Custom Search