Search code examples
c#sql-server-2019database-deadlockstempdb

Async SELECT statements causing deadlock on a SQL Server temp table

We have an application making an async database connection in C# that is attempting to run two SELECT statements at the same time. Both statements are referencing the same temp tables but are only SELECT, but we are seeing a deadlock occurring and the operation errors.

The deadlock graph indicates the same SPID for both sides and shows it is attempting to get an exclusive lock on the tempdb object (temp table). The same code on the same data in an older version of SQL server works but when testing migration to SQL Server 2019 we are seeing deadlocks causing error in the application. Determined the object causing the deadlock was a temp table by enabling Trace Flags 1204 and 1222 globally.

From SQL error log:

objectlock lockPartition=0 objid=-1598015006 subresource=FULL dbid=2 objectname=tempdb.dbo.#TempTable__________________________________________________________________________________________________________0000000049FC id=lock219d47a4c80 mode=X associatedObjectId=-1598015006

deadlock_graph

When we change the C# to make the call in serial (no async) there is no error - which makes sense because there is no concurrent access to the same tempdb object.

In the older SQL version, we do not have the deadlocks. The only obvious difference between the two instances is the SQL Server version. Copied the mdf/ldf from the SQL Server 2016 instance to the SQL Server 2019 instance and attached. Compatibility level is the same on both.

EDIT: Deadlock XML report

<deadlock>
 <victim-list>
  <victimProcess id="process21a00a2b848" />
 </victim-list>
 <process-list>
  <process id="process21a00a2b848" taskpriority="0" logused="0" waitresource="OBJECT: 2:-1346206128:0 " waittime="2837" ownerId="25719004" transactionname="SELECT" lasttranstarted="2024-01-12T10:19:32.853" XDES="0x21568dce188" lockMode="X" schedulerid="13" kpid="10232" status="suspended" spid="147" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2024-01-12T10:19:32.817" lastbatchcompleted="2024-01-12T10:19:31.813" lastattention="1900-01-01T00:00:00.813" clientapp=".Net SqlClient Data Provider" hostname="HOSTMACH1" hostpid="11172" loginname="DOMAIN1\Account1" isolationlevel="read committed (2)" xactid="25719004" currentdb="6" currentdbname="TestDB1" lockTimeout="4294967295" clientoption1="671088928" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="838" stmtend="6934" sqlhandle="0x020000008e6ea223919a9751bfa1ae026e9f70a4edb236af0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@Var0 nvarchar(36),@Var1 nvarchar(36),@Var2 nvarchar(36),@Var3 nvarchar(36),@Var4 nvarchar(36),@Var5 nvarchar(36),@Var6 nvarchar(36),@Var7 nvarchar(36),@Var8 nvarchar(36),@Var9 nvarchar(8),@Var10 nvarchar(8),@Var12 nvarchar(9),@Var11 nvarchar(8),@Var13 decimal(5,2),@Var14 uniqueidentifier)
                                        SELECT ID1, Val1,CASE WHEN Date1 is NULL then Date2 ELSE Date1 END as displayDate, Date2, Val2,Val0,Val3,Val4, Val5, Val6, Val7, Val8, Val9, isnull(Val10,0) as Val10, isnull(Val11,0) as Val11, #TempTable1.Val12 Val12, '' as Val13,   
                                            table1.* , Val14, Val15 as Val15   
                                            ,substring(convert(varchar(36),ID1),0,23) as TransID, substring(convert(varchar(36),val   </inputbuf>
  </process>
  <process id="process21a00a43c28" waittime="2874" schedulerid="16" kpid="2816" status="suspended" spid="147" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2024-01-12T10:19:32.817" lastbatchcompleted="2024-01-12T10:19:31.813" lastattention="1900-01-01T00:00:00.813" clientapp=".Net SqlClient Data Provider" hostname="HOSTMACH1" hostpid="11172" loginname="DOMAIN1\Account1" isolationlevel="read committed (2)" xactid="25719004" currentdb="6" currentdbname="TestDB1" lockTimeout="4294967295" clientoption1="671088928" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="838" stmtend="6934" sqlhandle="0x020000008e6ea223919a9751bfa1ae026e9f70a4edb236af0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@Var0 nvarchar(36),@Var1 nvarchar(36),@Var2 nvarchar(36),@Var3 nvarchar(36),@Var4 nvarchar(36),@Var5 nvarchar(36),@Var6 nvarchar(36),@Var7 nvarchar(36),@Var8 nvarchar(36),@Var9 nvarchar(8),@Var10 nvarchar(8),@Var12 nvarchar(9),@Var11 nvarchar(8),@Var13 decimal(5,2),@Var14 uniqueidentifier)
                                        SELECT ID1, Val1,CASE WHEN Date1 is NULL then Date2 ELSE Date1 END as displayDate, Date2, Val2,Val0,Val3,Val4, Val5, Val6, Val7, Val8, Val9, isnull(Val10,0) as Val10, isnull(Val11,0) as Val11, #TempTable1.Val12 Val12, '' as Val13,   
                                            table1.* , Val14, Val15 as Val15   
                                            ,substring(convert(varchar(36),ID1),0,23) as TransID, substring(convert(varchar(36),val   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="-1346206128" subresource="FULL" dbid="2" objectname="tempdb.dbo.#TempTable1__________________________________________________________________________________________________________00000000469C" id="lock217a1a7a080" mode="X" associatedObjectId="-1346206128">
   <owner-list>
    <owner id="process21a00a43c28" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process21a00a2b848" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
  <TransactionMutex>
   <TransactionInfo Workspace="2308500986304" />
   <owner-list>
    <owner id="process21a00a2b848" />
   </owner-list>
   <waiter-list>
    <waiter id="process21a00a43c28" />
   </waiter-list>
  </TransactionMutex>
 </resource-list>
</deadlock>
Solution

  • This was resolved by updating SQL Server 2019 to the latest Cumulative Update. A SQL Server bug was likely causing the issue and resolved once updated.

    For reference, it appears to have been resolved in CU 19: https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate19#2162840