<b>SQL Server锁定和阻塞 防备数据泥石流</b>[MSSQL防范]

本帖供应两种做法,可避免让 client-side 程序,持续等候 SQL Server 中事件锁定造成的不正常或长时间阻塞,而让用户也无期限等候,乃至造成 connection pooling 衔接数超越容量.

日前公司 server-side 有组件,疑似因撰写时 exception-handling 做得不全面,招致罕有的特别例外发生时,让 SQL Server 的事件未履行到 cmmmit 或 rollback,造成某些表或记录被「锁定 (lock)」.后来又有大量的 request,要透过代码拜候这些被锁定的记录,后果造成了严重的长时间「阻塞」,最后有大量 process 在 SQL Server 显现「等候中」的状况.

由于 SQL Server 的「事件断绝级别」默许是 READ COMMITTED (事件期间别人无法读取),加上 SQL Server 的锁定造成阻塞时,默许是别的进程必须无期限等候 (LOCK_TIMEOUT = -1).后果这些大量的客户端 request 无期限等候永久不会提交或回滚的事件,并一向占用着 connection pool 中的资源,最后造成 connection pooling 衔接数目超载.

查了一些书,若我们要查询 SQL Server 目前会话中的 lock 超不时间,可用以下的号令:

SELECT @@LOCK_TIMEOUT 

默许为 -1,意即欲拜候的对象或记录被锁按时,会无期限等候.若欲更改当前会话的此值,可用下列号令:

SET LOCK_TIMEOUT 3000 

背面的 3000,其单位为毫秒,亦即会先等候被锁定的对象 3 秒钟.若事件仍未释放锁,则会抛回以下代号为 1222 的错误信息,可供程序员编程时做相关的逾时处理:

消息 1222,级别 16,状况 51,第 3 行

已超越了锁恳求超不时段.

若将 LOCK_TIMEOUT 设置为 0,亦即当欲拜候对象被锁按时,完好不等候就抛回代号 1222 的错误信息.此外,此一 SET LOCK_TIMEOUT 号令,影响典范只限当前会话,而非对某个表做永久的设置.

-------------------------------------------------------------------------------------------

接下来我们在 SSMS 中,开两个会话 (查询窗口) 做测试,会话 A 成立会造成阻塞的事件进程,会话 B 去拜候被锁定的记录.

--会话 A

BEGIN TRAN;  
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248 

--rollback; --成心不提交或回滚

--会话 B

SELECT * FROM Orders WHERE OrderID=10248 

辨别履行后,因为欲拜候的记录是同一条,按照 SQL Server 「事件断绝级别」和「锁」的默许值,会话 B 将无法读取该条数据,并且会永久一向等下去 (若在实际项目里写出这种代码,就预备被客户和老板臭骂).

-------------------------------------------------------------------------------------------

若将会话 B 先加上 SET LOCK_TIMEOUT 3000 的设置,以下,则会话 B 会先等候 3 秒钟,才抛出代号 1222 的「锁恳求已超时」错误信息:

SET LOCK_TIMEOUT 3000  
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248  
--SET LOCK_TIMEOUT -1 

履行后果:

消息 1222,级别 16,状况 51,第 3 行

已超越了锁恳求超不时段.

语句已终止.

-------------------------------------------------------------------------------------------

另按照我之前写的文章「30 分钟快欢愉乐学 SQL Performance Tuning」所述:
http://www.cnblogs.com/WizardWu/archive/2008/10/27/1320055.html

撰写不当的 SQL 语句,会让数据库的索引无法利用,造成全表扫描或全堆积索引扫描.比方不当的:NOT、OR 算符利用,或是直接用 + 号做来串接两个字段当作 WHERE 条件,都大概造成索引失效,变成全表扫描,除了性能变差之外,此时若这句不良的 SQL 语句,是本帖前述会话 B 的语句,由于会造成全表扫描,因此就一定会被会话 A 的事件阻塞 (因为扫描全表时,一定也会读到 OrderID=10248 的这一条记录).

下方的 SQL 语句,由于 OrderID 字段有设索引,因此下图 1 的「履行筹划」,会以算法中的「二分搜索法」在索引中快速查找 OrderID=10250 的记录

SELECT * FROM Orders WHERE OrderID=10250  
SELECT * FROM Orders WHERE OrderID=10250 AND ShipCountry='Brazil' 

图 1　有精确利用到索引的 SQL 语句,以垂直的方向利用索引.用 AND 算符时,只要有任一个字段有加上索引,就可以受惠于索引的好处,并避免全表扫描

此时若我们将这句 SQL 语句,当作前述会话 B 的语句,由于它和会话 A 所 UPDATE 的 OrderID=10248 不是同一条记录,因此不会受会话 A 事件未回滚的影响,会话 B 能正常履行 SELECT 语句.

但假如我们将会话 B 的 SQL 语句,改用以下的 OR 算符,由于 ShipCountry 字段没有加上索引,此时会造成全表扫描.如此一来,除了性能低沉以外,还会因为在全表扫描时,读到会话 A 中锁定的 OrderID=10248 那一条记录,造成阻塞,让会话 B 永久显现「等候中」的状况.

SELECT * FROM Orders WHERE OrderID=10250 OR ShipCountry='Brazil' 

图 2　未精确利用索引的 SQL 语句,以水平的方向利用索引.用 OR 算符时,必须「全部」用到的字段都有加上索引,才能真正有效利用索引、避免全表扫描

-------------------------------------------------------------------------------------------

发生阻塞时,透过以下号令,可看出是哪个进程 session id,阻塞了哪几个进程 session id,且期间经过了多少「毫秒 (ms)」.以下图 3 里 session id = 53 阻塞了 session id = 52 的进程.另透过 SQL Server Profiler 工具,也能看到相同的内容.

SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks 

图 3　本帖前述会话 A 的 UPDATE 语句 (53),阻塞了会话 B 的 SELECT 语句 (52)

透过以下两个号令,我们还能看到整个数据库的锁定和阻塞具体信息:

SELECT * FROM sys.dm_tran_locks  
EXEC sp_lock 

图 4　session id = 52 的 process 因阻塞而一向处于等候中 (WAIT)

另透过 KILL 号令,可直接杀掉造成阻塞的 process,以下:

KILL 53

-------------------------------------------------------------------------------------------

欲办理无期限等候的问题,除了前述的 SET LOCK_TIMEOUT 号令外,还有更费事的做法,以下,在会话 B 的 SQL 语句中,在表名称背面加上 WITH (NOLOCK) 关键字,表示要求  SQL Server,没必要去考虑这个表的锁定状况为什么,因此也可削减「死锁 (dead lock)」发生的机率.但 WITH (NOLOCK) 不实用 INSERT、UPDATE、DELETE.

SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248 

近似的功效,也可以下,在 SQL 语句前,先设置「事件断绝级别」为可「脏读 (dirty read)」.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT * FROM Orders WHERE OrderID=10248 

两种做法的效果近似,让会话 B 即便读到被锁阻塞的记录,也永久没必要等候,但大概读到别人未提交的数据.固然说这种做法让会话 B 不用恳求同享锁,亦即永久不会和其他事件发生冲突,但应考虑项目开辟实际的需求,若是会话 B 要查询的是原物料的库存量,或银行系统的关键数据,就不适实用这种做法,而应改用第一种做法的 SET LOCK_TIMEOUT 号令,明确让数据库抛回等候逾时的错误代号 1222,再自己写代码做处理.

-------------------------------------------------------------------------------------------

归根究柢,我们在编程时,就应当避免写出会造生长时间阻塞的 SQL 语句,亦即应最小化锁定争用的大概性,以下为一些倡议:

• 尽大概让事件轻薄短小、让锁定的时间尽大概短,比方把不必要的号令移失事件外,或把一个大量更新的事件,切成多个更新较少的事件,以改进并发性.
• 将构成事件的 SQL 语句,摆到一个「批 (batch) 处理」,以避免不必要的耽误.这些耽误常由 BEGIN TRAN ... COMMIT TRAN 号令之间的网络 I/O 所惹起.
• 考虑将事件的 SQL 语句写在一个存储历程内.普通来说,存储历程的履行速度会比批处理的 SQL 语句快,且存储历程可降低网络的流量和 I/O,让事件可更快完成.
• 尽大概频繁地承认 Cursor 中的更新,因为 Cursor 的处理速度较慢,会让锁定的时间较长.
• 若无必要,利用较宽松的事件断绝级别,如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED.而非为了项目开辟便利,全部利用默许的 READ COMMITTED 级别.
• 避免在事件履行期间,还要等候用户的反馈或交互,这样大概会造成无期限的持有锁定,好像本帖一开始提到的情况,最后造成大量的阻塞和数据库 connection 被占用.
• 避免事件 BEGIN TRAN 后查询的数据,大概在事件开始之前先被引用.
• 避免在查询时 JOIN 过量的表,不然除了性能较差外,也很简单读到正被锁定或阻塞中的表和字段.
• 应注意在一个没有索引的表上,过量的「行锁」,或一些锁定利用了过量的内存和系统资源时,SQL Server 为了有效地管理这些锁定,会尝试将锁定扩大为整个表的「表锁」,此时会很简单造成其他 process 在拜候时的阻塞和等候.

原文标题:快速搞懂 SQL Server 的锁定和阻塞

链接:http://www.cnblogs.com/WizardWu/archive/2010/08/13/1798645.html

七道奇为您推荐以下文章：