目錄

• 一：背景
• 1. 講故事
• 二：死鎖簡析
• 1. 一個測試案例
• 2. 尋找死鎖源頭
• 3. 尋找解決方案
• 三：總結

一：背景

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 TRANUPDATE dbo.Employees SET Title="Dr." WHERE EmployeeID=1;WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Orders WHERE OrderID=10258ROLLBACK-- 會話 66 --BEGIN TRANUPDATE  dbo.Orders SET  ShipAddress="上海" WHERE OrderID=10258WAITFOR 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 TRANUPDATE  dbo.Orders SET  ShipAddress=&apos;上海&apos; WHERE OrderID=10258WAITFOR 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 TRANUPDATE dbo.Employees SET Title=&apos;Dr.&apos; WHERE EmployeeID=1;WAITFOR DELAY &apos;00:00:10&apos;SELECT * FROM dbo.Orders WHERE OrderID=10258ROLLBACK    </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 TRANUPDATE  dbo.Orders SET  ShipAddress="上海" WHERE OrderID=10258WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Employees WITH(NOLOCK) WHERE EmployeeID=1;ROLLBACKBEGIN TRANUPDATE dbo.Employees SET Title="Dr." WHERE EmployeeID=1;WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Orders WITH(NOLOCK) WHERE OrderID=10258ROLLBACK

使用 MVCC 多版本控制

現代化的關系型數據庫都支持 快照讀 來解決 并發讀寫 的沖突，同時又能保證不臟讀，簡而言之就是在事務修改時將修改前的數據存到 tempdb 中來形成字段的版本化。

首先需要從 數據庫 級別開啟它。

ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON  

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

-- 會話 65 --SET TRAN ISOLATION LEVEL SNAPSHOTBEGIN TRANUPDATE dbo.Employees SET Title="Dr." WHERE EmployeeID=1;WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Orders WHERE OrderID=10258ROLLBACK-- 會話 66 --SET TRAN ISOLATION LEVEL SNAPSHOTBEGIN TRANUPDATE  dbo.Orders SET  ShipAddress="上海" WHERE OrderID=10258WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Employees  WHERE EmployeeID=1;ROLLBACK

三：總結

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

到此這篇關于SQLSERVER 語句交錯引發的死鎖研究的文章就介紹到這了,更多相關sqlserver語句交錯引發的死鎖內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！