文档章节

需要我们了解的SQL Server阻塞原因与解决方法 - sym_cn

鱼煎
 鱼煎
发布于 2016/05/07 00:14
字数 4938
阅读 72
收藏 0
点赞 2
评论 0

这里通过连接在sysprocesses里字段值的组合来分析阻塞源头,可以把阻塞分为以下5种常见的类型(见表)。waittype,open_tran,status,都是sysprocesses里的值,“自我修复?”列的意思,就是指阻塞能不能自动消失。

 5种常见的阻塞类型

类型 waittype open_tran status 自我修复 原因/其他特征
1 不为0 >=0 runnable 是的,当语句运行结束后 语句运行的时间比较长,运行时需等待某些系统资源(如硬盘读写、CPU或内存等)。
2 0x0000 >0 sleeping 不能,但是如果运行 KILL语句,这个链接能够很容易被终止 可能客户端遇到了一个语句执行超时,或者主动取消了上一语句的执行,但是没有回滚开启的事务,在SQL Trace里能够看到一个Attention事件
3 0x0000
0x0800
0x0063
>=0 runnable 不能。知道客户端吧所有结果都主动取走,或者主动断开连接,可以运行KILL语句去终止它,但是可能要花长达30秒 客户端没有及时把所有结果都取走,这时可能open_tran=0,事务隔离级别也为默认(READ COMMITTED),但这个连接还会持有锁资源
4 0x0000 >0 rollback 是的 在SQL Trace里能够看到这个SPID已经发来了一个Attention事件,说明客户端已经遇到了超时,或者主动要求回滚事务
5 各种值都有可能 >=0 runnable 不能,直到客户端取消语句运行或者主动断开连接。可以运行KILL语句终止它,但是可能要花长达30秒 应用程序运行中产生死锁,在SQL Server中以阻塞形式体现。Sysprocesses里阻塞和被阻塞的连接hostname值是一样的

下面详细介绍这些类型产生的原因,以及解决方法

类型1:由于语句运行时间太长而导致的阻塞,语句本身在正常运行中,只须等待某些系统资源。

解决方法:

要解决这一类阻塞,数据库管理员需要和数据库应用设计人员合作,共同解决以下问题。

  1. 语句本身有没有可优化的空间? 
    这里包括修改语句本身降低复杂度、修改表格设计、调整索引等。
  2. SQL Server整体性能如何?是不是有资源瓶颈影响了语句执行速度? 
    当SQL Server 遇到诸如内存、硬盘读写、CPU等资源瓶颈是,原来能很快完成的语句有可能会花很长时间。
  3. 如果语句天生就很复杂,无法调优(很多处理报表的语句就是这样),就须考虑怎样把这一类应用(一般就是数据仓库应用)从OLTP系统中隔离出来。
类型2:由于一个未按预期提交的事务导致的阻塞

这一类阻塞的特征,就是问题连接早就进入了空闲状态(sysprocesses.status=’sleeping’和sysprocesses.cmd=’AWAITING COMMAND’),但是,如果检查sysprocesses.open_tran,就会发现它不为0,以及事务没有提交。这类问题很多都是因为应用端遇到一个执行超时,或者其他原因,当时执行的语句被提前终止了,但是连接还保留着。应用没有跟随发来的事务提交或回滚指令,导致一个事务被遗留在SQL Server里。

遇到这类问题,许多使用者会误以为是SQL Server端什么地方没有处理好。其实,执行超时(command timeout)完全是一个客户端的行为。当客户端应用向SQL Server发来语句执行请求时,自己会有一个执行超时设置。一般ADO或ADO.NET的连接超时时限是30秒。如果30秒以内SQL Server没有完成语句返回任何结果,客户端就会发送一个Attention的消息给SQL Server,告诉SQL Server它不想继续等下去了。SQL Server收到这个消息后,会终止当前正在运行的语句(或批处理)。但是,为了维护客户端的逻辑,SQL Server默认不会自动回滚或提交这个连接已经打开的事务,而是等待客户端的后续决定。如果客户端不发来回滚或提交指令,SQL Server会永远的把这个事务保持下去,直到客户端断开连接为止。

这里可以用下面这个实验来模拟这个问题。在Management Studio里创建一个连接到SQL Server,运行下面的批处理语句:

use sqlnexus go BEGIN TRAN SELECT * FROM ReadTrace.tblInterestingEvents WITH(HOLDLOCK) SELECT * FROM sysobjects s1,sysobjects s2 COMMIT TRAN

由于使用了HOLDLOCK参数,第一句SELECT会在运行结束后,在表格上维持一个TAB的S锁。如果批处理全部完成,这个锁会在提交事务的时候释放。但是第二句的SELECT会执行很久。请在等待3~4秒钟以后取消执行。然后运行下面的语句,检查open_tran和锁的情况。

SELECT @@TRANCOUNT GO sp_lock GO

通过结果(见图)可以得知:

(1)     批处理被取消的时候,“COMMIT TRAN”这条语句没有被执行到。SQL Server没有对“BEGIN TRAN”开启的那个事务做任何处理,只保持其活动的状态。

(2)     第一句SELECT带来的锁由于事务没有结束,所以锁还保持着(objID=85575343, Type=TAB, Mode=IS)。

现在,如果有其他连接要修改ReadTrace.tblInterestingEvents这张表,就会被阻塞住。

解决办法:

1. 应用程序本身必须意识到审核语句都有可能遇到意外终止情况,做好错误处理工作。这些工作包括

a)   在做SQL Server调用的时候,必须加上错误捕捉和处理语句

SQL Server客户端驱动程序(包括ODBC和OLE DB)当语句执行遇到意外终止(包括超时)的时候,都会向应用返回错误信息。客户端在捕捉到错误信息时。除了做记录以外(这对问题定位非常有帮助),还要运行下面这句话,把没有提交的事务回滚掉。

IF @@TRANCOUNT>0 ROLLBACK TRAN

有些程序员会问,我在T-SQL批处理里已经写了T-SQL层面的错误捕捉和处理语句(IF @@ERROR<>0 ROLLBACK TRAN),还有必要让应用程序再做一遍么?需要意识到的是,有些异常(比如超时)终止的是整个T-SQL批处理的执行,而不仅仅是当前语句。所以当这些异常发生的时候,T-SQL层面错误捕捉和处理语句很可能也一起被取消了。它们不能发挥想象中的作用。在应用程序里的错误捕捉和处理语句是必不可少的。

b)   设置连接属性“SET SACT_ABORT ON”

当SET SACT_ABORT为ON时,如果执行T-SQL语句产生运行错误,整个事务将会终止并回滚

当SET SACT_ABORT为OFF时,处理方法不是唯一的。有时只回滚产生错误的T-SQL语句,而事务将继续进行处理。如果错误很严重,及时SET SACT_ABORT 为OFF,也可能回滚整个事务。OFF是默认设置。

如果没有办法很快规范应用程序的错误捕捉和处理语句,一个最快的方法就是在每个连接建立以后,或者是容易出问题的存储过程的开头,运行“SET XACT_ABORT ON”,让SQL Server帮助应用程序回滚事务。

c)   考虑是否要关闭连接池

一般的SQL Server应用都会使用连接池来得到良好的性能。如果有一个连接忘记把事务关闭就推出连接,那么这个连接会被交还给连接池,但是这个时候事务不会被清理。客户端驱动程序会在这个连接下一次被重用的时候(又有新的用户要建立连接),发一句sp_reset_connection命令清理当前连接上次遗留下来的所有对象,包括回滚未提交的事务。如果连接交还给连接池以后很久都没有被重用,那它的事务就会持续长时间,引起阻塞。有些Java程序使用的驱动程序,提供连接池功能,但是不提供连接重用时的事务清理功能。这样的连接池对应用开发质量要求很高,比较容易发生阻塞。

    如果不能很快的实施建议a)和b),把连接池关闭能缩短食事务持续时间,也能从一定程度上缓解阻塞问题。

2. 分析为什么连接会遇到异常终止

这里又得谈到错误信息记录了。有了错误信息,就可以判定是超时问题,还是其他SQL Server错误。如果是超时问题,可按照第一种阻塞进行处理。

    还有一种孤儿事务的来源,是连接开启了隐式事务(implicit transaction)而没有加入及时提交事务的机制。如果连接处于隐式事务模式(SET IMPLICIT_TRANSACTIONS ON),并且连接当前不再事务中,则执行下列任何一条语句都会开启一个新的事务。

ALTER TABLE FETCH REVOKE
CREATE GRANT SELECT
DELETE INSERT TRUNCATE_TABLE
DROP OPEN UPDATE

对于因为此设置为ON而自动打开的事务,SQL Server会自动帮你打开事务,但是不会自动帮你提交。用户必须在该事务结束后将其显式提交或回滚。否则,当用户断开连接时,事务及其包含的所有数据更改将被回滚。事务提交后,执行上述任意一条语句又会启动一个新事务。隐式事务模式将始终生效,知道连接执行SET IMPLICIT_TRANSACTIONS OFF语句使连接恢复为自动提交模式。在自动提交模式下,所有单个语句在成功完成时将被提交,不会有事务遗留。

为什么会有连接要开启隐式事务呢?除了程序员有意为之以外,很多是客户端数据库连接驱动,或者空间为了实现它的事务功能(注意不是SQL Server通过T-SQL语句直接提供的)而选用这个机制。如果应用程序出现意外,或者脚本没有处理好,会有应用层事务未提交的现象。在SQL Server里也体现为一个孤儿事务。严格约束应用层对事务的使用,直接使用SQL Server里面的事务,是避免这种问题出现的好方法。

类型3:由于客户端没有及时把结果集取出而导致的语句长时间运行。

         语句在SQL Server内执行总时间不仅包含SQL Server的执行时间,还包含把结果集发给客户端的时间。如果结果集比较大,SQL Server会分几次打包发出,每发一次,都要等待客户端的确认。只有确认以后,SQL Server才会发送下一个结果集包。所有结果都发完以后,SQL Server才认为语句执行完毕,释放执行申请的资源(包括锁资源)。

         如果处于某种原因,客户端应用处理结果非常缓慢甚至没有相应,或者干脆不理睬SQL Server发送结果集的请求,则SQL Server会耐心的等待,因此会导致语句长时间执行而发生阻塞。

解决方法:

  1. 在设计程序时,一定要慎重返回大结果集。这种行为不仅会对SQL Server和网络带来很大负担,对应用程序本身来讲,也要花很多资源去处理结果集。如果最终用户只需要部分结果集就可以,则在发送SQL Server指令的时候就要指定好。要避免居于不管三七二十一所有数据都要,而结果集只取走开头一部分去展示这样的行为发生。
  2. 如果应用程序的确须返回大结果集,例如一些报表系统,则要考虑报表数据库和生产数据库分开。
  3. 如果1和2在短期内不能实现,可以和最终用户协商,返回大结果集的连接使用READ UNCOMMITTED事务隔离级别。这样查询语句就不会申请S锁了。
类型4:阻塞的源头连接一直处于rollback状态。

这种情况常是由第一类情况衍生来的。有时候数据库管理员发现一个连接阻塞住了别人,为了解决问题,会让连接主动退出或强制退出(轻质退出应用,或者直接在SQL Server端KILL连接)。对于大部分情况,这些措施会消除阻塞。但是要记住的是,不管是在客户端退出,还是要服务器端KILL,为了维护数据库事务的一致性,SQL Server都会对连接还没有来得及完成提交的事务做回滚动作。SQL Server要找到所有当前事务修改过的记录,把它们改回原来的状态。所以,如果一个DELETE、INSERT或UPDATE已经运行了一个小时,可能回滚也需要一个小时,在这个过程中,阻塞还会延续,我们只能等待。

有些用户可能等不及,直接重启SQL Server。当SQL Server关闭的时候,回滚动作会被中断,SQL Server会被很快关掉,但是这个回滚动作在下次SQL Server重启的时候会重新开始(数据库做恢复的时候)。重启的时候如果回滚不能很快结束,整个数据库都不可用,可能会带来更严重的后果。

解决方法:

最好的方法是在工作时间尽量不要做这种大的修改操作。这些操作尽量安排在半夜或者周末的时间完成。如果操作已经做了很久,最好耐心等它做完。如果一定要在有工作负荷的时候做,最好把一个大操作分成若干小操作分步完成。

类型5:应用程序运行中产生死锁,在SQL Server中以阻塞形式体现。

一个客户端的应用在运行过程中会使用到许多资源,包括线程资源,信号量资源,内存资源,IO资源等,SQL Server也是资源之一。如果发生死锁的两端不全是SQL Server,SQL Server的死锁判断机制可能不起作用。这时如果应用端没有处理好,可能会永远等下去。而SQL Server内部的表现可能仅仅是一个阻塞。但是这个阻塞不会自动消除。这样的阻塞对SQL Server的性能会产生很大影响。

下面我们举两个这种应用端死锁的例子。

1)  在应用的一个线程中开启不止一个数据库连接而产生的死锁(见图)。

假设应用有一个线程有这样的逻辑:

    ●  开始运行

    ●  建立数据库连接A,调用存储过程ProcA。打开结果集A。

    ●  建立数据库连接B,调用存储过程ProcB。打开结果集B。

    ●  轮流读取结果集A、B,整合输出最终结果。

    ●  关闭结果集A、B,关机连接A、B。

    ●  结束运行

在正常情况下这样的设计看上去没有问题,但是实际上很脆弱。因为在线程内部,这个逻辑是线程执行的。假设存储过程ProcA是一个事务,在返回结果集之前因为一些操作申请了一些排他锁,而ProcB为了返回结果又要用到这些锁,那会发生什么情况呢?

发生的情况会是连接A在等线程把连接B上的结果读出来,再来处理结果集A,而连接B等待连接A完成事务后再释放锁。双方相互等待,产生思索。

1)  两个线程间的死锁(见图)。

如果应用有两个线程,每个线程各开一个数据库连接,那上面的逻辑不会出问题。因为运行ProcA的那个线程会先做完,释放阻塞住连接B的锁,让B也能够接着跑完。但是假设有下列逻辑:

线程A:建立数据库连接A,不断读取表格A,按条取出记录,做一定处理后发给线程B的输入缓存。

线程B:建立数据库连接B,从输入缓存读取数据,依据收到的记录对表格A进行修改。

这个逻辑会产生什么问题呢?我们知道表格修改会在表上申请一些排他锁。如果线程A正在读取这条记录,修改动作会被阻塞住。这个时候线程B就会进入等待状态。但是线程A需要线程B输入缓存清空后才能写入。如果线程B还没来得及清空,它也不得不等待,这时候也会产生死锁(在SQL Server里是一个阻塞)。

解决方法:

复杂的程序还可能会出现其他的死锁形式。为了避免这种死锁,要在应用调用SQL Server的时候设置执行超时,并写好错误处理机制(参见阻塞原因2)。一旦死锁发生,SQL Server的操作在等待一段时间后会因为超时而放弃,并释放出SQL Server内部的资源,解决死锁。

小结:应更多从程序设计着手解决阻塞问题

很多用户有一种误解,认为阻塞是一个数据库问题。当阻塞问题发生的时候,都希望从数据库层面找到方法,一劳永逸地解决问题。可是,阻塞本身是为了完成事务的隔离,是应用程序向SQL Server提出的要求。所以很多时候,光从数据库端努力是不能解决阻塞问题的。在应用程序层面也要做很多工作。例如应用在做连接的时候选择什么样的隔离级别,事务开始和结束的时间点选择,连接的建立和回收机制,指令复杂度的控制等。应用程序还应该考虑到控制结果集大小,并及时从SQL Server端取走数据。还要考虑SQL Server指令执行时间长短控制,以及发生超时或其他意外后的错误处理机制等。尤其是对高并发量、高响应要求的关键业务系统,在设计应用时必须要考虑好上面这些关键因素。对于关键的业务逻辑,必须逐个审查,保证应用选择的是能够满足业务需求的最低隔离级别,事务的大小已经控制到了最小的粒度。而运行的语句,也要有良好的数据库设计,保证它不会随着数据库的增大和用户量的增多,占用更多的资源和运行时间。如果做不到这几点,就会容易发生应用在用户量比较少,或者数据库比较小的初始阶段性能不错,但是当用户量增长或数据量增大以后性能越来越慢的问题。

本文转载自:

共有 人打赏支持
鱼煎
粉丝 5
博文 180
码字总数 37359
作品 0
深圳
技术主管
Deadlock的一些总结

1.1.1 摘要 在系统设计过程中,系统的稳定性、响应速度和读写速度至关重要,就像12306.cn那样,当然我们可以通过提高系统并发能力来提高系统性能总体性能,但在并发作用下也会出现一些问题,...

长平狐
2012/06/11
70
0
Java 实时论坛——Sym 1.4.0 发布

简介 Sym 是一个用 Java 写的实时论坛,欢迎来体验! 如果你需要搭建一个企业内网论坛,请使用 SymX。 作者 Sym 的主要作者是 Daniel 与 Vanessa,所有贡献者可以在这里看到。 初衷 Sym 的诞...

88250
2016/07/29
5.5K
40
数据库管理提速:SQL解析的探索与应用

作者介绍 广友,美团点评到店综合事业群资深MySQL DBA,长期致力于MySQL及周边工具的研究。 金龙,主要从事数据库运维、高可用和运维平台建设。个人微信公众号:自己的设计师。 邢帆,美团点...

广友 金龙 邢帆
06/21
0
0
Java 实时论坛——Sym 1.5.0 发布

Java 实时论坛 Sym 1.5.0 发布了。 简介 Sym 是一个用 Java 写的实时论坛,欢迎来 体验!(如果你需要搭建一个企业内网论坛,请使用 SymX) 非常详细的 Sym 功能点脑图 如果你在搭建或者二次...

88250
2016/08/19
4K
24
TempDB为什么要根据CPU数目来决定文件个数

在SQL Server的世界中,SQL Server在Windows之上有一套自己的任务调度和资源分配系统,这使得SQL Server作为Windows的一个进程,却可以处理大量的并发,这些任务调度和资源分配非常像一个操作...

范大脚脚
2017/12/20
0
0
Java 社区论坛 - Sym 1.6.0 发布

简介 Sym 是一个用 Java 写的现代化的社区论坛,欢迎来体验!(如果你需要搭建一个企业内网论坛,请使用 SymX) 非常详细的 Sym 功能点脑图 如果你在搭建或者二次开发时碰到问题,欢迎加 Q ...

88250
2016/10/19
3.2K
32
Hive源码剖析之HiveServer2服务启动过程

一、前言 前段时间用Hive JDBC出现了阻塞问题,客户端一直处于等待状态,为了解决该问题,花了几天研究了Hive源码,于是准备写成系列博文,与大家分享其中的心得。有不当之处,请大家更正。 ...

冷血狂魔
2016/08/27
140
0
盘点灵活实用的SQL Server第三方工具

  【IT168 资讯】监控并优化SQL Server性能,以及识别和解决SQL Server数据库中的性能瓶颈,这些对于数据库管理员(DBA),开发人员和应用程序支持团队来说都是一项艰巨的任务。影响SQL Ser...

TechTarget中国
2014/08/05
0
0
SQL Server 死锁处理和优化心得

前段时间提到的"sql server 2005 死锁解决探索",死锁严重,平均每天会发生一次死锁,在解决和处理SQL server2005死锁中查了很多资料和想了很多办法, 对为何出现死锁和怎样较少死锁有了进一...

SANSOM
2014/01/19
0
0
解决SQL server2005数据库死锁的经验心得

前段时间提到的"sql server 2005 死锁解决探索",死锁严重,平均每天会发生一次死锁,在解决和处理SQL server2005死锁中查了很多资料和想了很多办法,后来我们就使用了以下方法: 1,将数据库...

光石头
2011/01/17
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

arts-week1

Algorithm 594. Longest Harmonious Subsequence - LeetCode 274. H-Index - LeetCode 219. Contains Duplicate II - LeetCode 217. Contains Duplicate - LeetCode 438. Find All Anagrams ......

yysue
13分钟前
0
0
NNS拍卖合约

前言 关于NNS的介绍,这里就不多做描述,相关的信息可以查看NNS的白皮书http://doc.neons.name/zh_CN/latest/nns_background.html。 首先nns中使用的竞价货币是sgas,关于sgas介绍可以戳htt...

红烧飞鱼
46分钟前
0
0
Java IO类库之管道流PipeInputStream与PipeOutputStream

一、java管道流介绍 在java多线程通信中管道通信是一种重要的通信方式,在java中我们通过配套使用管道输出流PipedOutputStream和管道输入流PipedInputStream完成线程间通信。多线程管道通信的...

老韭菜
今天
0
0
用Python绘制红楼梦词云图,竟然发现了这个!

Python在数据分析中越来越受欢迎,已经达到了统计学家对R的喜爱程度,Python的拥护者们当然不会落后于R,开发了一个个好玩的数据分析工具,下面我们来看看如何使用Python,来读红楼梦,绘制小...

猫咪编程
今天
0
0
Java中 发出请求获取别人的数据(阿里云 查询IP归属地)

1.效果 调用阿里云的接口 去定位IP地址 2. 代码 /** * 1. Java中远程调用方法 * http://localhost:8080/mavenssm20180519/invokingUrl.action * @Title: invokingUrl * @Description: * @ret......

Lucky_Me
今天
1
0
protobuf学习笔记

相关文档 Protocol buffers(protobuf)入门简介及性能分析 Protobuf学习 - 入门

OSC_fly
昨天
0
0
Mybaties入门介绍

Mybaties和Hibernate是我们在Java开发中应用的比较多的两个ORM框架。当然,目前Mybaties正在慢慢取代Hibernate,这是因为相比较Hibernate而言Mybaties性能更好,响应更快,更加灵活。我们在开...

王子城
昨天
2
0
编程学习笔记之python深入之装饰器案例及说明文档[图]

编程学习笔记之python深入之装饰器案例及说明文档[图] 装饰器即在不对一个函数体进行任何修改,以及不改变整体的原本意思的情况下,增加函数功能的新函数,因为这个新函数对旧函数进行了装饰...

原创小博客
昨天
1
0
流利阅读笔记33-20180722待学习

黑暗中的生物:利用奇技淫巧快活生存 Daniel 2018-07-22 1.今日导读 如果让你在伸手不见五指的黑暗当中生存,你能熬过几天呢?而大千世界,无奇不有。在很多你不知道的角落,有些生物在完全黑...

aibinxiao
昨天
6
0
Hystrix降级逻辑中如何获取触发的异常

通过之前Spring Cloud系列教程中的《Spring Cloud构建微服务架构:服务容错保护(Hystrix服务降级)》一文,我们已经知道如何通过Hystrix来保护自己的服务不被外部依赖方拖垮的情况。但是实际...

程序猿DD
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部