SqlServer死锁时使用nolock、option(force order)优化
前两天突然收到运维说某个项目经常死锁,客户反馈操作很卡,我们公司是做的wms(仓储物流管理)系统,卡起来无疑会导致客户的发货效率降低,所以我马上进到服务器检查了死锁情况,发现在下午2点到4点大概每10分钟一个死锁。
我检查是否是性能到瓶颈了导致死锁卡顿,但是并没有看到性能有太大压力,执行缓慢的语句拿出来重复执行并不缓慢,卡顿时也没执行其它耗时语句,就估计操作卡顿其实也是死锁造成资源等待引起的了。这样就把问题定位到死锁上了,查看死锁记录的信息,可以归类为两个死锁情况。
第一个:界面查询和过程种的update语句死锁了,这种就把查询改为nolock即可解决,后面内容都是讲第二个情况。
第二个:线程1执行以下语句(语句经过简化表达)
1,update T1 set filedvalue = '?'
2,waitfor delay '00:00:04.000' --延时4秒
3,update T2 set filedvalue = '?'
线程2执行以下语句(语句经过简化表达)
1,update a set a.filedvalue = '?' from T1 b right join T2 a on a.x=b.x where b.datetime < '?' and a.order_no = '???'
--执行计划如下
情况2的死锁信息如下
<resource-list> <pagelock fileid="1" pageid="12038033" dbid="5" objectname="T2" id="lock3e39c400" mode="IX" associatedObjectId="72057630639521792"> <owner-list> <owner id="线程一" mode="IX"/> </owner-list> <waiter-list> <waiter id="线程二" mode="S" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="11993674" dbid="5" objectname="T1" id="lockffffffffce34b100" mode="U" associatedObjectId="72057630639456256"> <owner-list> <owner id="线程二" mode="U"/> </owner-list> <waiter-list> <waiter id="线程一" mode="IX" requestType="wait"/> </waiter-list> </pagelock> </resource-list>
可以看出是线程一先拿到了T2的IX锁,然后线程二拿到了T1的U锁,此时线程一和线程二都无法拿到对方的锁。等待一段时间后就死锁了。
像这种死锁只要调整加锁顺序即可解决,例如,线程一先拿T1的锁再拿T2的锁,线程二也先拿T1的锁再拿T2的锁,当线程二拿不到T1的锁就会等待线程一先执行完,线程一的执行并不会遇到阻塞,这也就不会存在互相等待死锁了。
但是问题就在线程二的一条语句里面怎么设置占用锁的先后顺序,这里我想到了force order,然后做了实验(使用语句如上面的线程一、二的语句),发现确实可以。
操作方式:先执行线程一的语句,并且在1秒内(小于4秒的延时)执行线程二的语句。
对照如下:
1、没加force order的线程二的执行计划,线程二的锁资源监控是先锁T2再锁T1,执行结果出现死锁
2、加了force order的线程二的执行计划,线程二的锁资源监控是先锁T1再锁T2,执行结果没有死锁
注:版本为SQL Server 2008R2,事务级别是Read Committed
Read