Sql-Server

不同程序中相同臨時表的鎖導致死鎖

  • January 23, 2022

我發現了一個僵局，似乎表明了我認為不可能的事情。死鎖涉及兩個過程：

1.process8cf948 SPID 63

  • 對臨時表 #PB_Cost_Excp_Process_Invoices_Work 執行 ALTER TABLE。
  • 擁有表 #PB_Cost_Excp_Process_Invoices_Work 上的 IX 鎖，對象 ID 為 455743580

2.process4cb3708 SPID 72

  • 在臨時表#PB_Cost_Excp_Process_Invoices_Work 上執行更新，這應該是它自己的表的唯一副本。
  • 擁有 #PB_Cost_Excp_Process_Invoices_Work 上的 Sch-M 鎖定，具有相同的對象 ID 455743580！

這應該是不可能的。我錯過了什麼嗎？#Temporary 表真的在這兩個 SPID 之間重用了嗎？

這是在帶有累積更新 1（版本 10.50.4260）的 SQL Server 2008 R2 Service Pack 2 上。

完整的未更改死鎖跟踪如下。請注意這兩個程序是如何在同一個對象 ID 上使用相同的表名 #PB_Cost_Excp_Process_Invoices_Work_SNIP_0000000D8519 進行操作的：

12/14/2012 13:46:03,spid23s,Unknown,waiter id=process8cf948 mode=X requestType=wait
12/14/2012 13:46:03,spid23s,Unknown,waiter-list
12/14/2012 13:46:03,spid23s,Unknown,owner id=process4cb3708 mode=Sch-M
12/14/2012 13:46:03,spid23s,Unknown,owner-list
12/14/2012 13:46:03,spid23s,Unknown,objectlock lockPartition=0 objid=455743580 subresource=FULL dbid=2 objectname=tempdb.dbo.#PB_Cost_Excp_Process_Invoices_Work_________________________________________________________________________________0000000D8519 id=lock371705d00 mode=Sch-M associatedObjectId=455743580
12/14/2012 13:46:03,spid23s,Unknown,waiter id=process4cb3708 mode=Sch-M requestType=wait
12/14/2012 13:46:03,spid23s,Unknown,waiter-list
12/14/2012 13:46:03,spid23s,Unknown,owner id=process8cf948 mode=IX
12/14/2012 13:46:03,spid23s,Unknown,owner-list
12/14/2012 13:46:03,spid23s,Unknown,objectlock lockPartition=3 objid=455743580 subresource=FULL dbid=2 objectname=tempdb.dbo.#PB_Cost_Excp_Process_Invoices_Work_________________________________________________________________________________0000000D8519 id=lock3139b4780 mode=IX associatedObjectId=455743580
12/14/2012 13:46:03,spid23s,Unknown,resource-list
12/14/2012 13:46:03,spid23s,Unknown,Proc [Database Id = 8 Object Id = 1857974987]
12/14/2012 13:46:03,spid23s,Unknown,inputbuf
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Submit_SP @SiteKey, @PWDate
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.DR_SubmitPaperwork_SP line=174 stmtstart=12912 stmtend=13018 sqlhandle=0x03000800cb72be6e500434018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Create_SP
   
   -- Clean up work table
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Submit_SP line=138 stmtstart=11890 stmtend=12012 sqlhandle=0x03000800428c1f1950f833018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,UPDATE #PB_Cost_Excp_Process_Invoices_Work
   SET PBCEPrcInv_RtlPkg_Item_Quantity = RtlPkg_Item_Quantity
   FROM #PB_Cost_Excp_Process_Invoices_Work
       INNER JOIN Item_Packages (NOLOCK)
           ON PBCEPrcInv_ItemPkg_Key = ItemPkg_Key
       INNER JOIN Retail_Packages (NOLOCK)
           ON ItemPkg_RtlPkg_Key = RtlPkg_Key
   
   -- Lookup pricebook cost
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Create_SP line=25 stmtstart=2394 stmtend=3050 sqlhandle=0x030008003a082846321f46018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,executionStack
12/14/2012 13:46:03,spid23s,Unknown,process id=process8cf948 taskpriority=0 logused=0 waitresource=OBJECT: 2:455743580:0  waittime=3739 ownerId=707053534 transactionname=UPDATE lasttranstarted=2012-12-14T13:45:59.327 XDES=0x3c4502930 lockMode=X schedulerid=4 kpid=7276 status=suspended spid=72 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-14T13:45:58.337 lastbatchcompleted=2012-12-14T13:45:58.337 clientapp=PDI WCF Services - pdidb01-PDIMaster.cfg hostname=PDIWEB01 hostpid=2084 loginname=pdiuser isolationlevel=read committed (2) xactid=707053534 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128568
12/14/2012 13:46:03,spid23s,Unknown,Proc [Database Id = 8 Object Id = 1857974987]
12/14/2012 13:46:03,spid23s,Unknown,inputbuf
12/14/2012 13:46:03,spid23s,Unknown,EXEC PB_ProcessExc_Costs_Submit_SP @SiteKey, @PWDate
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.DR_SubmitPaperwork_SP line=174 stmtstart=12912 stmtend=13018 sqlhandle=0x03000800cb72be6e500434018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,EXEC dbo.PB_ProcessExc_Costs_CreateInvoiceWorkTable_SP
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_Submit_SP line=58 stmtstart=5782 stmtend=5894 sqlhandle=0x03000800428c1f1950f833018da000000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,ALTER TABLE #PB_Cost_Excp_Process_Invoices_Work DROP COLUMN PBCEPrcInv_Filler
12/14/2012 13:46:03,spid23s,Unknown,frame procname=PDICompany_218_01.dbo.PB_ProcessExc_Costs_CreateInvoiceWorkTable_SP line=50 stmtstart=5382 stmtend=5538 sqlhandle=0x0300080025d75a14ffff4701969f00000100000000000000
12/14/2012 13:46:03,spid23s,Unknown,executionStack
12/14/2012 13:46:03,spid23s,Unknown,process id=process4cb3708 taskpriority=0 logused=0 waitresource=OBJECT: 2:455743580:3  waittime=3739 ownerId=707052778 transactionname=ALTER TABLE lasttranstarted=2012-12-14T13:45:58.517 XDES=0x5f48bce80 lockMode=Sch-M schedulerid=6 kpid=7212 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-12-14T13:45:58.513 lastbatchcompleted=2012-12-14T13:45:58.513 clientapp=PDI WCF Services - pdidb01-PDIMaster.cfg hostname=PDIWEB01 hostpid=2084 loginname=pdiuser isolationlevel=read committed (2) xactid=707052778 currentdb=2 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128568
12/14/2012 13:46:03,spid23s,Unknown,process-list
12/14/2012 13:46:03,spid23s,Unknown,deadlock victim=process4cb3708
12/14/2012 13:46:03,spid23s,Unknown,deadlock-list

更新

有問題的機器在任務管理器和設備管理器中顯示了 16 個處理器，因此啟用了鎖分區，並且兩個鎖位於不同的鎖分區上。我不知道鎖分區是否是一個促成因素。

我還在CSS SQL Server 工程師部落格上發現了這篇有趣的文章。

更新 2

在每個儲存過程結束時都會刪除臨時表。它們是通過模式創建#table、修改模式、插入、更新、選擇然後刪除來創建的。使用此臨時 #table 的公共過程有多個入口點，因此我們有一個中央 proc 來設置呼叫公共 proc 所需的列。否則，我們必須在所有入口點過程中複製相同的#table 定義。

該過程經常從多個客戶端應用程序中呼叫。一些客戶端應用程序從多個執行緒呼叫此程序。其他人一次執行一個。想想庫存/會計軟體，家庭辦公室並行處理數千家商店的數據，而商店本身也執行相同的過程。因此，如果在啟用鎖定分區時這是一個罕見的問題，那麼在我們更大的客戶數據庫中就不會那麼罕見了。

更新 3 - 2012-12-19

另一位客戶在 SQL Server 2012 build 11.0.2100 上遇到了同樣的問題。我在累積更新描述中沒有看到任何關於此問題的修復。研究。

更新 4 - 2013-02-13

Microsoft 已在以下更新中發布了此錯誤的修復程序：

如果在 SQL Server 2008 R2 中啟用了鎖分區，則在執行儲存過程以更改臨時表時發生死鎖時剛剛宣布了這個確切的問題。它從SQL Server 2008 R2 SP2 的累積更新包 4連結。

閱讀 SQL Server 修復說明終於得到了回報。

我們已經就這個問題與 Microsoft 建立了一個案例。Microsoft 確認此錯誤也會影響 SQL Server 2012。他們計劃在 SQL Server 2012 Service Pack 2 中發布修復程序（在我撰寫此答案時尚未發布）。

在 Microsoft 發布此服務包之前，SQL Server 2012 使用者可以通過跟踪標誌 1229禁用鎖定分區來繞過該問題。

請注意，此問題僅適用於具有 16 個或更多處理器的電腦。

有關鎖分區的更多資訊

感謝微軟的支持！他們非常迅速和樂於助人。

更新

該錯誤已在SQL Server 2012 Cumulative Update 2 For SQL Server 2012 SP 1中修復。

引用自：https://dba.stackexchange.com/questions/30477

相關問答

Sql-Server

我可以創建一個使用者定義的表類型並在同一個事務中使用它嗎？

  • November 25, 2020
檢視問答
Sql-Server

為什麼死鎖圖中沒有填充對象名和索引名？

  • January 10, 2019
檢視問答
Sql-Server

MERGE 是否可以防止死鎖和伺服器阻塞？

  • August 24, 2018
檢視問答
Sql-Server

可以從 SQL Server 2008 中的環形緩衝區中提取的死鎖 xml 創建圖表嗎？

  • July 4, 2018
檢視問答
Sql-Server

啟用 ALLOW_SNAPSHOT_ISOLATION 和 READ_COMMITTED_SNAPSHOT 時更新 SQL Server 中的查詢死鎖

  • April 10, 2017
檢視問答
Sql-Server

選擇語句的死鎖

  • June 15, 2016
檢視問答