SQLServer解决deadlock问题的一个场景

背景

公司产品出现过很多次dead lock
跟研发讨论了很久, 都没有具体的解决思路
但是这边知道了一个SQLServer数据库上面计划100%出现问题的场景
然后想着跟之前微软case一起处理一下 看能否解决这个问题. 

整体思路

1. 修改默认的隔离级别
2. 关闭索引上面的页锁,只留下行数, 避免锁升级到页锁,导致问题
3. 使用profiler的方式,跟着你干出来deadlock 对应的资源, 查看资源并且进行优化. 

第一步修改隔离级别

SQLSERVER 默认的是 Read Commited 的隔离级别.
大部分高并发场景都建议执行一下修改, 改为快照级别, 避免出现阻塞
方式方法为: 
查看:
SELECT is_read_committed_snapshot_on FROM sys.databases 
WHERE name= 'YourDatabase'
或者是:
DBCC USEROPTIONS

修改的方法为:
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE dbname SET MULTI_USER

注意可以online 修改 可能速度比较慢, 建议在停机时间时执行处理

快照隔离会给每一行增加一个版本号, 实现类似于MVCC的效果,提高并发度,但是会导致数据量使用的上升

关闭所以上面的页级别锁

注意这个思路是 研发同事告知 微软case 时给出的方案
我这边并不是非常建议关闭默认值
但是可以作为一个优化项目使用. 

执行方法比较简单, 下面的SQL执行出来的结果 另外开一个分析窗口执行就可以了.
速度比较快几乎瞬间完成. 

SELECT
	'ALTER INDEX ' + i.NAME + ' ON  yourdatabase.' + t.NAME + '  SET (ALLOW_PAGE_LOCKS=OFF,ALLOW_ROW_LOCKS=ON) ;' 
FROM
	sys.objects t
	INNER JOIN sys.indexes i ON t.object_id = i.object_id CROSS APPLY (
	SELECT
		col.[ NAME ] + ', ' 
	FROM
		sys.index_columns ic
		INNER JOIN sys.COLUMNS col ON ic.object_id = col.object_id 
		AND ic.column_id = col.column_id 
	WHERE
		ic.object_id = t.object_id 
		AND ic.index_id = i.index_id 
	ORDER BY
		col.column_id FOR XML PATH ( '' ) 
	) D ( column_names ) 
WHERE
	t.is_ms_shipped <> 1 
	AND t.type = 'U' 
	AND index_id > 0 
ORDER BY
	i.[ NAME ]

使用profiler 跟踪出具体的死锁信息,进行针对性的优化

Profiler 可以跟踪到具体的被锁的资源信息. 

可以通过修改profiler的配置项目就可以了. 

跟踪属性-常规-使用模板里面选择 TSQL_Locks 
跟踪属性-事件选择-仅选择 Locks -> Deadlock graph
执行跟踪就可以了. 

就能够看到具体的被死锁的信息.

然后可以根据被锁死的信息, 适当的进行优化处理一下. 

profiler设置1

profiler设置2

profiler设置3

进行索引重建

alter index PK__lsrwztlo__CCDA21518AF0ADA3 on yourdatabase.lsrwztlog rebuild 
alter index PK__pfhiacti__3213E83F88237CAF  on yourdatabase.pfhiactinst rebuild
alter index index_procintsid  on yourdatabase.pfhiactinst rebuild

设置索引重建计划任务

建议设置全局的索引重建任务, 每天晚上进行相关的索引重建
提高性能.