SQL Server临时表因异步SELECT语句引发死锁问题排查

我们有一个基于C#异步数据库连接开发的应用，尝试同时执行两条仅包含SELECT操作的语句，两条语句均引用同一个临时表，却出现死锁报错。

死锁图显示死锁双方为同一SPID，且都尝试获取tempdb中该临时表的排他锁（X锁）。相同代码与数据在旧版SQL Server中运行正常，但迁移至SQL Server 2019测试环境后触发死锁。

已全局启用Trace Flags 1204和1222，确认引发死锁的对象为该临时表。

SQL错误日志信息 #

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

死锁图 #

补充信息 #

• 将C#代码改为串行调用（取消异步）后不再报错，原因是不存在对同一tempdb对象的并发访问
• 旧版SQL Server无此问题，两个实例唯一差异为SQL Server版本；已将SQL Server 2016的mdf/ldf文件附加到2019实例，二者兼容性级别一致

死锁XML报告 #

<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>

内容的提问来源于stack exchange，提问作者Jason Boone