性能调优(处理 sql server 死锁)

        最近在做性能测试的时候发现程序在SQL Server下有很多死锁，于是进行了一些优化工作。尽管并无法解决所有问题，但是可喜的是性能得到了量级的提升。

        测试工具：winRunner
        测试环境：Windows 2003 server + windows xp + SQL Server
        用 户 数：100用户 ×10次   (混合测试)
        处理结果：优化前，测试前死锁现象严重，执行时长约为1小时15分钟。优化后，死锁问题基本解决，执行时长约为35分钟。效果明显

        产生死锁的主要原因包括三大类，处理方法也将给出：     
        (1)操作临时表产生死锁。
                 向临时表插入数据时产生死锁，这个问题很奇怪的，我在另一篇blog文章(SQL Server 临时表 与 Oracle 临时表)中已经阐述了SQL Server临时表的一些特性和后台处理方式。由于时间原因，最后没有深入研究产生这儿问题的原因。
        
        解决方法：
                drop掉临时表，重新创建即可。

        (2)更新与查询操作冲突。
                SQL Server默认的隔离级别不允许脏读。所以如果你要查询的内容正在被锁定更新时，你需要等待。如果之前有过更复杂的操作时，可能就会造成阻塞，进而引起超时，然后引发锁，然后会有语句被杀死，通常Select语句会被牺牲掉，也就是被杀死。(相应的内容，例如阻塞，锁升级 百度 或者看一下SQL Server的帮助，可以找到很多)
        
        解决方法：
                在select语句中适当的使用(nolock/readpast)关键字，允许脏读还是跳过锁定数据。当然了，允许脏读是要冒一定的风险的，所以在使用nolock前，要确认你真正理解它的意思。文章最后将列出关键字的说明。
        这个例子会有助于理解。在查询分析器中
        打开一个窗口1执行
        begin transaction work
        update sysobjects set name = name where name not like 'sys%';
        打开一个窗口2执行
        select * from sysobjects;  --在等待
        打开一个窗口3执行
        select * from(nolock) sysobjects;  --所有的数据都出来了
        打开一个窗口4执行
        select * from(readpast) sysobjects;  --除了sys开头的都出来了

        (3)索引不合理造成的冲突
                a)全表扫描。全表扫描会占用大量的资源。全表扫描会锁定大量的数据。这会造成执行时长过长，也会使死锁概率提高。使用substring,sum,count等函数时，常常造成无法匹配索引，从而造成全表扫描。

        解决方法：
                当然是避免使用以上函数，例如where条件中的substring可以考虑用like代替。统计sum,count可以在表名后加(nolock/readpast)关键字等

                b)没有索引可以使用。这个就不说了。新建一个索引就可以了。

 

附: Nolock 和 Readpast和Rowlock的区别

Nolock: 

不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT 语句。适合查询操作，或者判断资信的操作，保证数据是最大范围的。nolock关键字，不等待指定表的锁定，即允许脏读，所以，不要直接使用读出的数据进行更新操作，否则可能会造成数据偏差。可以多用在查询操作中。只在SQL Server下测试通过 (Sybase和Asa应该也可以，未验证)

 

Readpast:

跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。READPAST 锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于 SELECT 语句。适合记帐、核销前归集数据，保证数据是已经经过确认的。

 

Rowlock:

使用行级锁，而不使用粒度更粗的页级锁和表级锁。大量使用rowlock会影响系统性能。对于数据量大的语句会严重影响系统性能。适合在update和delete中处理，不建议在select中使用。如果我们在update语句的where条件中有主键时，系统将自动使用行级锁。

 

注意事项：

        仅在SQL Server中使用以上关键字。对于严格要求数据准确性的地方，要谨慎使用这几个关键字