ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 2005(2008)游标的行为变化导致的死锁问题(转载)

SQL Server 2005(2008)游标的行为变化导致的死锁问题(转载)

原创 Linux操作系统 作者:edwardking888 时间:2011-08-25 13:46:08 0 删除 编辑
转载之: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(更新锁)-->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也是如此。除了使用相应的参数保证两个连接不要访问同样的数据,没有解决该问题的方案。



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8183550/viewspace-705775/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1466433