SQL Server 2005遊標的行為變化導致的死鎖問題

 產生死鎖的場景：

 

連線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)

 

 

對於上面的語句，在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/25175503/viewspace-704919/，如需轉載，請註明出處，否則將追究法律責任。