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
设置索引重建计划任务
建议设置全局的索引重建任务, 每天晚上进行相关的索引重建
提高性能.