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

时　间:2016-05-08 08:44:13
作　者:缪炜   ID：24010  城市：江阴
摘　要:这里通过连接在sysprocesses里字段值的组合来分析阻塞源头，可以把阻塞分为以下5种常见的类型（见表）。waittype,open_tran,status,都是sysprocesses里的值，“自我修复？”列的意思,就是指阻塞能不能自动消失。
正　文:

 5种常见的阻塞类型

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

解决方法：

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

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

这一类阻塞的特征，就是问题连接早就进入了空闲状态（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

通过结果（见图）可以得知：

(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），并且连接当前不再事务中，则执行下列任何一条语句都会开启一个新的事务。

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

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

         语句在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锁了。

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

有些用户可能等不及，直接重启SQL Server。当SQL Server关闭的时候，回滚动作会被中断，SQL Server会被很快关掉，但是这个回滚动作在下次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。

    ●  结束运行

发生的情况会是连接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指令执行时间长短控制，以及发生超时或其他意外后的错误处理机制等。尤其是对高并发量、高响应要求的关键业务系统，在设计应用时必须要考虑好上面这些关键因素。对于关键的业务逻辑，必须逐个审查，保证应用选择的是能够满足业务需求的最低隔离级别，事务的大小已经控制到了最小的粒度。而运行的语句，也要有良好的数据库设计，保证它不会随着数据库的增大和用户量的增多，占用更多的资源和运行时间。如果做不到这几点，就会容易发生应用在用户量比较少，或者数据库比较小的初始阶段性能不错，但是当用户量增长或数据量增大以后性能越来越慢的问题。