SQL Server死鎖問題的排查和解決方法
一、背景
我們?cè)赨AT環(huán)境壓測(cè)的時(shí)候，遇到了如下的死鎖異常。
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
我們立即 查看應(yīng)用日志，找到報(bào)錯(cuò)的方法查看，發(fā)現(xiàn)在一個(gè)方法對(duì)同一張表進(jìn)行了3種操作，先INSERT，然后SELECT，最后DELETE。也就是說在同一個(gè)事務(wù)中，對(duì)同一張表先插入，然后查詢，最后根據(jù)查詢結(jié)果刪除。此時(shí)，我大概意識(shí)到問題所在了。但是UAT環(huán)境中，SQL Server數(shù)據(jù)庫是部署在客戶側(cè)的，不太好拿死鎖報(bào)告。所以我決定在本地模擬出來這個(gè)死鎖問題，然后進(jìn)行修復(fù)。
二、本地模擬死鎖
1.業(yè)務(wù)場(chǎng)景簡介
我們有一張userToken表，核心字段有id、loginId和token，主要用來記錄用戶的登錄token，用來控制系統(tǒng)中一個(gè)用戶能不能多次登錄。
我們出現(xiàn)死鎖問題的方法是登錄方法，該方法在登錄時(shí)會(huì)向userToken表中插入一條數(shù)據(jù)，插入成功之后回去第三方檢查這個(gè)用戶的狀態(tài)等是否正常，因?yàn)橛脩魯?shù)據(jù)是第三方維護(hù)的。如果檢查結(jié)果是這個(gè)用戶狀態(tài)不可用，那么就會(huì)去刪除這個(gè)用戶的token數(shù)據(jù)，同時(shí)給前端返回相應(yīng)的異常信息。問題就出在刪除的時(shí)候，是先根據(jù)用戶的loginId去查詢出該用戶的所有token數(shù)據(jù)，然后找出本次登錄的token數(shù)據(jù)，進(jìn)行刪除。為什么這里有問題后面我們?cè)僭敿?xì)說明。
2.在本地模擬死鎖
1). 準(zhǔn)備數(shù)據(jù)
要模擬這個(gè)死鎖場(chǎng)景，可以在 SQL Server Management Studio (SSMS) 或者DBeaver中創(chuàng)建一個(gè)簡單的腳本，我使用的是DBeaver也很好用。使用以下存儲(chǔ)過程代碼：
-- 1.創(chuàng)建一個(gè)示例 userToken 表
CREATE TABLE userToken (
id INT IDENTITY(1,1) PRIMARY KEY,
loginId VARCHAR(50),
token VARCHAR(50)
);
-- 2.創(chuàng)建一個(gè)存儲(chǔ)過程，以模擬登錄過程
CREATE PROCEDURE sp_Login
@loginId VARCHAR(50)
AS
BEGIN
-- 插入一個(gè)新記錄
INSERT INTO userToken (loginId, token) VALUES (@loginId, 'token_' + CONVERT(VARCHAR(50), NEWID()));
WAITFOR DELAY '00:00:05'; -- 模擬延遲，更容易發(fā)生死鎖
-- 選擇和刪除記錄
DECLARE @id INT;
SELECT @id = id FROM userToken WHERE loginId = @loginId;
DELETE FROM userToken WHERE id = @id;
END;
-- 3. 在第一個(gè)窗口中模擬第一個(gè)線程
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 4. 在第二個(gè)窗口中模擬第二個(gè)線程
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 5. 在兩個(gè)窗口中同時(shí)運(yùn)行，模擬并發(fā)登錄，并觀察執(zhí)行結(jié)果
2).執(zhí)行存儲(chǔ)過程并觀察死鎖發(fā)生
按照上面的步驟創(chuàng)建表和存儲(chǔ)過程，并分別在兩個(gè)窗口中同時(shí)執(zhí)行。可能需要執(zhí)行多次才能出現(xiàn)死鎖。如果出現(xiàn)下面的兩種之一，就說明已經(jīng)發(fā)生了死鎖。
情況一：
數(shù)據(jù)庫連接工具控制臺(tái)出現(xiàn)以下錯(cuò)誤：SQL Error [1205] [40001]: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
情況二：
通過sqlserver自帶的擴(kuò)展事件[system_health]查看死鎖的詳細(xì)信息，執(zhí)行下面的sql如果表格中有數(shù)據(jù)則已經(jīng)發(fā)生了死鎖。
SELECT xdr.value('@timestamp', 'datetime') AS [Date],
xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;
如上圖，已經(jīng)發(fā)生死鎖。
三、死鎖的詳細(xì)分析
1.查看死鎖報(bào)告
在上面第二步中，我們通過sqlserver自帶的擴(kuò)展事件[system_health]先拿到了死鎖報(bào)告。如下：
<event name="xml_deadlock_report" package="sqlserver" timestamp="2024-05-10T07:53:31.599Z">
<data name="xml_report">
<type name="xml" package="package0"/>
<value>
<deadlock>
<victim-list>
<victimProcess id="process19f4497c108"/>
</victim-list>
<process-list>
<process id="process19f4497c108" taskpriority="0" logused="284" waitresource="KEY: 6:72057594058768384 (e8a66f387cfa)" waittime="3342" ownerId="50677" transactionname="user_transaction" lasttranstarted="2024-05-10T15:53:23.250" XDES="0x19f4c400428" lockMode="S" schedulerid="3" kpid="7120" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-05-10T15:53:23.250" lastbatchcompleted="2024-05-10T15:51:07.110" lastattention="1900-01-01T00:00:00.110" clientapp="DBeaver 24.0.2 - SQLEditor <Script-7.sql>" hostname="NCSCND13691RVD0" hostpid="30508" loginname="sa" isolationlevel="read committed (2)" xactid="50677" currentdb="6" currentdbname="deadLockDatabase" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="deadLockDatabase.dbo.sp_Login" line="11" stmtstart="590" stmtend="698" sqlhandle="0x03000600dfe61621f0cd05016cb1000001000000000000000000000000000000000000000000000000000000">
SELECT @id = id FROM userToken WHERE loginId = @loginI
</frame>
<frame procname="adhoc" line="4" stmtstart="124" stmtend="166" sqlhandle="0x02000000b95c920287375badb00b99eeb827a3f3037c6bda0000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
</inputbuf>
</process>
<process id="process19f4497e4e8" taskpriority="0" logused="284" waitresource="KEY: 6:72057594058768384 (11ea04af99f6)" waittime="2677" ownerId="50681" transactionname="user_transaction" lasttranstarted="2024-05-10T15:53:23.917" XDES="0x19f4ffdc428" lockMode="S" schedulerid="2" kpid="1248" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-05-10T15:53:23.913" lastbatchcompleted="2024-05-10T15:52:46.183" lastattention="1900-01-01T00:00:00.183" clientapp="DBeaver 24.0.2 - SQLEditor <Script-2.sql>" hostname="NCSCND13691RVD0" hostpid="30508" loginname="sa" isolationlevel="read committed (2)" xactid="50681" currentdb="6" currentdbname="deadLockDatabase" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="deadLockDatabase.dbo.sp_Login" line="11" stmtstart="590" stmtend="698" sqlhandle="0x03000600dfe61621f0cd05016cb1000001000000000000000000000000000000000000000000000000000000">
SELECT @id = id FROM userToken WHERE loginId = @loginI
</frame>
<frame procname="adhoc" line="5" stmtstart="128" stmtend="170" sqlhandle="0x020000009bc16a079a9d61241dde15013e2cc413cd9c26920000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594058768384" dbid="6" objectname="deadLockDatabase.dbo.userToken" indexname="PK__userToke__3213E83FCAB09E1A" id="lock19f4f504a00" mode="X" associatedObjectId="72057594058768384">
<owner-list>
<owner id="process19f4497e4e8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process19f4497c108" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594058768384" dbid="6" objectname="deadLockDatabase.dbo.userToken" indexname="PK__userToke__3213E83FCAB09E1A" id="lock19f4f509180" mode="X" associatedObjectId="72057594058768384">
<owner-list>
<owner id="process19f4497c108" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process19f4497e4e8" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</value>
</data>
</event>2.分析死鎖報(bào)告
首先，在死鎖發(fā)生的過程中，我們可以通過以下sql查詢當(dāng)前表鎖持有的鎖有哪些。
--將userToken換成自己的表名
SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT' AND resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID('userToken');
我們可以看到在死鎖發(fā)生的過程中，userToken表上有2把IX鎖（意向排他鎖）。應(yīng)該就是上面執(zhí)行存儲(chǔ)過程中的2條INSERT語句產(chǎn)生的。
接下來，我們來詳細(xì)分析一下死鎖報(bào)告的內(nèi)容，以了解為什么會(huì)出現(xiàn)死鎖。
a.犧牲的進(jìn)程
從報(bào)告上我們可以看到<victimProcess>，犧牲的進(jìn)程是 process19f4497c108，它被suspend并等待共享鎖在一個(gè)關(guān)鍵資源上。在sqlserver中當(dāng)發(fā)生死鎖時(shí)，sqlserver會(huì)選擇犧牲其中的一個(gè)死鎖，釋放它所持有的鎖，從而打破死循環(huán)。
b.進(jìn)程列表
通過<process-list>我們可以看到本次有兩個(gè)進(jìn)程參與了死鎖。
process19f4497c108（被犧牲的進(jìn)程）
process19f4497e4e8
兩個(gè)進(jìn)程都在執(zhí)行 sp_Login 存儲(chǔ)過程，該過程將新記錄插入到 userToken 表中，然后根據(jù) loginId 列選擇和刪除記錄。從<executionStack>可以看到是在執(zhí)行SELECT @id = id FROM userToken WHERE loginId = @loginId的時(shí)候阻塞了，也就是去根據(jù)loginId去查詢的時(shí)候阻塞了。
這兩個(gè)進(jìn)程分別等待的資源是：KEY: 6:72057594058768384 (e8a66f387cfa)和KEY: 6:72057594058768384 (11ea04af99f6)。
KEY值的含義：KEY表示等待的資源是一個(gè)鍵，也就是索引中的特定行或行范圍。以KEY: 6:72057594058768384 (e8a66f387cfa)為例。6代表數(shù)據(jù)庫id，72057594058768384代表被鎖索引（index）的id，也就是某一個(gè)索引，(e8a66f387cfa)代表索引中內(nèi)部id，也就是在該索引中具體是哪一行，可以幫我們定位到表中特定的數(shù)據(jù)行。
關(guān)于前兩個(gè)，比較簡單可以通過系統(tǒng)表查詢出來。
--72057594058768384替換為死鎖報(bào)告中的KEY: 6:72057594058768384 (e8a66f387cfa)的中間數(shù)字部分 select db_id() as database_id, o. name, i. name, i. type from sys. indexes i inner join sys.objects o on i.object_id = o.object_id inner join sys.partitions p on p.index_id = i.index_id and p. object_id = i. object_id where p.partition_id = 72057594058768384
從下面的結(jié)果中可以看到和報(bào)告下面index_name一致，鎖定就是主鍵索引
關(guān)于(e8a66f387cfa)代表索引中內(nèi)部id，可以通過一個(gè)未公布的系統(tǒng)函數(shù) %%lockres%% 查看得到，如下
with cte as ( select %%lockres%% as resource_key, id from userToken with(index(PK__userToke__3213E83FCAB09E1A))--替換為自己的表名和死鎖報(bào)告中沖突的索引 ) select * from cte where resource_key in ( '(e8a66f387cfa)', '(11ea04af99f6)');--替換為死鎖報(bào)告中等待的resource_key
c.資源列表
從<resource-list>中可以看到，有兩個(gè)關(guān)鍵的鎖在userToken表上。
lock19f4f504a00：由 process19f4497e4e8 擁有，具有排他（X）鎖模式
lock19f4f509180：由 process19f4497c108 擁有，具有排他（X）鎖模式
死鎖發(fā)生是因?yàn)槊總€(gè)進(jìn)程都在等待共享鎖在一個(gè)資源上（userToken 表的 PK__userToke__3213E83FCAB09E1A 索引），而該資源已經(jīng)被另一個(gè)進(jìn)程以排他鎖模式擁有的。
d.死鎖場(chǎng)景
下面是死鎖報(bào)告中描述的死鎖場(chǎng)景：
- process19f4497c108將一條新記錄插入到userToken表中，并獲取了索引（PK__userToke__3213E83FCAB09E1A）的排他鎖（mode='X'）。
- process19f4497e4e8將一條新記錄插入到userToken表中，并獲取了索引（PK__userToke__3213E83FCAB09E1A）的排他鎖（mode='X'）。
- process19f4497c108 嘗試根據(jù) loginId 去查詢userToken表中的數(shù)據(jù)，由于process19f4497e4e8 持有了索引的排他鎖，所以process19f4497c108必須等待鎖的釋放。
- process19f4497e4e8 嘗試根據(jù) loginId 去查詢userToken表中的數(shù)據(jù)，由于process19f4497c108持有了索引的排他鎖，所以process19f4497e4e8 必須等待鎖的釋放。
- 此時(shí)，兩個(gè)進(jìn)程都在等待對(duì)方釋放鎖，結(jié)果導(dǎo)致死鎖。
e.結(jié)論
死鎖是由于 sp_Login 存儲(chǔ)過程的并發(fā)執(zhí)行導(dǎo)致的，這導(dǎo)致了 userToken 表上的爭(zhēng)用。每個(gè)進(jìn)程在 索引上的排他鎖阻止了另一個(gè)進(jìn)程執(zhí)行其選擇和刪除操作，導(dǎo)致死鎖。因?yàn)閮蓚€(gè)進(jìn)程都持有了 userToken 表的 PK__userToke__3213E83FCAB09E1A 索引的排他鎖（mode='X'），每個(gè)進(jìn)程都在等待另一個(gè)進(jìn)程釋放其鎖。
要解決這個(gè)問題，我們可以優(yōu)化存儲(chǔ)過程以減少 userToken 表上的爭(zhēng)用。
四、解決死鎖問題
有了上面對(duì)死鎖報(bào)告的詳細(xì)分析，我們了解到了死鎖產(chǎn)生的原因是鎖競(jìng)爭(zhēng)。那么我們可以減少一層鎖，以避免鎖的競(jìng)爭(zhēng)。修改后存儲(chǔ)過程如下：
-- 2.創(chuàng)建一個(gè)存儲(chǔ)過程，以模擬登錄過程
CREATE PROCEDURE sp_Login
@loginId VARCHAR(50)
AS
BEGIN
-- 插入一個(gè)新記錄
INSERT INTO userToken (loginId, token) VALUES (@loginId, 'token_' + CONVERT(VARCHAR(50), NEWID()));
-- 直接根據(jù)loginId刪除記錄，減少一次查詢，減少一次S鎖的獲取
DELETE FROM userToken WHERE loginId = @loginId;
END;
-- 3. 在第一個(gè)窗口中模擬第一個(gè)線程
DECLARE @loginId VARCHAR(50) = 'user1';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 4. 在第二個(gè)窗口中模擬第二個(gè)線程
DECLARE @loginId VARCHAR(50) = 'user2';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 5. 在兩個(gè)窗口中同時(shí)運(yùn)行，模擬并發(fā)登錄，并觀察執(zhí)行結(jié)果再次多次執(zhí)行上面的存儲(chǔ)過程，沒有再遇到過死鎖了。
新的存儲(chǔ)過程分析：
在這個(gè)修改后的場(chǎng)景中，我們可以看到，每個(gè)窗口中都執(zhí)行了一個(gè)事務(wù)，該事務(wù)包括插入一條記錄、刪除該記錄、并提交事務(wù)。
在這種情況下，死鎖的可能性非常小，因?yàn)槊總€(gè)窗口中的事務(wù)都是自包含的，不會(huì)等待另一個(gè)窗口中的事務(wù)釋放鎖。
- 當(dāng)?shù)谝粋€(gè)窗口執(zhí)行
INSERT語句時(shí)，它會(huì)獲取該索引的 X 鎖，并插入一條記錄。然后，它執(zhí)行DELETE語句，刪除該記錄，并釋放 X 鎖。最后，它提交事務(wù)。 - 同樣，第二個(gè)窗口執(zhí)行
INSERT語句時(shí)，它會(huì)獲取該索引的 X 鎖，并插入一條記錄。然后，它執(zhí)行DELETE語句，刪除該記錄，并釋放 X 鎖。最后，它提交事務(wù)。 - 由于每個(gè)窗口中的事務(wù)都是獨(dú)立的，不會(huì)等待另一個(gè)窗口中的事務(wù)釋放鎖，因此死鎖的可能性非常小。
通過以上步驟，成功解決這個(gè)死鎖問題。
以上就是SQL Server死鎖問題的排查和解決方法的詳細(xì)內(nèi)容，更多關(guān)于SQL Server死鎖問題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
SQLSERVER對(duì)索引的利用及非SARG運(yùn)算符認(rèn)識(shí)
SQL對(duì)篩選條件簡稱：SARG（search argument/SARG）當(dāng)然這里不是說SQLSERVER的where子句，是說SQLSERVER對(duì)索引的利用,感興趣的朋友可以了解下，或許本文的知識(shí)點(diǎn)對(duì)你有所幫助哈2013-02-02
SQL查詢連續(xù)登陸7天以上的用戶的方法實(shí)現(xiàn)
本文主要介紹了SQL查詢連續(xù)登陸7天以上的用戶的方法實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-12-12
SQL Server全文索引服務(wù)...2006-09-09
SQL Server Parameter Sniffing及其改進(jìn)方法
這篇文章主要介紹了SQL Server Parameter Sniffing及其改進(jìn)方法,需要的朋友可以參考下2017-06-06
SQL Server通過重建方式還原master數(shù)據(jù)庫
這篇文章主要為大家詳細(xì)介紹了SQL Server通過重建方式還原master數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下2016-09-09
SQL數(shù)據(jù)庫連接超時(shí)時(shí)間已到的問題
這篇文章主要介紹了SQL數(shù)據(jù)庫連接超時(shí)時(shí)間已到的問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
Sql?Server高版本數(shù)據(jù)庫數(shù)據(jù)備份后還原到低版本數(shù)據(jù)庫詳細(xì)步驟
不同版本SQL?Server數(shù)據(jù)庫備份還原存在問題,不能從高版本的數(shù)據(jù)庫導(dǎo)入到低版本數(shù)據(jù)中,這篇文章主要給大家介紹了關(guān)于Sql?Server高版本數(shù)據(jù)庫數(shù)據(jù)備份后還原到低版本數(shù)據(jù)庫的詳細(xì)步驟,需要的朋友可以參考下2023-10-10
本文給大家分享的是使用Python實(shí)現(xiàn)的暴力破解mssql用戶密碼的腳本的代碼，配合上合適的字典非常好用，有需要的小伙伴可以參考下2016-10-10
盤點(diǎn)SqlServer?分頁方式和拉姆達(dá)表達(dá)式分頁
這篇文章主要介紹了SqlServer?5種分頁方式和拉姆達(dá)表達(dá)式分頁，進(jìn)來看看吧,本文結(jié)合示例代碼給大家講解的非常詳細(xì)，需要的朋友可以參考下2022-12-12
這篇文章主要介紹了sql coalesce函數(shù)的具體使用,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-10-10