快速搞懂 sql server 的锁定和阻塞

《快速搞懂 sql server 的锁定和阻塞》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、快速搞懂SQLServer的锁定和阻塞本帖提供两种做法，可避免在SQLServer事务锁定时产生的不正常或长时间阻塞，让用户和程序也无限期等待，甚至引起connectionpooling连接数超过容量。所谓的「阻塞」，是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进入等待的状态。SQLServer默认会让被阻塞的请求无限期地一直等待，直到原来的事务释放相关的锁，或直到它超时(根据SETLOCK_TIMEOUT，本文后续会提到)、服务器关闭、进程被杀死。一般的系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导

2、致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话欲读取或更新的需求。遇到这种情况，可能就需要手工排除阻塞的状态，而本文接下来要介绍两种排除阻塞的做法。 日前公司server-side有组件，疑似因撰写时exception-handling做得不周全，导致罕见的特殊例外发生时，让SQLServer的事务未执行到cmmmit或rollback，造成某些表或记录被「锁定(lock)」。后来又有大量的request，要透过代码访问这些被锁定的记录，结果造成了严重的长时间「阻塞」，最后有大量process(进程) 在SQLServer呈现「等待中(WAIT)」的状态。由于

3、SQLServer的「事务隔离级别」默认是READCOMMITTED(事务期间别人无法读取)，加上SQLServer的锁定造成阻塞时，默认是别的进程必须无限期等待(LOCK_TIMEOUT=-1)。结果这些大量的客户端request无限期等待永远不会提交或回滚的事务，并一直占用着connectionpool中的资源，最后造成connectionpooling连接数目超载。查了一些书，若我们要查询SQLServer目前会话中的lock超时时间，可用以下的命令：SELECT@@LOCK_TIMEOUT执行结果默认为-1，意即欲访问的对象或记录被锁定时，会无限期等待。若欲更改当前

4、会话的此值，可用下列命令：SETLOCK_TIMEOUT3000后面的3000，其单位为毫秒，亦即会先等待被锁定的对象3秒钟。若事务仍未释放锁，则会抛回如下代号为1222的错误信息，可供程序员编程时做相关的逾时处理：消息1222，级别16，状态51，第3行已超过了锁请求超时时段。若将LOCK_TIMEOUT设置为0，亦即当欲访问对象被锁定时，完全不等待就抛回代号1222的错误信息。此外，此一SETLOCK_TIMEOUT命令，影响范例只限当前会话(进程)，而非对某个表做永久的设置。-----------------------------------------------

5、--------------------------------------------接下来我们在SSMS中，开两个会话(查询窗口)做测试，会话A创建会造成阻塞的事务进程，会话B去访问被锁定的记录。 --会话 ABEGIN TRAN;UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248--rollback; --故意不提交或回滚  --会话 BSELECT * FROM Orders WHERE OrderID=10248  分别执行后，因为欲访问的记录是同一条，按照SQLServer「事务隔离级别」和「锁」的默认值，会话B

6、将无法读取该条数据，而且会永远一直等下去(若在现实项目里写出这种代码，就准备被客户和老板臭骂)。-------------------------------------------------------------------------------------------若将会话B先加上SETLOCK_TIMEOUT3000的设置，如下，则会话B会先等待3秒钟，才抛出代号1222的「锁请求已超时」错误信息： --会话 BSET LOCK_TIMEOUT 3000SELECT * FROM Orders WHERE OrderID=10248--SET LOCK_TIM

7、EOUT -1  执行结果：消息1222，级别16，状态51，第3行已超过了锁请求超时时段。语句已终止。-------------------------------------------------------------------------------------------另根据我之前写的文章「30分钟快快乐乐学SQLPerformanceTuning」所述：http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html撰写不当的SQL语句