sqlserver中select造成死锁

项目上线，准备验收前出现了一个严重的问题：很多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()