SQLServer中排查死鎖及死鎖問題解決
一、背景
我們在UAT環(huán)境壓測的時候，遇到了如下的死鎖異常。
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.
我們立即 查看應用日志，找到報錯的方法查看，發(fā)現(xiàn)在一個方法對同一張表進行了3種操作，先INSERT，然后SELECT，最后DELETE。也就是說在同一個事務中，對同一張表先插入，然后查詢，最后根據(jù)查詢結果刪除。此時，我大概意識到問題所在了。但是UAT環(huán)境中，SQL Server數(shù)據(jù)庫是部署在客戶側的，不太好拿死鎖報告。所以我決定在本地模擬出來這個死鎖問題，然后進行修復。
二、本地模擬死鎖
1.業(yè)務場景簡介
我們有一張userToken表，核心字段有id、loginId和token，主要用來記錄用戶的登錄token，用來控制系統(tǒng)中一個用戶能不能多次登錄。
我們出現(xiàn)死鎖問題的方法是登錄方法，該方法在登錄時會向userToken表中插入一條數(shù)據(jù)，插入成功之后回去第三方檢查這個用戶的狀態(tài)等是否正常，因為用戶數(shù)據(jù)是第三方維護的。如果檢查結果是這個用戶狀態(tài)不可用，那么就會去刪除這個用戶的token數(shù)據(jù)，同時給前端返回相應的異常信息。問題就出在刪除的時候，是先根據(jù)用戶的loginId去查詢出該用戶的所有token數(shù)據(jù)，然后找出本次登錄的token數(shù)據(jù)，進行刪除。為什么這里有問題后面我們再詳細說明。
2.在本地模擬死鎖
1). 準備數(shù)據(jù)
要模擬這個死鎖場景，可以在 SQL Server Management Studio (SSMS) 或者DBeaver中創(chuàng)建一個簡單的腳本，我使用的是DBeaver也很好用。使用以下存儲過程代碼：
-- 1.創(chuàng)建一個示例 userToken 表
CREATE TABLE userToken (
id INT IDENTITY(1,1) PRIMARY KEY,
loginId VARCHAR(50),
token VARCHAR(50)
);
-- 2.創(chuàng)建一個存儲過程，以模擬登錄過程
CREATE PROCEDURE sp_Login
@loginId VARCHAR(50)
AS
BEGIN
-- 插入一個新記錄
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. 在第一個窗口中模擬第一個線程
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 4. 在第二個窗口中模擬第二個線程
DECLARE @loginId VARCHAR(50) = 'user';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 5. 在兩個窗口中同時運行，模擬并發(fā)登錄，并觀察執(zhí)行結果
2).執(zhí)行存儲過程并觀察死鎖發(fā)生
按照上面的步驟創(chuàng)建表和存儲過程，并分別在兩個窗口中同時執(zhí)行。可能需要執(zhí)行多次才能出現(xiàn)死鎖。如果出現(xiàn)下面的兩種之一，就說明已經(jīng)發(fā)生了死鎖。
情況一：
數(shù)據(jù)庫連接工具控制臺出現(xiàn)以下錯誤：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自帶的擴展事件[system_health]查看死鎖的詳細信息，執(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ā)生死鎖。
三、死鎖的詳細分析
1.查看死鎖報告
在上面第二步中，我們通過sqlserver自帶的擴展事件[system_health]先拿到了死鎖報告。如下：
<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.分析死鎖報告
首先，在死鎖發(fā)生的過程中，我們可以通過以下sql查詢當前表鎖持有的鎖有哪些。
--將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鎖（意向排他鎖）。應該就是上面執(zhí)行存儲過程中的2條INSERT語句產生的。
接下來，我們來詳細分析一下死鎖報告的內容，以了解為什么會出現(xiàn)死鎖。
a.犧牲的進程
從報告上我們可以看到<victimProcess>，犧牲的進程是 process19f4497c108，它被suspend并等待共享鎖在一個關鍵資源上。在sqlserver中當發(fā)生死鎖時，sqlserver會選擇犧牲其中的一個死鎖，釋放它所持有的鎖，從而打破死循環(huán)。
b.進程列表
通過<process-list>我們可以看到本次有兩個進程參與了死鎖。
process19f4497c108（被犧牲的進程）
process19f4497e4e8
兩個進程都在執(zhí)行 sp_Login 存儲過程，該過程將新記錄插入到 userToken 表中，然后根據(jù) loginId 列選擇和刪除記錄。從<executionStack>可以看到是在執(zhí)行SELECT @id = id FROM userToken WHERE loginId = @loginId的時候阻塞了，也就是去根據(jù)loginId去查詢的時候阻塞了。
這兩個進程分別等待的資源是：KEY: 6:72057594058768384 (e8a66f387cfa)和KEY: 6:72057594058768384 (11ea04af99f6)。
KEY值的含義：KEY表示等待的資源是一個鍵，也就是索引中的特定行或行范圍。以KEY: 6:72057594058768384 (e8a66f387cfa)為例。6代表數(shù)據(jù)庫id，72057594058768384代表被鎖索引（index）的id，也就是某一個索引，(e8a66f387cfa)代表索引中內部id，也就是在該索引中具體是哪一行，可以幫我們定位到表中特定的數(shù)據(jù)行。
關于前兩個，比較簡單可以通過系統(tǒng)表查詢出來。
--72057594058768384替換為死鎖報告中的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
從下面的結果中可以看到和報告下面index_name一致，鎖定就是主鍵索引
關于(e8a66f387cfa)代表索引中內部id，可以通過一個未公布的系統(tǒng)函數(shù) %%lockres%% 查看得到，如下
with cte as ( select %%lockres%% as resource_key, id from userToken with(index(PK__userToke__3213E83FCAB09E1A))--替換為自己的表名和死鎖報告中沖突的索引 ) select * from cte where resource_key in ( '(e8a66f387cfa)', '(11ea04af99f6)');--替換為死鎖報告中等待的resource_key
c.資源列表
從<resource-list>中可以看到，有兩個關鍵的鎖在userToken表上。
lock19f4f504a00：由 process19f4497e4e8 擁有，具有排他（X）鎖模式
lock19f4f509180：由 process19f4497c108 擁有，具有排他（X）鎖模式
死鎖發(fā)生是因為每個進程都在等待共享鎖在一個資源上（userToken 表的 PK__userToke__3213E83FCAB09E1A 索引），而該資源已經(jīng)被另一個進程以排他鎖模式擁有的。
d.死鎖場景
下面是死鎖報告中描述的死鎖場景：
- 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 必須等待鎖的釋放。
- 此時，兩個進程都在等待對方釋放鎖，結果導致死鎖。
e.結論
死鎖是由于 sp_Login 存儲過程的并發(fā)執(zhí)行導致的，這導致了 userToken 表上的爭用。每個進程在 索引上的排他鎖阻止了另一個進程執(zhí)行其選擇和刪除操作，導致死鎖。因為兩個進程都持有了 userToken 表的 PK__userToke__3213E83FCAB09E1A 索引的排他鎖（mode='X'），每個進程都在等待另一個進程釋放其鎖。
要解決這個問題，我們可以優(yōu)化存儲過程以減少 userToken 表上的爭用。
四、解決死鎖問題
有了上面對死鎖報告的詳細分析，我們了解到了死鎖產生的原因是鎖競爭。那么我們可以減少一層鎖，以避免鎖的競爭。修改后存儲過程如下：
-- 2.創(chuàng)建一個存儲過程，以模擬登錄過程
CREATE PROCEDURE sp_Login
@loginId VARCHAR(50)
AS
BEGIN
-- 插入一個新記錄
INSERT INTO userToken (loginId, token) VALUES (@loginId, 'token_' + CONVERT(VARCHAR(50), NEWID()));
-- 直接根據(jù)loginId刪除記錄，減少一次查詢，減少一次S鎖的獲取
DELETE FROM userToken WHERE loginId = @loginId;
END;
-- 3. 在第一個窗口中模擬第一個線程
DECLARE @loginId VARCHAR(50) = 'user1';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 4. 在第二個窗口中模擬第二個線程
DECLARE @loginId VARCHAR(50) = 'user2';
BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;
-- 5. 在兩個窗口中同時運行，模擬并發(fā)登錄，并觀察執(zhí)行結果
再次多次執(zhí)行上面的存儲過程，沒有再遇到過死鎖了。
新的存儲過程分析：
在這個修改后的場景中，我們可以看到，每個窗口中都執(zhí)行了一個事務，該事務包括插入一條記錄、刪除該記錄、并提交事務。
在這種情況下，死鎖的可能性非常小，因為每個窗口中的事務都是自包含的，不會等待另一個窗口中的事務釋放鎖。
- 當?shù)谝粋€窗口執(zhí)行
INSERT語句時，它會獲取該索引的 X 鎖，并插入一條記錄。然后，它執(zhí)行DELETE語句，刪除該記錄，并釋放 X 鎖。最后，它提交事務。 - 同樣，第二個窗口執(zhí)行
INSERT語句時，它會獲取該索引的 X 鎖，并插入一條記錄。然后，它執(zhí)行DELETE語句，刪除該記錄，并釋放 X 鎖。最后，它提交事務。 - 由于每個窗口中的事務都是獨立的，不會等待另一個窗口中的事務釋放鎖，因此死鎖的可能性非常小。
通過以上步驟，成功解決這個死鎖問題。
到此這篇關于SQLServer中排查死鎖及死鎖問題解決的文章就介紹到這了,更多相關SQL 排查死鎖內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
sqlserver 因為選定的用戶擁有對象，所以無法除去該用戶的解決方法
這篇文章主要介紹了sqlserver 因為選定的用戶擁有對象，所以無法除去該用戶，因為是附加數(shù)據(jù)庫選擇了與源服務器一樣的用戶導致2016-04-04
輕量級數(shù)據(jù)庫SQL?Server?Express?LocalDb介紹
這篇文章介紹了輕量級數(shù)據(jù)庫SQL?Server?Express?LocalDb，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-06-06
從安裝程序看，感覺SQL Server 2008的設計更靈活、更精確，安裝速度在我的筆記本上裝的虛擬機（分配了768M內存）中比較流暢，感覺比2005要好。2009-02-02
Merge關鍵字是一個神奇的DML關鍵字。它在SQL Server 2008被引入，它能將Insert,Update,Delete簡單的并為一句。通過本篇文章給大家介紹sqlserver中merge函數(shù)用法詳解，感興趣的朋友一起學習吧2015-11-11
對于數(shù)據(jù)庫運維人員來說創(chuàng)建session或者查詢時產生問題是常規(guī)情況，下面介紹一種很有效且不借助第三方工具的方式來解決類似問題，需要的朋友可以參考下2016-10-10
MSSQL 多字段根據(jù)范圍求最大值實現(xiàn)方法
MSSQL 多字段根據(jù)范圍求最大值實現(xiàn)語句，大家可以參考下2009-09-09
自己網(wǎng)站的世界杯猜想活動，整理了幾個排行榜。寫了半個小時的SQL，丟了多可惜，放在這里，反正是別人的地盤，不心疼。2010-07-07
本文主要介紹了SQL去除重復記錄的七種方法。具有很好的參考價值，下面跟著小編一起來看下吧2017-02-02
SQL Server實時同步更新遠程數(shù)據(jù)庫遇到的問題小結
這篇文章主要介紹了SQL Server實時同步更新遠程數(shù)據(jù)庫遇到的問題小結,需要的朋友可以參考下2017-04-04![淺談SQL Server 對于內存的管理[圖文]](http://img.jbzj.com/images/xgimg/bcimg9.png)
理解SQL Server對于內存的管理是對于SQL Server問題處理和性能調優(yōu)的基本，本篇文章講述SQL Server對于內存管理的內存原理2013-01-01