Mode:RangeS-U
Range locks indicate SERIALIZABLE isolation level. Are you using plain new TransactionScope()
? Unless you really really really want serializable isolation (which is never the case...) consider reducing the isolation level to something more manageable, start from read committed.
Mode:SIX
This is an entire object Shared lock (the S
in SIX
). The UPDATE could not had acquire it as it is, given the selectivity of the predicate [ColumnID] = @p
and the primary key constraint on ColumnID
. It must had been acquired by a previous scan on the whole table in the same transaction, and again we see a very high isolation level (at least repeatable, most likely again serializable).
First advice: post the deadlock XML, always. Do not post some picture of it, is useless. The XML contains critical information we would like to see (like, trivial example, the isolation level used...).
Second advice: post what else is the transaction doing before issuing the UPDATE.
Last: review your isolation level. Read the linked article about new TransactionScope()
considered harmful. Always use read committed, unless very very very clear requirement state you need higher isolation levels. SQL Server 2005 already offers row version based isolation levels, investigate moving to it.
I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could confirm this theory by capturing exchange spills and matching them up (or not) to the deadlock.
Writing exchange buffers to tempdb to resolve a deadlock is not ideal. Look to eliminate sequences of order-preserving operations in the execution plan (e.g. order-preserving exchanges feeding a parallel merge join). Unless it's not causing a noticeable performance problem, and you have other things to worry about.
Out of interest, is this problem likely to be exacerbated by high fragmentation/outdated statistics?
Fragmentation, no. Outdated Statistics: not in any specific sense I can think of, no. Of course unrepresentative stats are rarely a good thing generally.
The fundamental issue here is that parallelism works best when there are as few dependencies between threads as possible; preserved ordering introduces rather nasty dependencies. Things can easily get gummed up, and the only way to clear the logjam is to spill a bunch of rows held at exchanges to tempdb.
Note
Intra-query parallel deadlocks no longer generate xml graphs when the deadlock can be resolved with an exchange spill, following a fix released for Cumulative Update 10 for SQL Server 2017 and Cumulative Update 2 for SQL Server 2016 SP2.
Best Answer
First off, intra-query parallelism deadlocks are always a SQL Server bug. There's not much you can do to fix them, other than updating SQL Server, opening a support case, suppressing parallelism (eg adding a MAXDOP 1 hint), or trying to get a query that uses a simpler plan.
In this case in EF, if you can, set UseDatabaseNullSemantics to true. Otherwise the EF query translator generates TSQL queries that emulate the C# null comparison semantics. That will clean up the NULL handling in the query.
Other than that, this query appears to be a view, joined with another table, and then a paging query on top of that. So possibly can be simplified if you can figure out what the intent is.