Select语句也会引起死锁

作者:cow-man 和mssql相关  

  项目上线,准备验收前出现了一个严重的问题:很多select语句作为死锁的牺牲,大部分报表无法打开。这个问题影响范围很大所有的报表都无法访问,而我们的报表是放在电视上面轮播的,电视放在工厂里面,所以出现问题后,整个工厂都知道了。

  解决这个问题比较曲折,首先是写SAP接口的同事发现了问题:SAP一直在传错误数据导致产量表被锁住。修改SAP传输的错误数据后,这个死锁的问题没有出现了。但是我查看生产环境服务器日志的时候,发现这个问题依然存在,由于客户没有提这个问题,我也就是没有理由要求花时间修改了,因为我还有其他项目在忙。

  问题始终存在,它的暴露只是时间问题。过了一周,在我们要谈项目验收的时候,这个问题又暴露出来了。因为影响非常大,给客户造成不好的印象。所以修改这个问题得到了老板的支持,说实话,我也头一次遇到这样的问题,也想不通select语句怎么就死锁了。我知道这个问题很头疼,好在得到老板的支持,会给足够的时候我来解决问题,我也就有信心了。

  在网上找了很多文章,我的解决思路是:通过查询语句查找死锁相关的sql语句,只发现了被牺牲的那条sql语句,另外一条sql语句没有找到,这条路走不通。接着就是重现问题,然后解决问题。这条路刚开始也走不通,也行不通,重现了一个下午有没有发现问题。在快下班的时候,我迷茫了。第二天又网上找了半天资料,这个时候想到用sql server profiler去监听数据库,悲剧的是客户那边放假了,连不到出问题的生产环境数据库。接着跟同事聊这个问题,他给的建议依然是重现,然后记得有个方法可以重现,这个时候我才猛然想起来了有这档子事。

  重现方法是有sql语句循环往产量表插入数据,由于报表大多读取产量表,然后报表就经常死锁。这个时候我看到希望了,只是不理解select语句怎么会引起死锁,后来在网上认真读了一篇文章:SqlServer中select语句引起的死锁(http://www.csharpwin.com/csharpspace/11505r288.shtml),读懂这篇文章后我觉得跟我遇到的情况非常相似。

  通过他的理论我分析了下死锁过程:

  • select语句使用非聚族索引查询产量信息,会对非聚族索引添加共享锁,由于非聚族索引上没有select的全部数据列,(所以会有书签查找出现,)需要查询产量表。查询产量表时,需要对产量表数据添加共享锁,需要等待Update语句更新完产量表后释放排他锁。即Select等待Update释放锁。
  • 此时产量表上的Update/Insert语句更新产量信息的时候,会在聚族索引上做定位,添加排他锁和修改非聚族索引的信息,问题就出在修改非聚族索引信息的时候,需要对非聚族做索引添加排他锁。此时select语句已经在聚族索引上面添加了共享锁,需要释放后才能被添加排他锁。即update语句需要等待select语句是否锁。
  • 这样死锁就形成了。
 
于是只要让查询语句加共享锁就解决问题了,sql server行版本级别控制能解决我的问题。

使用基于行版本控制的隔离级别:当在基于行版本控制的隔离下运行的事务读取数据时,读取操作不会获取正被读取的数据上的共享锁(S 锁)

找到最快设置行版本级别的方法:
 

if(charindex('Microsoft SQL Server 2008',@@version) > 0) 
begin 

declare @sql varchar(8000) 
select @sql = ' 
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; 
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;' 

Exec(@sql) 
end 

很神奇,这样设置后,死锁的问题就不存在了。

 

查询是否设置成功:
select is_read_committed_snapshot_on from sys.databases where name = DB_Name()  

相关资料:

Select语句也会引起死锁来源网络,如有侵权请告知,即处理!

编程Tags: