如何排查死锁原因及解决思路
eamonjxl(Q3):目前有一个表,主键为ID自增,数据量很小,更新的并发也不是特别高,Update的条件是表中的另外两个联合字段,但是经常会出现死锁情况,请问是否只有通过修改聚集索引的方式来处理呀,非常感谢。
石沫(A3):这是一个关于死锁的问题,为了能够提供帮助一些。请根据下列建议进行:
1. 跟踪死锁
2. 分析死锁链和原因
3. 一些解决办法
关于跟踪死锁,我们首先需要打开1222标记,例如DBCC TRACEON(1222,-1), 他将收集的信息写入到死锁事件发生的服务器上的日志文件中。同时建议打开Profiler的跟踪信息:
如果发生了死锁,需要分析死锁发生的根源在哪里?我们不是很清楚你的具体发生死锁的形态是怎么样的。
关于死锁的实例也多,这里不再举例。
这里只是提出一些可以解决的思路:
1. 减少锁的争用
2. 减少资源的访问数
3. 按照相同的时间顺序访问资源
减少锁的争用,可以从几个方面入手
1. 使用锁提示,比如为查询语句添加WITH (NOLOCK), 但这还取决于你的应用是否允许,大部分分布式的系统都是可以加WITH (NOLOCK), 金融行业可能需要慎重。
2. 调整隔离级别,使用MVCC,我们的数据库默认级别是READ COMMITED. 建议修改为读提交快照隔离级别,这样的话可以尽量读写不阻塞,只不过MVCC的ROW VERSION保存到TEMPDB下面,需要维护好TEMPDB。当然如果你的整个数据库隔离级别可以设置为READUNCOMMINTED,这些就不必了。
减少资源的访问数,可以从如下几个方面入手:
1. 使用聚集索引,非聚集INDEX的叶子页面与堆或者聚集INDEX的数据页面分离。因此,如果对非聚集INDEX 操作的话,会产生两个锁,一个是基本表,一个是非聚集INDEX。而聚集INDEX就不一样,聚集INDEX的叶子页面和表的数据页面相同,他只需要一个LOCK。
2. 查询语句尽量使用覆盖INDEX, 使用全覆盖INDEX,就不需要访问基本表。如果没有全覆盖,还会通过RID或者CLUSTER INDEX访问基本表,这样产生的LOCK可能会与其他SESSION争用。
按照相同的时间顺序访问资源:
确保每个事务按照相同的物理顺序访问资源。两个事务按照相同的物理顺序访问,第一个事务会获得资源上的锁而不会被第二个事务阻塞。第二个事务想获得第一个事务上的LOCK,但被第一个事务阻塞。这样的话就不会导致循环阻塞的情况。