目錄

• 一：背景
• 1. 講故事
• 二：死鎖簡(jiǎn)析
• 1. 一個(gè)測(cè)試案例
• 2. 尋找死鎖源頭
• 3. 尋找解決方案
• 三：總結(jié)

一：背景

1. 講故事

相信大家在使用 SQLSERVER 的過(guò)程中經(jīng)常會(huì)遇到 阻塞 和 死鎖，尤其是 死鎖，比如下面的輸出：

(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.

要解決死鎖問(wèn)題，個(gè)人感覺(jué)需要非常熟知各種隔離級(jí)別，尤其是 可提交讀 模式下的 CURD 加解鎖過(guò)程，這一篇我們就來(lái)好好聊一聊。

二：死鎖簡(jiǎn)析

1. 一個(gè)測(cè)試案例

開(kāi)啟兩個(gè)會(huì)話(huà) 65 和 66 ，分別使用如下查詢(xún)。

-- 會(huì)話(huà) 65 --BEGIN TRANUPDATE dbo.Employees SET Title="Dr." WHERE EmployeeID=1;WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Orders WHERE OrderID=10258ROLLBACK-- 會(huì)話(huà) 66 --BEGIN TRANUPDATE  dbo.Orders SET  ShipAddress="上海" WHERE OrderID=10258WAITFOR DELAY "00:00:10"SELECT * FROM dbo.Employees WHERE EmployeeID=1;ROLLBACK

兩個(gè)會(huì)話(huà)非常簡(jiǎn)單，交錯(cuò)的對(duì) Employees 和 Orders 進(jìn)行 SELECT 和 UPDATE 操作，稍等幾秒后就會(huì)出現(xiàn)死鎖。

2. 尋找死鎖源頭

當(dāng)我們的應(yīng)用程序拿到了這樣的輸出其實(shí)作用是不大的，要想溯源最好就是通過(guò)不斷的對(duì) SQLSERVER 進(jìn)行監(jiān)視來(lái)捕獲死鎖時(shí)的上下文信息，手段也有很多：

• SQL Server Profile
• DBCC TRACEON(1222)
• DMV VIEW

這里我們就用第一種方式，一定要勾選 TextData 項(xiàng)，因?yàn)檫@里面會(huì)有死鎖上下文信息的xml表示，截圖如下：

將 profile 開(kāi)啟后，重新執(zhí)行剛才的兩個(gè)查詢(xún)，一旦出現(xiàn)死鎖，profile 就會(huì)成功捕獲，然后 copy 出 TextData 項(xiàng)，截圖如下：

<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>

雖然上面有圖形化表示，但在生產(chǎn)環(huán)境下參考價(jià)值并不多，因?yàn)檫@張圖蘊(yùn)含的信息比較少，熟讀和整理 xml 的內(nèi)容就非常必要了，截圖如下：

仔細(xì)觀察上面的這張圖可以清晰的看到，spid=66 持有了 Orders.PK_Orders 索引上哈希碼為 59ce0997f9b8 鍵值的 X 鎖，之后需要再次獲取 Employees.PK_Employees 索引上哈希碼為 8194443284a0 鍵值上的 S 鎖，很不巧的是，此時(shí)的 Employees.PK_Employees 索引上哈希碼為 8194443284a0 的鍵值已經(jīng)被 spid=65 的會(huì)話(huà)附加了 X 鎖，這是一種典型的相互等待造成的死鎖。

同時(shí)也可以觀察到，我們的語(yǔ)句是一個(gè) adhoc 即時(shí)查詢(xún)，其外層也沒(méi)有 存儲(chǔ)過(guò)程 之類(lèi)的包圍語(yǔ)句。

3. 尋找解決方案

知道了是什么語(yǔ)句和什么語(yǔ)句之間的沖突之后，后面的問(wèn)題就比較簡(jiǎn)單了，常見(jiàn)措施如下：

使用 nolock 臟讀

由于沖突中涉及到了 S 鎖，其實(shí)絕大多數(shù)系統(tǒng)對(duì)臟讀不是特別敏感，所以使用 nolock 無(wú)鎖提示是一個(gè)好辦法。

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 多版本控制

現(xiàn)代化的關(guān)系型數(shù)據(jù)庫(kù)都支持 快照讀 來(lái)解決 并發(fā)讀寫(xiě) 的沖突，同時(shí)又能保證不臟讀，簡(jiǎn)而言之就是在事務(wù)修改時(shí)將修改前的數(shù)據(jù)存到 tempdb 中來(lái)形成字段的版本化。

首先需要從 數(shù)據(jù)庫(kù) 級(jí)別開(kāi)啟它。

ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON  

然后在各自事務(wù)中顯式使用 SNAPSHOT 隔離級(jí)別查詢(xún)，參考sql如下：

-- 會(huì)話(huà) 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-- 會(huì)話(huà) 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

三：總結(jié)

在真實(shí)的死鎖案例集錦中，相對(duì)來(lái)說(shuō) 語(yǔ)句順序交錯(cuò) 引發(fā)的死鎖會(huì)相對(duì)多一些，其次就是 書(shū)簽查找，這個(gè)放到后面的文章中來(lái)聊，面對(duì) 語(yǔ)句順序交錯(cuò) 的場(chǎng)景盡量的收集整理死鎖的 xml數(shù)據(jù)，或許有很多意想不到的發(fā)現(xiàn)。

到此這篇關(guān)于SQLSERVER 語(yǔ)句交錯(cuò)引發(fā)的死鎖研究的文章就介紹到這了,更多相關(guān)sqlserver語(yǔ)句交錯(cuò)引發(fā)的死鎖內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！