觸發器上的鎖升級問題
我繼承了一個 SQL Server 2005 數據庫，該數據庫每天會發生 2-3 次死鎖。
我已將其跟踪到白天執行並插入帶有觸發器的表中的計劃作業。
觸發器由對另一個表的 10 次更新組成，標準略有不同。死鎖發生在觸發器中。
當一個人提出一個應用程序並且工作正在執行時，就會發生死鎖。應用程序插入到與計劃作業相同的表中。
查看跟踪似乎發生在程序 1 獲得鍵鎖，程序 2 獲得頁鎖，然後程序 1 將鍵鎖升級為頁鎖並且程序 2 嘗試獲得鍵鎖時。
我添加了缺失的索引，這似乎有所幫助，但它仍在發生。我不是 DBA，因此對於解決此問題的方法的任何建議將不勝感激。
我添加了一個指向死鎖 xml 的連結——這是我為複制問題所做的測試。
最終解決方案需要包含所有索引的 tableA 的創建腳本和涉及的兩個 UPDATE 語句的執行計劃。話雖如此，我們仍然可以將提供的死鎖圖與 SQL Server 如何執行更新的知識結合起來，並且很有可能解決這個問題。
此死鎖涉及兩個 spid 對錶 A、58 和 59 執行更新。
Spid 58 的查詢：
Update b set syd_id=d.syd_id from tableA b inner join tableC d with (nolock) on b.syd_pers_id=d.syd_pers_id --and b.game='es_lotto' and b.game=d.game and isnull(b.syd_id,0)=0Spid 59 的查詢：
Update b set Draw_Date=d.draw_date from tableA b inner join tableB d with (nolock) on b.draw_no=d.draw_no and left(b.game,2)='UK' and b.Draw_Date is null在這個死鎖中，涉及到兩個資源：
- 索引 IX_tableA_Draw_Date 上的“鍵鎖”。
- 由 spid 58 擁有，模式 = ‘X’（獨家）
- 由 spid 59 請求，模式 = ‘U’（更新）
- tableA 上不同的未指定索引的“頁面”鎖
- 由 spid 59 擁有，模式 = ‘IX’（意圖獨占）
- 由 spid 58 要求，模式 = ‘U’（更新）
還使用了 3 種不同的鎖所有權模式（U、X、IX）：
- “U”或更新鎖。從概念上講，UPDATE 語句的 sql 計劃有兩個部分：一個“選擇”部分，其中標識要修改的數據，以及一個“修改”部分，其中實際發生修改。在“選擇”部分使用更新鎖以保護行不被修改，直到我們開始實際修改它們（此時獲取 X 鎖）。更新鎖與“S”（共享）鎖兼容，因此讀者不會被阻塞，但是它們與 X、IX 和其他 U 鎖不兼容。
- “X”或獨占鎖。排他鎖用於保護在插入、更新和刪除期間被修改的數據。它們與所有其他鎖類型不兼容，並確保只有一個寫入者可以同時訪問資源。
- “IX” 或 Intent Exclusive Lock。在較低級別的資源獲得 X 鎖之前，對較高級別的資源進行意向排他鎖。例如，在對行使用 X 鎖之前，會在關聯頁和整個表上都使用 IX 鎖。IX 鎖表示 X 鎖存在於較低級別。作為鎖升級的一部分，它們也可以更改為 X 鎖。IX 鎖與 X 鎖不兼容，因此如果兩個事務在一個表上具有 IX 鎖，則兩個事務都不能將它們的頁鎖或行鎖升級到表級別。Intent 鎖（包括 IX）是一種優化，它允許 SQL 檢查一個更高級別的鎖，而不是檢查數千個較低級別的鎖。
現在，讓我們詳細檢查 spid 58 的 UPDATE 查詢。我們從死鎖圖中知道 spid 58 在 IX_tableA_Draw_Date 中的一行上有一個排他鎖。因此，我們知道 syd_id 要麼在索引鍵中，在聚集索引鍵中，要麼是包含列（我將忽略 spid 58 已經擁有觸發器中先前語句中的鎖的可能性）。
Spid 58 還試圖在不同索引中的頁面上獲取更新鎖。這說明了兩件事：
- 我們知道，在修改部分開始後，更新的選擇部分仍在讀取和鎖定數據。這意味著沒有阻塞運算符影響計劃中 tableA 中的行。例如，如果從 tableA 讀取行、排序並在 MERGE 連接中使用；然後在獲取任何排他鎖之前將讀取所有行（並進行 U 鎖定）。事實並非如此。
- 我們正在嘗試在 PAGE 而不是 ROW 上獲取更新鎖，因此很可能我們正在執行索引掃描。
我們還可以假設預計會更新相對少量的行（由於多列連接結合在插入觸發器中更新空值或 0，這感覺不像是更新大部分行的查詢）。考慮到所有這些，很可能 UPDATE 正在使用掃描我們的神秘索引（不知道它是集群的還是非集群的）從 tableA 讀取行，並對 tableC 執行 LOOP JOIN 以獲取 tableC.syd_id .
對 spid 59 執行類似的分析，很可能我們正在尋找“Draw_Date 為空”的 IX_tableA_Draw_Date，讀取（並在 ROWS 上獲取更新鎖），針對 tableB 進行 LOOP JOIN，然後獲取 X ROW 鎖並更新我們的“神秘”索引（頁面上的 IX 鎖意味著行上有 X 鎖）。
我們在哪？我們接下來要做什麼？
我們知道我們有兩個更新語句。兩者都在讀取和寫入同一對索引（交換了讀取和寫入）。在他們已經更新了一些行之後，兩者都在等待更新鎖。Spid 58 正在讀取頁面，59 正在讀取行。兩者都在寫入和鎖定行。鎖升級不是一個因素，因為行鎖 -> 表鎖（不是頁鎖）。
如果 Spid 58 在神秘索引上使用行鎖而不是頁鎖，那麼只有兩個查詢更新同一行時才會出現死鎖。您可以使用 ROWLOCK 查詢提示來實現這一點，但是您會面臨增加鎖升級到表鎖的風險，這可能會更糟。
另一種方法是將更新分成兩部分。
select id, --Not sure what the PK is... syd_id into #tmp from tableA inner join tableB (nolock) on b.syd_pers_id=d.syd_pers_id --and b.game='es_lotto' and b.game=d.game and isnull(b.syd_id,0)=0 UPDATE tableA SET syd_id = t.syd_id from tableA d inner join #tmp t on t.id = d.id這將改變鎖定的順序，因為非聚集索引只會鎖定寫入，而不是讀取。
推薦
死鎖是 RDBMS 的自然副產品。即使你做的每件事都“正確”，你也不能總是消除它們。在每天 3-4 次死鎖時，正確的方法可能是將語句包裝在一些錯誤處理中，以便在發生死鎖時重新嘗試。
http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx有一個 SQL 2005 的範例。但是，由於這是在觸發器中，您可能需要添加錯誤處理在觸發觸發器的 INSERT 語句之外。