Can a single query deadlock itself? Apparently, yes: A curious case of Intra-query Parallelism

Imagine that you’re in a SQL data warehouse in the middle of the night, a single stored procedure is running, is nothing else, and it’s simply doing inserts and updates, one statement at the time, but then.. Deadlock. How can it be? Something else must be running, right? Someone launched something else, or a transaction was left open the day before, or the Russians were spying querying the server in secret, it must be something, right?!?

You open up the deadlock graph to find out who’s the other offending transaction, and you find this:

Marvelous. In every node there is exactly the same session, exactly the same process:

ART.

The XML confirms that only the same exact procedure is involved:

<deadlock>
  <victim-list>
    <victimProcess id="process1c116970c8" />
  </victim-list>
  <process-list>
 <process id="process1c116970c8" taskpriority="0" logused="10000" waittime="2517" schedulerid="10" kpid="4512" status="suspended" spid="72" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2018-02-24T22:43:23.923" lastbatchcompleted="2018-02-24T22:43:23.923" lastattention="1900-01-01T00:00:00.923" clientapp=".Net SqlClient Data Provider" hostname="GENERICHOSTNAME" hostpid="9300" isolationlevel="read committed (2)" xactid="2104669267" currentdb="7" lockTimeout="4294967295" clientoption1="538970208" clientoption2="128056">
 <executionStack>
 <frame procname="unknown" line="2" stmtstart="12" sqlhandle="0xSAMESQLHANDLE_1">
unknown </frame>
 <frame procname="unknown" line="430" stmtstart="29792" stmtend="29836" sqlhandle="0xSAMESQLHANDLE_2">
unknown </frame>
 <frame procname="unknown" line="1" sqlhandle="0xSAMESQLHANDLE_3">
unknown </frame>
 </executionStack>
 <inputbuf>
exec ASTOREDPROCEDURE @Param1=1, @Param2=2, @Param3=3 </inputbuf>
 </process>
 <process id="processb7167b0c8" taskpriority="0" logused="10000" waittime="2517" schedulerid="9" kpid="10916" status="suspended" spid="72" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2018-02-24T22:43:23.923" lastbatchcompleted="2018-02-24T22:43:23.923" lastattention="1900-01-01T00:00:00.923" clientapp=".Net SqlClient Data Provider" hostname="GENERICHOSTNAME" hostpid="9300" isolationlevel="read committed (2)" xactid="2104669267" currentdb="7" lockTimeout="4294967295" clientoption1="538970208" clientoption2="128056">
 <executionStack>
 <frame procname="unknown" line="2" stmtstart="12" sqlhandle="0xSAMESQLHANDLE_1">
unknown </frame>
 <frame procname="unknown" line="430" stmtstart="29792" stmtend="29836" sqlhandle="0xSAMESQLHANDLE_2">
unknown </frame>
 <frame procname="unknown" line="1" sqlhandle="0xSAMESQLHANDLE_3">
unknown </frame>
 </executionStack>
 <inputbuf>
exec ASTOREDPROCEDURE @Param1=1, @Param2=2, @Param3=3 </inputbuf>
 </process>
 <process id="process12ba785498" taskpriority="0" logused="10000" waittime="2517" schedulerid="12" kpid="13660" status="suspended" spid="72" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2018-02-24T22:43:23.923" lastbatchcompleted="2018-02-24T22:43:23.923" lastattention="1900-01-01T00:00:00.923" clientapp=".Net SqlClient Data Provider" hostname="GENERICHOSTNAME" hostpid="9300" isolationlevel="read committed (2)" xactid="2104669267" currentdb="7" lockTimeout="4294967295" clientoption1="538970208" clientoption2="128056">
 <executionStack>
 <frame procname="unknown" line="2" stmtstart="12" sqlhandle="0xSAMESQLHANDLE_1">
unknown </frame>
 <frame procname="unknown" line="430" stmtstart="29792" stmtend="29836" sqlhandle="0xSAMESQLHANDLE_2">
unknown </frame>
 <frame procname="unknown" line="1" sqlhandle="0xSAMESQLHANDLE_3">
unknown </frame>
 </executionStack>
 <inputbuf>
exec ASTOREDPROCEDURE @Param1=1, @Param2=2, @Param3=3 </inputbuf>
 </process>

<!-- This goes on like this for hundreds of lines -->

<process id="processbd4bab0c8" waittime="2484" schedulerid="10" kpid="0" />
  </process-list>
  <resource-list>
    <exchangeEvent id="Port1ec1edac00" WaitType="e_waitPortOpen" nodeId="11">
      <owner-list>
        <owner id="processb7167b0c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process1c116970c8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Port1ec1edac00" WaitType="e_waitPortOpen" nodeId="11">
      <owner-list>
        <owner id="process12ba785498" />
      </owner-list>
      <waiter-list>
        <waiter id="processb7167b0c8" />
      </waiter-list>
    </exchangeEvent>
 <!-- This goes on like this for hundreds of lines -->
    </exchangeEvent>
    <threadpool id="scheduler1ec5c20040">
      <owner-list>
        <owner id="processbd4baacf8" />
        <owner id="processbd4baa928" />
        <owner id="processbd4baa558" />
        <owner id="process1c11696928" />
        <owner id="process1c11697498" />
        <owner id="process1c116970c8" />
      </owner-list>
      <waiter-list>
        <waiter id="processbd4bab0c8" />
      </waiter-list>
    </threadpool>
  </resource-list>
</deadlock>

So, how can you react to something like this? After the initial amazement and screenshotting the hell out of the Deadlock Graph, you can only do the following:

• Update SQL Server to the latest CU (This happened on SQL Server 2012 SP2), not the freshest
• Rewrite/Optimize/Add indexes to your SQL Statement in order to have the query run serially