Database Reference
In-Depth Information
As you see, there is a clustered index scan involved. Now you have enough data for analysis. Select queries
scanned the entire table. Because both processes were using READ COMMITTED transaction isolation level, the
queries tried to acquire shared (S) lock on every row from the table and, at some point, were blocked by the exclusive
(X) locks held by another session. It did not matter that those rows did not have the
CustomerId
that the queries were
looking for. In order to evaluate this predicate, queries had to read those rows, which required acquiring shared (S)
locks on them.
The best way to solve this deadlock situation is by adding a non-clustered index on the
CustomerID
column,
as shown in Listing 19-8. This would eliminate the clustered index scan and replace it with an Index Seek operator,
as shown in Figure
19-19
.
Listing 19-8.
Adding non-clustered index
create nonclustered index IDX_Orders_CustomerID on Delivery.Orders(CustomerId)
Figure 19-19.
Execution plan for the query with non-clustered index
Instead of acquiring the shared (S) lock on every row of the table, the query would read only the rows that belong
to a specific customer. It would dramatically reduce the number of shared (S) locks that need to be acquired, and it
would prevent the query from being blocked by the other sessions that held exclusive (X) lock on the rows that belong
to different customers.
In some cases you can have intra-query parallelism deadlocks—when the query that runs with parallelism
deadlocks itself—but those cases are rare and usually introduced by a bug in SQL Server rather than application
or database issues. You can detect those cases when a deadlock graph has more than two processes with the same
SPID
and
resource-list
has
exchangeEvent
and/or
threadPoll
listed as the resources without any lock resources
associated with them. When it happens, you can work around the problem by reducing degree of parallelism for the
query with
MAXDOP
hint. There is also the chance that the issue has already been fixed in the latest service pack or
cumulative update.
Here are the other methods that can be used to obtain the deadlock graph:
•
Trace Flag 1222: This trace flag saves deadlock information to SQL Server Error Log files.
It is a perfectly safe method to use in production. We can enable it for all sessions with
DBCC
TRACEON(1222,-1)
command or by using startup
parameter -T1222
•
You can create event notification that fires when deadlock occurs
xml_deadlock_report
extended event
•
Capturing
system_health
extended event session. That session is enabled by default and
captures deadlock information. This could be a great place to start troubleshooting if no other
collection methods are enabled.
•
Examining