技術文章 » SQLSERVER 語句交錯引發的死鎖研究

SQLSERVER 語句交錯引發的死鎖研究

2023-02-20 12:02:20

一：背景

1. 講故事

相信大家在使用 SQLSERVER 的過程中經常會遇到 阻塞 和 死鎖，尤其是 死鎖，比如下面的輸出：


(1 row affected)
Msg 1205, Level 13, State 51, Line 5
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

要解決死鎖問題，個人感覺需要非常熟知各種隔離級別，尤其是 可提交讀 模式下的 CURD 加解鎖過程，這一篇我們就來好好聊一聊。

二：死鎖簡析

1. 一個測試案例

開啟兩個對談 65 和 66 ，分別使用如下查詢。


-- 對談 65 --
BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK

-- 對談 66 --
BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK

兩個對談非常簡單，交錯的對 Employees 和 Orders 進行 SELECT 和 UPDATE 操作，稍等幾秒後就會出現死鎖。

2. 尋找死鎖源頭

當我們的應用程式拿到了這樣的輸出其實作用是不大的，要想溯源最好就是通過不斷的對 SQLSERVER 進行監視來捕獲死鎖時的上下文資訊，手段也有很多：

  • SQL Server Profile

  • DBCC TRACEON(1222)

  • DMV VIEW

這裡我們就用第一種方式，一定要勾選 TextData 項，因為這裡面會有死鎖上下文資訊的xml表示，截圖如下：

將 profile 開啟後，重新執行剛才的兩個查詢，一旦出現死鎖，profile 就會成功捕獲，然後 copy 出 TextData 項，截圖如下：


<deadlock-list>
 <deadlock victim="process2d69c9748c8">
  <process-list>
   <process id="process2d69c9748c8" taskpriority="0" logused="324" waitresource="KEY: 7:72057594043170816 (8194443284a0)" waittime="1304" ownerId="70740" transactionname="user_transaction" lasttranstarted="2023-02-19T22:11:26.413" XDES="0x2d6a0200428" lockMode="S" schedulerid="5" kpid="13816" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-02-19T22:11:26.413" lastbatchcompleted="2023-02-19T22:11:26.410" lastattention="1900-01-01T00:00:00.410" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DESKTOP-STS8TPB" hostpid="1696" loginname="DESKTOP-STS8TPB\Administrator" isolationlevel="read committed (2)" xactid="70740" currentdb="7" currentdbname="Northwind" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="5" stmtstart="24" stmtend="128" sqlhandle="0x020000007383d935b349bc173c0f104de14945e9a526322b0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="5" stmtstart="204" stmtend="294" sqlhandle="0x020000002c3b203105961d63d10b17e54ed6ac081105f9450000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress=&apos;上海&apos; WHERE OrderID=10258
WAITFOR DELAY &apos;00:00:10&apos;
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK
    </inputbuf>
   </process>
   <process id="process2d6ae694ca8" taskpriority="0" logused="368" waitresource="KEY: 7:72057594044088320 (59ce0997f9b8)" waittime="3468" ownerId="70716" transactionname="user_transaction" lasttranstarted="2023-02-19T22:11:24.247" XDES="0x2d6a7284428" lockMode="S" schedulerid="9" kpid="7124" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-02-19T22:11:24.247" lastbatchcompleted="2023-02-19T22:11:24.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DESKTOP-STS8TPB" hostpid="1696" loginname="DESKTOP-STS8TPB\Administrator" isolationlevel="read committed (2)" xactid="70716" currentdb="7" currentdbname="Northwind" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="5" stmtstart="26" stmtend="118" sqlhandle="0x02000000dd7720067e0519b8a368501716c04b4b50cfe6be0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="5" stmtstart="196" stmtend="282" sqlhandle="0x0200000093f01512208755a056f5f28930fbd3dedf58a2850000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

BEGIN TRAN
UPDATE dbo.Employees SET Title=&apos;Dr.&apos; WHERE EmployeeID=1;
WAITFOR DELAY &apos;00:00:10&apos;
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594043170816" dbid="7" objectname="Northwind.dbo.Employees" indexname="PK_Employees" id="lock2d69ccbbb80" mode="X" associatedObjectId="72057594043170816">
    <owner-list>
     <owner id="process2d6ae694ca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2d69c9748c8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594044088320" dbid="7" objectname="Northwind.dbo.Orders" indexname="PK_Orders" id="lock2d69ccbbf80" mode="X" associatedObjectId="72057594044088320">
    <owner-list>
     <owner id="process2d69c9748c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2d6ae694ca8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

雖然上面有圖形化表示，但在生產環境下參考價值並不多，因為這張圖蘊含的資訊比較少，熟讀和整理 xml 的內容就非常必要了，截圖如下：

仔細觀察上面的這張圖可以清晰的看到，spid=66 持有了 Orders.PK_Orders 索引上雜湊碼為 59ce0997f9b8 鍵值的 X 鎖，之後需要再次獲取 Employees.PK_Employees 索引上雜湊碼為 8194443284a0 鍵值上的 S 鎖，很不巧的是，此時的 Employees.PK_Employees 索引上雜湊碼為 8194443284a0 的鍵值已經被 spid=65 的對談附加了 X 鎖，這是一種典型的相互等待造成的死鎖。

同時也可以觀察到，我們的語句是一個 adhoc 即時查詢，其外層也沒有 儲存過程 之類的包圍語句。

3. 尋找解決方案

知道了是什麼語句和什麼語句之間的衝突之後，後面的問題就比較簡單了，常見措施如下：

  • 使用 nolock 髒讀

由於衝突中涉及到了 S 鎖，其實絕大多數系統對髒讀不是特別敏感，所以使用 nolock 無鎖提示是一個好辦法。


BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees WITH(NOLOCK) WHERE EmployeeID=1;
ROLLBACK


BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WITH(NOLOCK) WHERE OrderID=10258
ROLLBACK
  • 使用 MVCC 多版本控制

現代化的關係型資料庫都支援 快照讀 來解決 並行讀寫 的衝突，同時又能保證不髒讀，簡而言之就是在事務修改時將修改前的資料存到 tempdb 中來形成欄位的版本化。

首先需要從 資料庫 級別開啟它。


ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON

然後在各自事務中顯式使用 SNAPSHOT 隔離級別查詢，參考sql如下：


-- 對談 65 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK

-- 對談 66 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees  WHERE EmployeeID=1;
ROLLBACK

三：總結

在真實的死鎖案例集錦中，相對來說 語句順序交錯 引發的死鎖會相對多一些，其次就是 書籤查詢，這個放到後面的文章中來聊，面對 語句順序交錯 的場景儘量的收集整理死鎖的 xml資料，或許有很多意想不到的發現。