您好,登錄后才能下訂單哦!
這篇文章主要講解了“SQLSERVER語句交錯引發(fā)的死鎖問題怎么解決”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“SQLSERVER語句交錯引發(fā)的死鎖問題怎么解決”吧!
相信大家在使用 SQLSERVER 的過程中經(jīng)常會遇到 阻塞
和 死鎖
,尤其是 死鎖
,比如下面的輸出:
(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.
開啟兩個會話 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 操作,稍等幾秒后就會出現(xiàn)死鎖。
當(dāng)我們的應(yīng)用程序拿到了這樣的輸出其實作用是不大的,要想溯源最好就是通過不斷的對 SQLSERVER 進行監(jiān)視來捕獲死鎖時的上下文信息,手段也有很多:
SQL Server Profile
DBCC TRACEON(1222)
DMV VIEW
這里我們就用第一種方式,一定要勾選 TextData
項,因為這里面會有死鎖上下文信息的xml表示,截圖如下:
將 profile 開啟后,重新執(zhí)行剛才的兩個查詢,一旦出現(xiàn)死鎖,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='上海' WHERE OrderID=10258 WAITFOR DELAY '00:00:10' 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='Dr.' WHERE EmployeeID=1; WAITFOR DELAY '00:00:10' 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>
雖然上面有圖形化表示,但在生產(chǎn)環(huán)境下參考價值并不多,因為這張圖蘊含的信息比較少,熟讀和整理 xml
的內(nèi)容就非常必要了,截圖如下:
仔細(xì)觀察上面的這張圖可以清晰的看到,spid=66
持有了 Orders.PK_Orders
索引上哈希碼為 59ce0997f9b8
鍵值的 X 鎖,之后需要再次獲取 Employees.PK_Employees
索引上哈希碼為 8194443284a0
鍵值上的 S 鎖,很不巧的是,此時的 Employees.PK_Employees
索引上哈希碼為 8194443284a0
的鍵值已經(jīng)被 spid=65 的會話附加了 X 鎖,這是一種典型的相互等待造成的死鎖。
同時也可以觀察到,我們的語句是一個 adhoc 即時查詢,其外層也沒有 存儲過程
之類的包圍語句。
知道了是什么語句和什么語句之間的沖突之后,后面的問題就比較簡單了,常見措施如下:
使用 nolock 臟讀
由于沖突中涉及到了 S 鎖,其實絕大多數(shù)系統(tǒng)對臟讀不是特別敏感,所以使用 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 多版本控制
現(xiàn)代化的關(guān)系型數(shù)據(jù)庫都支持 快照讀
來解決 并發(fā)讀寫
的沖突,同時又能保證不臟讀,簡而言之就是在事務(wù)修改時將修改前的數(shù)據(jù)存到 tempdb
中來形成字段的版本化。
首先需要從 數(shù)據(jù)庫
級別開啟它。
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON
然后在各自事務(wù)中顯式使用 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
感謝各位的閱讀,以上就是“SQLSERVER語句交錯引發(fā)的死鎖問題怎么解決”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對SQLSERVER語句交錯引發(fā)的死鎖問題怎么解決這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關(guān)知識點的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。