SQLSERVER語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決

這篇文章主要講解了“SQLSERVER語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“SQLSERVER語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決”吧！

阿克塞哈薩克族自治網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)建站,阿克塞哈薩克族自治網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為阿克塞哈薩克族自治上千提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)要多少錢(qián)，請(qǐng)找那個(gè)售后服務(wù)好的阿克塞哈薩克族自治做網(wǎng)站的公司定做！

一：背景

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.

二：死鎖簡(jiǎn)析

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

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

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

-- 會(huì)話 66 --
BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK

兩個(gè)會(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è)查詢，一旦出現(xiàn)死鎖，profile 就會(huì)成功捕獲，然后 copy 出 TextData 項(xiàng)，截圖如下：

 
  
   
    
     
unknown     
     
unknown     
    
    

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

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

雖然上面有圖形化表示，但在生產(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ì)話附加了 X 鎖，這是一種典型的相互等待造成的死鎖。

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

3. 尋找解決方案

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

使用 nolock 臟讀

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

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ù)庫(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í)別查詢，參考sql如下：

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

-- 會(huì)話 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語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)SQLSERVER語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

分享名稱：SQLSERVER語(yǔ)句交錯(cuò)引發(fā)的死鎖問(wèn)題怎么解決
URL標(biāo)題：http://m.jiaotiyi.com/article/pjhsii.html