SQL Server 2005(2008)遊標的行為變化導致的死鎖問題(轉載)

edwardking888發表於2011-08-25
轉載之:http://space.itpub.net/25175503/viewspace-704919
正文如下:

 產生死鎖的場景:

 

連線1:

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'select * from employee', 2, 2

EXEC sp_cursorfetch @cursor, 2, 0,1

select @cursor

--返回180150003

 

連線2:

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'select * from employee', 2, 2

EXEC sp_cursorfetch @cursor, 2, 0,1

select @cursor

 

現在連線2被連線1阻塞

接下來執行:

連線1:

EXEC sp_cursor 180150003, 33, 1, '', @ContactID=5 –這個語句實際上是做update

出現錯誤1205

連線2仍舊被連線1阻塞:

連線1正持有鍵上的U鎖,連線2在等待同一個鍵值上的U鎖。

 

 

我們來具體看一下遊標的幾個呼叫方式在資料庫上申請和釋放lock的過程

 

--建立一個dynamic遊標

DECLARE @cursor INT

EXEC sp_cursoropen @cursor OUTPUT, 'select * from employee', 2, 2

--提取下一行;這將行放到fetch buffer

EXEC sp_cursorfetch @cursor, 2, 0,1

--更新fetch buffer中的行

EXEC sp_cursor @cursor, 33, 1, '', @zip=5

--關閉遊標

EXEC sp_cursorclose @cursor

 

這裡我們介紹trace flag 1200來輸出語句在資料庫上對鎖申請和釋放的日誌:

DBCC traceon(1200,-1)  

關閉trace flag dbcc traceoff(1200,-1)

筆者補充以下資訊,在SQL SERVER 2008中記得在執行DBCC traceon(1200,-1)  前,先執行:
DBCC traceon(3604,-1);   --3604:將跟蹤結果輸出的螢幕

 

 

對於上面的語句,在2005版本中:

EXEC sp_cursorfetch @cursor, 2, 0,1

Process 53 acquiring S lock on RID: 6:1:60795:0 (class bit0 ref1) result: OK                

Process 53 acquiring U lock on RID: 6:1:60795:0 (class bit10000000 ref0) result: OK

 

EXEC sp_cursor @cursor, 33, 1, '', @zip=5

Process 53 acquiring U lock on RID: 6:1:60795:0 (class bit0 ref1) result: OK      

Process 53 acquiring X lock on RID: 6:1:60795:0 (class bit2000000 ref0) result: OK

Process 53 releasing lock reference on RID: 6:1:60795:0

 

2000版本:

EXEC sp_cursoropen @cursor OUTPUT, 'select * from employee', 2, 2

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit2000000 ref1) result: OK

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit10000000 ref0) result: OK

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit0 ref1) result: OK

 

EXEC sp_cursor @cursor, 33, 1, '', @zip=5

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit0 ref1) result: OK

Process 51 acquiring X lock on RID: 5:1:240:0 (class bit2000000 ref1) result: OK

Process 51 releasing lock reference on RID: 5:1:240:0

 

接下來,我們對上面現象進行一定的說明:

2005上,從cursor開啟,到執行update,整個lock的變化過程為:

 

S(共享鎖)à U(更新鎖)--&gtX(排他鎖)

Process 53 acquiring S lock on RID: 6:1:60795:0 (class bit0 ref1) result: OK

Process 53 acquiring U lock on RID: 6:1:60795:0 (class bit0 ref1) result: OK      

Process 53 acquiring X lock on RID: 6:1:60795:0 (class bit2000000 ref0) result: OK

 

而在2000上,從cursor開啟,到執行update,整個lock的變化過程為:

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit10000000 ref0) result: OK

Process 51 acquiring U lock on RID: 5:1:240:0 (class bit0 ref1) result: OK

Process 51 acquiring X lock on RID: 5:1:240:0 (class bit2000000 ref1) result: OK

 

1.在該錯誤重現中,滾動鎖定(scroll lock)在GetRow上提取S鎖,而後是U鎖,就導致了死鎖和錯誤結果的出現。

2.提取並沒有滾動鎖定基本的索引

 

正如上面那個簡單的錯誤重現,我們可以發現,2005 fetch過程中S鎖到U鎖的升級導致了deadlock的出現。對於S鎖,兩個連線可以在同一時間申請同一行資料上的S鎖,而當兩個連線都有對S鎖進行升級到U鎖的時候,一定會出現deadlock

 

SQL Server 2000版本中,fetch鎖定直接請求U鎖,這種情況下,就只有一個連線可以在同一時間對同一資料行上申請U鎖了。這樣直接申請U的行為,雖然避免了deadlock,但是會導致更多的lock和更長的lock持有時間,從而導致更多的阻塞出現。

 

這實際上是SQL Server 2005的一個設計變化,SQL Server2008也是如此。除了使用相應的引數保證兩個連線不要訪問同樣的資料,沒有解決該問題的方案。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-705775/,如需轉載,請註明出處,否則將追究法律責任。

相關文章