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. 結論

到這里，本次死鎖發生的原因其實已經明確了，原因可以歸結為，在一個事務記憶體在對某一資源的重復申請與釋放，在并發場景下，就可能會因為對該資源的爭奪而產生死鎖，在開發的程序中，應當盡量避免在并發情況下，對同一資源的重復申請與釋放

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551780.html

標籤：SQL Server

上一篇：count(列名)、count(1)和 count(*)有什么區別?