SQLServer阻塞、死锁和最⼤并⾏度
1.阻塞
阻塞:是指当⼀个数据库会话中的事务,正在锁定其他会话事务想要读取或修改的资源,造成这些会话发出的请求进⼊等待的状态。SQL Server 默认会让被阻塞的请求⽆限期地⼀直等待,直到原来的事务释放相关的锁,或直到它超时 (根据 SET LOCK_TIMEOUT参数)、进程被杀死、服务器关闭。⼀般系统中,偶尔有短时间的阻塞是正常且合理的;但若设计不良的程序,就可能导致长时间的阻塞,这样就不必要地锁定了资源,⽽且阻塞了其他会话欲读取或更新的需求,系统表现为很卡。
项⽬中平时遇到的⼤多数是阻塞问题,⽐如系统页⾯报超时错误、SSMS中执⾏⼀条原本很快的SQL却卡住执⾏不了或需要执⾏很久、甚⾄整个数据库处于瘫痪卡死状态(严重情况)等。
阻塞的原因有很多,这⾥简单罗列下:文员职位描述
1.并发执⾏⼤量表扫描的SQL,由于缺失索引,造成⼤量阻塞,这种现象⾮常常见,例如⽹站⾸页中某个模块加载的SQL中缺失索引,导致⽹站⾸页访问超时。
2.隔离级别的问题,SQL Server默认隔离级别为READ COMMITTED,级别越⾼,并发性越差,造成阻塞的⼏率也越⾼。
3.长时间运⾏的SQL或者存储过程,由于执⾏效率较低或内容较多,事务期间会阻塞住其他会话执⾏
2.死锁
死锁:是指多个事务会话互相等待对⽅释放持有的锁⽽造成的环路,如果没有外部⼲预,死锁将⽆限期持续下去。Sqlrver 中默认产⽣死锁时,会根据事务开销选择终⽌并回滚其中某⼀个事务,让其他事务获取所需的锁完成事务提交。如果系统出现⼤量死锁,会导致⽤户使⽤过程中出现⼤量死锁报错页⾯,事务数据⽆法正常提交,影响系统使⽤。
死锁发⽣的现象往往是很明显的,页⾯会出现报错,如下图
3.阻塞与死锁区别
阻塞现象:系统访问超时、卡慢
死锁现象:系统访问报错
大型超市有哪些
死锁其实也是由于阻塞造成的,会话之间先是阻塞,然后⼜因为产⽣交叉访问,造成环路,最终死锁。死锁的发⽣其实和阻塞是类似的,只不过阻塞是⼀种串⾏的等待,⽽死锁是交叉环路。
等待⽅式:默认情况下,当数据库出现阻塞时,如果没有⼈⼯⼲预,那么该会话会⼀直阻塞下去,直到阻塞该会话的事务结束,或直到LOCK_TIMEOUT设置的超时等待时间(默认为0即⽆限); ⽽当出现死锁时,数据库会⽴即根据事务的回滚开销,来选择终⽌(回滚)其中⼀个事务,进⽽解除死锁,所以死锁会造成操作⽆效。
4.排查维护空调压缩机原理
之前在公司平台发布栏⽬⾥发布了SQLServer 性能排查指南,可以下载参阅,下⾯简单说下操作。
4.1 阻塞处理
当系统突然发⽣了超时或卡顿等情况,可以先使⽤下⾯的脚本查看数据库阻塞情况,正常情况下没有结果或者出现少量短时间阻塞,问题发⽣时,通常会出现⼤量长时间阻塞,可以根据显⽰的BlockingSession_TSQL进⾏查看是SQL性能问题还是程序问题,必要时可以选择kill 掉阻塞的会话来解决。
SELECT R.ssion_id AS WaitingSessionID ,
S.ssion_id AS BlockingSessionID ,
大学军训感悟< AS WaitingSession_TSQL ,
< AS BlockingSession_TSQL ,
A.wait_duration_ms as WAIT_DURATION_MS ,
S.program_name AS BlockingSession_ApplicationName ,
S.host_name AS BlockingSession_HostName
从军行其二王昌龄FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_ssions AS S ON R.blocking_ssion_id = S.ssion_id
INNER JOIN sys.dm_exec_connections AS C1 ON R.ssion_id = C1.most_recent_ssion_id
INNER JOIN sys.dm_exec_connections AS C2 ON S.ssion_id = C2.most_recent_ssion_id
祛湿药材
INNER JOIN sys.dm_os_waiting_tasks AS A ON A.ssion_id = C1.most_recent_ssion_id
CROSS APPLY sys.dm_exec_sql_st_recent_sql_handle) AS Q1
CROSS APPLY sys.dm_exec_sql_st_recent_sql_handle) AS Q2
⽰例:
4.2 死锁处理
当系统中出现死锁时,可以使⽤SQl Profile进⾏死锁监控(具体⽅法参考SQLServer 性能排查指南),捕捉到死锁后进⾏分析,看是否是由于SQL缺失索引导致。
5.最⼤并⾏度
SQL Server 通过设置最⼤并⾏度来限制并⾏计划执⾏时所⽤的处理器数,默认为0,即不限制,由SQL Server⾃⼰控制并⾏。
心律不齐怎么办
并⾏开销阈值:指定SQLServer创建和运⾏并⾏查询计划的阈值,仅当运⾏同⼀查询的串⾏计划的估计开销⾼于在“并⾏的开销阈值”中设置的值时,SQLServer才创建和运⾏该查询的并⾏计划。开销指的是在特定硬件配置中运⾏串⾏计划估计需要花费的时间(秒)。
我们的业务系统⼀般都是为OLTP,理想情况下事务很短,这时候其实就没必要通过并⾏来提⾼运⾏速度,所以对于这类系统来说,可以把最⼤并⾏度设为1,也就是不适⽤并⾏操作,从⽽减少不必要的资源等待。
如果是OLAP,由于事务普遍较长,所以并⾏操作往往能提⾼速度和资源利⽤率。
个⼈观点:对于最⼤并⾏度,项⽬上出现过⼏次该问题,导致系统访问超时,修改为1也就是关闭最⼤并⾏度后,问题解决。对于我们公司的系统来说,⼤部分事务都很短,不需要并⾏,⼩部分较长的事务可以改写SQL来使⽤并⾏; 另外也可以调整并⾏阈值,同时设置并⾏度为0或是固定值;
比萨的做法SELECT * FROM TEST WHERE OBJECT_ID =1 OPTION (MAXDOP 8)
没有最好的⽅法,只有最适合的⽅法