最近在做性能测试的时候发现程序在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)没有索引可以使用。这个就不说了。新建一个索引就可以了。