0. 前情提要 系統的某個用來上報數據的介面存在死鎖的問題。這個介面內部對多張表進行了Update操作，執行順序為A表、B表、C表、D表、A表。死鎖發生的SQL，一條是第一次更新A表的SQL，另一條是第二次更新A表的SQL。整個更新都處在一個事務內，理論上講，只要第一個Session開始執行事務，第 ...
0. 前情提要
系統的某個用來上報數據的介面存在死鎖的問題。這個介面內部對多張表進行了Update操作，執行順序為A表、B表、C表、D表、A表。死鎖發生的SQL，一條是第一次更新A表的SQL，另一條是第二次更新A表的SQL。整個更新都處在一個事務內，理論上講，只要第一個Session開始執行事務，第二個Session就會由於無法獲取到A表的鎖而被阻塞，直到第一個Session執行完畢釋放鎖，那為什麼對A表的更新還會產生死鎖呢？
1. 準備工作
用準備來做測試的表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[Id] [int] NOT NULL,
[Progress] [int] NOT NULL,
[Score] [int] NOT NULL,
[Grade] [int] NOT NULL,
[Usn] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[IsDeleted] [int] NOT NULL,
CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [index_usn_and_project_id] ON [dbo].[Test]
(
[Usn] ASC,
[ProjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
插入一條測試用的數據
PRINT @@spid
insert into dbo.Test (Id, Progress, Score, Grade, Usn, ProjectId, IsDeleted)
VALUES (1, 1, 1, 1, 1, 1, 1);
2. 非聚簇索引的情況下，在表上執行一次update的加鎖過程
用來測試的update語句
BEGIN TRAN
PRINT @@spid
update dbo.Test
set Progress=2
WHERE Usn=1 and ProjectId=1
COMMIT TRAN
利用Sql Server Profiler追蹤加鎖與釋放鎖的過程
從圖中我們可以得知，在鎖增長階段，存在以下加鎖步驟：
- 根據輸入條件，找到非聚簇索引所在頁，並獲取非聚簇索引所在頁的IU鎖
- 獲取非聚簇索引的U鎖
- 根據非聚簇索引的值，找到聚簇索引（即主鍵索引）所在的頁，獲取頁的IU鎖
- 獲取聚簇索引的U鎖
- 將聚簇索引所在頁的鎖提升為IX鎖
- 將聚簇索引的鎖提升為X鎖
- 更新數據
- 釋放非聚簇索引所在頁的IU鎖
- 釋放非聚簇索引的U鎖
- 釋放聚簇索引的X鎖
- 釋放聚簇索引所在頁的IX鎖
所有的鎖都被爭取的申請與釋放
2. 非聚簇索引的情況下，在表上執行兩個update的加鎖
BEGIN TRAN
PRINT @@spid
update dbo.Test
set Progress=2
WHERE Usn=1 and ProjectId=1
update dbo.Test
set Score=2,Grade = 2
WHERE Usn=1 and ProjectId=1
COMMIT TRAN
從紅框中可以看到，非聚簇索引索引在第一個update時申請U鎖、釋放U鎖，併在第二個update時再次申請U鎖、釋放U鎖。
在這裡，有意思的事情就發生了：第一個update在聚簇索引上申請的X鎖，在第一個update執行完成後，並沒有像非聚簇索引一樣被釋放。
這裡猜測一個原因：由於非聚簇索引並不需要更新，它只是用來更新聚簇索引數據的一個跳板，因此在聚簇索引更新完後就被釋放了。而聚簇索引上的X鎖，則需要在事務commit之後才會被釋放。
為了驗證這一點，嘗試在第一次update時，同時更新ProjectId
BEGIN TRAN
PRINT @@spid
update dbo.Test
set Progress=2,ProjectId=2
WHERE Usn=1 and ProjectId=1
update dbo.Test
set Score=2,Grade = 2
WHERE Usn=1 and ProjectId=2
COMMIT TRAN
紅線上方的就是第一個update， 由於對非聚簇索引進行了修改，所以非聚簇索引頁申請了X鎖，且沒有在第一次Update語句結束後釋放鎖。由此驗證了上面的假設
3. 推測與模擬
由上面的內容，我們知道了一件事情：在Update時，會申請非聚簇索引上的U鎖，並且會在Update結束後（而不是事務提交後）釋放鎖；當在一個事務記憶體在兩個Update（且更新的是同一條數據）時，會在非聚簇索引上存在 獲取鎖 -> 釋放鎖 -> 獲取鎖 -> 釋放鎖 的過程。
同時，我們也知道，假如同時存在兩個Session要Update同一張表的同一條數據，那麼晚到的Session由於事務的原因，會等待第一個Session釋放非聚簇索引上的鎖（因為Update的入口就是獲取非聚簇索引的鎖）。
那麼，我們就能推理出一種情況：Session1執行完了第一個Update，並將自己持有的非聚簇索引的U鎖釋放，保留著聚簇索引的X鎖；Session2看到非聚簇索引的U鎖被釋放，於是獲取了U鎖；此時Session1試圖再次獲取非聚簇索引的U鎖，但非聚簇索引的U鎖已經被Session2占有。而Session2要進一步更新數據，就需要Session1手裡的聚簇索引的X鎖。於是，死鎖就發生了
接下來就是驗證：左側的sql先執行，在第一個update後，延遲1分鐘；左側sql開始之後，執行右側的sql。理論上講，此時左右會觸發死鎖：
最終的結果，成功觸發死鎖，右側的sql作為後來者被犧牲：
本次運行的加鎖、釋放鎖的過程，其中白色部分為左側sql的執行過程（spid=53），藍色的部分是右側的sql的執行過程（spid=57），下麵以53和57代替：
可以看到，53釋放了非聚簇索引後，57立刻獲取了非聚簇索引上的U鎖，並獲得了聚簇索引所在頁的IU鎖，但由於53任然持有聚簇索引的X鎖，導致57無法進一步申請聚簇索引的鎖：
而53這時試圖獲取非聚簇索引所在頁上的IU鎖（被57持有），由此引發了死鎖。經過一段時間後，資料庫發現死鎖的存在，並主動終結了57，讓57持有的資源被釋放，從而保證53可以順利執行
4. 結論
到這裡，本次死鎖發生的原因其實已經明確了，原因可以歸結為，在一個事務記憶體在對某一資源的重覆申請與釋放，在併發場景下，就可能會因為對該資源的爭奪而產生死鎖。在開發的過程中，應當儘量避免在併發情況下，對同一資源的重覆申請與釋放
