SQL Server死鎖診斷--同一行數據在不同索引操作下引起的死鎖

?

?

死鎖概述

對于數據庫中出現的死鎖，通俗地解釋就是：不同Session（會話）持有一部分資源，并且同時相互排他性地申請對方持有的資源，然后雙方都得不到自己想要的資源，從而造成的一種僵持的現象。
當然，在任何一種數據庫中，這種僵持的情況不會一直持續下去，因為一直持續下去雙方永遠都無法執行，沒有任何意義，
在SQL Server中，后臺線程會以3秒鐘一次的頻率檢測死鎖Session，并且選擇其中一個回滾代價相對較低的作為犧牲品，從而使解除不同Session相互僵持的現象。
因此SQL Server中死鎖的僵持時間不會超過3秒鐘。

通常情況下，最簡單也是最常見的死鎖是發生在不同表級別的，
Session 1 第一步修改A表，第二步修改B表，
Session 2第一步修改B表，第二步修改A表，
當發生Session 1與Session 2推進順序發生交叉的時候，死鎖就發生了，這種結局辦法也比較簡單，以相同的推進順序進行操作即可解除死鎖。

以下演示一種不用于以上情況，稍微特殊一點的死鎖。

?

同一張表上發生的死鎖演示

不過死鎖的種類有很多種，上述的僅是一種最簡單最常見的一種死鎖，
理論上，只要滿足死鎖發生的條件：不同Session（會話）排他性地持有一部分資源，并且相互申請對方持有的資源
都會產生死鎖，并不僅僅是在不同的表上，而是在不同的資源上，這種資源，可以是同一張表，甚至同一行數據上，以下舉例說明。

--TestDeadLock的Id是主鍵（默認生成聚集索引），Col2字段是唯一性的非聚集索引
create table TestDeadLock
(Id int constraint pk_TestDeadLock_id primary key,Col2 int constraint uk_TestDeadLock_col2 unique,Remark varchar(100)
)

然后利用SQLQueryStress，開啟兩個回話，分別按照聚集索引和非聚集索引，刪除同一行數據（造測試數據的時候會設置Id和Col2都為1），
如下圖所示
一開始先讓這兩個Session一直執行（空運行），隨后往TestDeadLock表中插入一行數據（insert into [TestDeadLock] values (1,1,newid())）
可能需要執行幾次嘗試，就會觀察到其中一個SQLQueryStress中發生了異常信息

打開其異常信息的詳細內容 ，會發現是死鎖

?

首先查一下表上索引的id，一下分析加鎖的過程中會用到。
pk_TestDeadLock_id 是聚集索引，其Id是 72057594050314240
uk_TestDeadLock_col2 是非聚集索引，其Id是 72057594050379776

利用sqlserver自帶的system_health擴展事件，觀察其死鎖信息（xml_deadlock_report）

SELECT  CAST(xet.target_data AS XML)
FROM    sys.dm_xe_session_targets xetJOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )
WHERE   xe.name = 'system_health'select xml_event_data,
xml_event_data.value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') Execution_Time,
xml_event_data.value('(event/data/value)[1]','varchar(max)') Query
from 
(SELECT event_table.xml_event_dataFROM(SELECT CAST(event_data AS XML) xml_event_data FROM sys.fn_xe_file_target_read_file(N'your path \system_health_*', NULL, NULL, NULL)) AS event_tableCROSS APPLY xml_event_data.nodes('//event') n (event_xml)WHERE  event_xml.value('(./@name)', 'varchar(1000)') IN ('xml_deadlock_report')   
) v 
order by Execution_Time

得到如下的死鎖信息，擴展事件中的xml_deadlock_report清楚吧地表明：對于當前這一行數據（8194443284a0一樣）
delete from [TestDeadLock] where Id= 1 　　　　等待非聚集索引上的鎖（waitresource="KEY: 11:72057594050379776 (8194443284a0)" ）
delete from [TestDeadLock] where Col2 = 1　 　? 等待聚集索引上的鎖（waitresource="KEY: 11:72057594050314240 (8194443284a0)" ）
兩者有死鎖，肯定是相互等待對方已經持有的資源（索引上的鎖）
因此，當前這個死鎖可以這么理解
delete from [TestDeadLock] where Id=1 　　　　持有聚集索引上的U鎖，申請非聚集索引上的X鎖
delete from [TestDeadLock] where Col2 = 1 　　 持有非聚集索引上的X鎖，申請聚集索引上的U鎖
結果：死鎖！

?

關于waitresource的解讀，參考：https://blog.csdn.net/kk185800961/article/details/41687209

?

兩個SQL對同一行數據的加鎖順序分析

上述分析只是根據已有現象推測其過程，如果能夠觀察到每一個sql語句執行過程中的鎖的申請與釋放順序，問題就更容易理解了。
以下利用profile觀察兩個語句執行過程中對鎖的申請和釋放順序

觀察一下delete from [TestDeadLock] where Id = 1 這句sql的執行過程的鎖的申請順序
profile里就很清楚，對于delete from [TestDeadLock] where Id = 1
先申請聚集索引（72057594050314240）page層面上的意向排它鎖（IX），轉為行級別的排它鎖（X），再申請非聚集索引（72057594050379776）的page層面意向排它鎖（IX），轉換為行級別排它鎖（X）

對于delete from [TestDeadLock] where Col2 = 1
先申請非聚集索引（72057594050379776）上page層面的意向更新鎖（IU），轉為行級別更新鎖鎖（U），再申請page層面聚集索引（72057594050314240）的意向排它鎖（IX），轉換為行級別排它鎖（X）

通過以上加鎖順序的分析，印證了上述加鎖方式的推測，不難理解兩個SQL語句為什么會發生死鎖。
仍然回到死鎖的概念上：不同Session（會話）排他性地持有一部分資源，并且同時申請對方持有的資源
這種相互持有的資源，可以是不同表上的資源，可以是同一個表上的資源，甚至可以是同一行數據的不同資源（不同索引的資源）
只要發生不同Session相互排他性地持有對方想要的資源，死鎖就會發生。

這種方式是雙方根據不同的索引同時delete引起的死鎖，類似上述情況，可以延伸到雙方同時update，雙方同時delete或者update，雙方同時update或者select等等
只要是索引推進順序不一致，都有可能引起死鎖的發生，此類問題可以歸結為同一行數據上，不同索引操作引起的死鎖。

?

如何解決？

對于常見的不同表上的推進順序不當造成的死鎖，只要改進持鎖的順序即可，也就是按照同一種方式來操作不同表中的數據。
對于上述的問題，不是不同表上的推進順序造成的，而是同一張表的同一行數據的資源推進順序不當導致的，在sql語句層面看起來并沒有什么不妥當的，因此只能從鎖的范圍或者隔離級別上進行調整。
1，嘗試從業務入手，是否能夠按照統一的方式對數據進行操作。
2，使用隊列消除并發操作的峰值。
3，嘗試tablockx，一次性鎖定整個表。
4，嘗試改變隔離級別，嘗試序列化隔離級別。

最后佛系一下：
很多問題都喜歡用奇怪解釋，其實很多問題并不奇怪，只是不知道而已，
技術上的問題，不知道也沒什么大不了，知道了更沒什么大不了，知道也僅僅是知道而已，不知道經歷一次就知道了，知不知道都沒有任何值得自豪或者自卑的
你的知識死角不能否定你的技術能力，應用層面的東西，只不過是在人家制定好的規則上玩游戲而已，誰也不要裝。

參考：
https://www.cnblogs.com/Uest/p/4998527.html
https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

?

?

需要注意的是：擴展事件中記錄的事件發生的時間，都是標準時間（格林威治時間），而其errorlog中或者自定義異常中的時間，都是當前時間

?