Sql-server – SQL Server 2016 SP2 – ONLINE REBUILD DEADLOCKed Itself – is this a bug

deadlocksql serversql-server-2016

ALTER INDEX [PK_Order_Line_Inventory] 
ON [OrderManager_C].[Order_Line_Inventory] REBUILD 
WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=SELF) )

This was satisfied by 3 threads within SPID 58. However, arguing over the same data page, one of the threads became the DEADLOCK victim. Process IDs are different, as are the Execution Context IDs and the Txn properties. There are actually two DEADLOCKs for this same command, one for the 3 threads, the 2nd for the surviving 2 that then became one.

The SQL 2017 Guidelines for Online Index Operations has this paragraph, "Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.", but as the deadlock involved "itself", my root cause wasn't found.

The table has just a single-column, bigint, Clustered PK, and contains 2.7M rows in 520MB; one FK to its parent in a 1::0/1 relationship.

Questions arise:

  1. Is this a bug? I've not seen this behavior in over a decade with SQL Server.
  2. Did any of the REBUILD survive?
  3. Is this likely to recur?
  4. I guess I could enhance our Defrag Tool to allow an option to override MAXDOP=4 to =1 for specific tables, but is that really necessary?

I've hunted for similar occurrences but what looked a promising link from SQLCoffee to MS KB978250 met a 404 error and no hits at all from searching MS (Original thread). That KB spoke of a fix to SQL2008 – if relevant, one would assume it should still be a fix in the 2016 bits…

Can anyone shed any light on this, please?

Graph: On OneDrive

Best Answer

Sounds like an intra-query parallelism deadlock.

They're generally considered bugs because there's not much you can do about it, other than to rerun the query or drop the maxdop of the statement down (if its happening often for the same query).

Here is some information to help troubleshoot them, hope this helps.

https://support.microsoft.com/en-us/help/4089473/better-intra-query-parallelism-deadlock-troubleshooting-in-sql-server2

Related Question