Sql-Server

為什麼我在 SQL 中遇到 1 個讀取和 1 個寫入執行緒的死鎖

  • April 23, 2012

如果有人可以請幫助我理解為什麼以下交易死鎖？我為每個表提供 2 個事務和索引：

交易一：

SELECT blockId, blockData, syncedToGeneration, itemId 
FROM blocks 
WHERE indexId=@indexId 
and itemId IN (SELECT itemId 
              FROM KeywordReferences 
              WHERE indexId=@indexId 
              AND keywordRootId IN (360,4498,359,1229))

交易2：

UPDATE blocks 
  SET blockData=@blockData,
      syncedToGeneration=@syncedToGeneration 
  WHERE indexId=@indexId 
AND blockId=@blockId

（請注意，第一個事務中的“IN”部分要長得多，包含大約​​ 30 個值，為了便於閱讀，我將其截斷）

blocks 表有以下索引：

  • indexId->blockId (Clustered)

  • indexId->itemId

indexId->itemId

keywordReferences 表具有以下索引：

  • indexId_>keywordRootId (Clustered)

  • indexId->keywordRootId->score

  • indexId->itemId

  • indexId->blockId

波紋管是死鎖圖xml輸出：

<deadlock-list>
<deadlock victim="process5a274c8">
 <process-list>
  <process id="process5a274c8" taskpriority="0" logused="0" waitresource="PAGE: 5:1:91671" waittime="2709" ownerId="122348" transactionname="SELECT" lasttranstarted="2012-04-19T21:23:26.680" XDES="0xf382aca0" lockMode="S" schedulerid="4" kpid="10992" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-04-19T21:23:26.650" lastbatchcompleted="2012-04-19T21:23:26.647" clientapp=".Net SqlClient Data Provider" hostname="AMIT-PC" hostpid="6752" loginname="sa" isolationlevel="read committed (2)" xactid="122348" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x020000002e9a3633b816ffc89dc234b4c0351887e4e1b2cf">
SELECT blockId, blockData, syncedToGeneration, itemId FROM blocks WHERE indexId=@indexId and itemId IN (SELECT itemId FROM KeywordReferences WHERE indexId=@indexId AND keywordRootId IN (360,4498,359,1229,2143,14330,7661,3755,1156,21490,5567,1933,429,28197,2,3165,524,3182,2655,27262,17407,2673,570,1478,3802,6838,19668,17,6586,2484,2794,1640,5171,2558,6592,5833,695,1199,2307,335,1351,6651,6899,3740,7048,22030,14356,597,3175,3965,3297,2711,14484,2761,2265,28,1647,3223,226,304,298,1157,197,2696,21172,19149,9,1159,135,1,3166,23325))     </frame>
    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
   </executionStack>
   <inputbuf>
(@indexId bigint)SELECT blockId, blockData, syncedToGeneration, itemId FROM blocks WHERE indexId=@indexId and itemId IN (SELECT itemId FROM KeywordReferences WHERE indexId=@indexId AND keywordRootId IN (360,4498,359,1229,2143,14330,7661,3755,1156,21490,5567,1933,429,28197,2,3165,524,3182,2655,27262,17407,2673,570,1478,3802,6838,19668,17,6586,2484,2794,1640,5171,2558,6592,5833,695,1199,2307,335,1351,6651,6899,3740,7048,22030,14356,597,3175,3965,3297,2711,14484,2761,2265,28,1647,3223,226,304,298,1157,197,2696,21172,19149,9,1159,135,1,3166,23325))    </inputbuf>
  </process>
  <process id="process5a13b88" taskpriority="0" logused="215304" waitresource="PAGE: 5:1:91669" waittime="2910" ownerId="128212" transactionname="user_transaction" lasttranstarted="2012-04-19T21:23:28.567" XDES="0xedcd9000" lockMode="IX" schedulerid="2" kpid="5500" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-19T21:23:29.007" lastbatchcompleted="2012-04-19T21:23:29.007" clientapp=".Net SqlClient Data Provider" hostname="AMIT-PC" hostpid="6752" loginname="sa" isolationlevel="read committed (2)" xactid="128212" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="154" sqlhandle="0x02000000f4d83b0df2bfedc5a346288c21fa78e07eb152f6">
UPDATE blocks SET blockData=@blockData, syncedToGeneration=@syncedToGeneration WHERE indexId=@indexId AND blockId=@blockId     </frame>
    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
   </executionStack>
   <inputbuf>
(@indexId bigint,@blockId int,@blockData ntext,@syncedToGeneration int)UPDATE blocks SET blockData=@blockData, syncedToGeneration=@syncedToGeneration WHERE indexId=@indexId AND blockId=@blockId    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="91671" dbid="5" objectname="isqdb.dbo.blocks" id="lock5c54700" mode="IX" associatedObjectId="72057594043826176">
   <owner-list>
    <owner id="process5a13b88" mode="IX"/>
   </owner-list>
   <waiter-list>
    <waiter id="process5a274c8" mode="S" requestType="wait"/>
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="91669" dbid="5" objectname="isqdb.dbo.blocks" id="lock5b84780" mode="S" associatedObjectId="72057594043826176">
   <owner-list>
    <owner id="process5a274c8" mode="S"/>
   </owner-list>
   <waiter-list>
    <waiter id="process5a13b88" mode="IX" requestType="wait"/>
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>
</deadlock-list>

這看起來像一個典型的表掃描案例（可能缺少索引，但可能更多）。SELECT 選擇了頁鎖粒度，表示大掃描（在blocks表上）。還要注意所有鎖是如何在同一個資源行集、聚集索引上的，這是 SELECT 不使用選擇性二級索引來定位行的另一個指示。您似乎希望 上的非聚集索引(indexId, itemId)（您描述為現有的）會被選擇，並且如果選擇它，查詢可能不會死鎖。從目前的情況來看，它看起來很可能因為選擇性而被忽略（它達到了臨界點）。在不了解您的工作負載和數據模型的情況下，很難建議更好的索引。至少我可以說項目的要求blockData, syncedToGeneration在 SELECT 列表中使索引不被覆蓋，因此也許將它們添加為包含的列將是第一步。但是，如果不知道這些列的大小（blockData似乎是一個大列的名稱……），再次難以預測結果。

為了舉例說明為什麼掃描是罪魁禍首，請考慮由 UPDATE 語句更新的任意兩行。UPDATE 將按順序更新它們，(indexId, blockId)但 SELECT 掃描將按順序看到它們(indexId, itemId)。除非在 和 之間存在函式依賴關係itemId，blockId以保證兩個索引鍵以相同的順序返回所有行，否則在聚集索引和非聚集索引之間總會有一些順序顛倒的行。它的要點是，因為 SELECT 進行掃描，所以保證訪問每一行。因此，任何由 UPDATE 更新的行都會被掃描訪問。有四種可能：

  • UPDATEd 的兩行都落後於（按聚集索引鍵順序）掃描的目前位置（正在掃描的目前行）。這是安全的，UPDATE 阻塞並等待掃描。
  • UPDATEd 的兩行都在掃描位置之前。同樣安全的是，掃描會阻止並等待更新。
  • UPDATEd 的第一行在目前掃描位置的後面，但第二行在前面。安全，更新塊。
  • 第一行 UPDATEd 在掃描的目前位置之前，但第二行在後面。這是一個有保證的死鎖。請注意，行在聚集索引順序中越多，一個在前面和一個在任何掃描位置後面的機率就越高。

在存在掃描的情況下，多行更新總是會出現這種情況。解決方案通常是用更具選擇性的操作代替掃描，這具有提高性能的額外好處。如果 SELECT 和 UPDATE 只訪問他們真正希望處理的行，那麼只有在同一邏輯數據上同時發生兩個操作時才會發生死鎖（即同時讀取和更新同一項目），但大多數時候在 OLTP 系統上，這是由業務工作流阻止的。它仍然可能發生，但頻率大大降低。附帶說明一下，即使對不相交的邏輯項進行操作，由於雜湊衝突，它仍然是機率遊戲。

實際上，一種簡單的方法是部署快照隔離。但是潛在的問題（掃描）只會被掩蓋，不會得到緩解。掃描引起的其他問題（性能差、延遲高和響應時間慢、緩衝池污染等）仍然存在。您應該修復掃描問題並部署快照隔離。

PS 請注意，掃描使用 PAGE 粒度鎖的事實沒有相關性。如果掃描使用 ROW 粒度，死鎖只會發生在行而不是頁上。但是選擇 PAGE 粒度這一事實對於掃描來說意義重大。

看起來這是一個可能的解釋：

  1. select 在 itemId 上的非聚集索引上保持一個共享鎖，等待在聚集索引上獲取另一個共享鎖，以便它可以檢索更多列。
  2. 更新修改了聚集索引的一頁，當然保留了排他鎖。它也等待修改非聚集索引。

您可以發布相關表和索引的 DDL 嗎？

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

相關問答

Sql-Server

有沒有辦法在 SQL SERVER 2008 中獲取目前事務 ID？

  • February 28, 2022
檢視問答
Sql-Server

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

  • January 23, 2022
檢視問答
Sql-Server

如何在 SQL Server 中鎖定表以防止其他事務 INSERT 並避免死鎖？

  • August 4, 2021
檢視問答
Sql-Server

診斷執行多層儲存過程的程序中的塊

  • March 2, 2021
檢視問答
Sql-Server

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

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

呼叫相同過程的相同資源死鎖

  • June 10, 2019
檢視問答