Freigeben über

SQL Server 2005游标的行为变化导致的死锁问题

  • Artikel

 

产生死锁的场景：

 

连接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（更新锁）-->X（排他锁）

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也是如此。除了使用相应的参数保证两个连接不要访问同样的数据，没有解决该问题的方案。

Comments