日前公司 server-side 有组件，疑似因撰写时 exception-handling 做得不周全

由于 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 去访问被锁定的记录。--会话 ABEGIN TRAN;UPDATE Orders SET EmployeeID7 WHERE OrderID10248--rollback; --故意不提交或回滚--会话 BSELECT * FROM Orders WHERE OrderID10248分别执行后因为欲访问的记录是同一条按照 SQL Server 「事务隔离级别」和「锁」的默认值会话 B 将无法读取该条数据而且会永远一直等下去 (若在现实项目里写出这种代码就准备被客户和老板臭骂)。-------------------------------------------------------------------------------------------若将会话 B 先加上 SET LOCK_TIMEOUT 3000 的设置如下则会话 B 会先等待 3 秒钟才抛出代号 1222 的「锁请求已超时」错误信息--会话 BSET LOCK_TIMEOUT 3000SELECT * FROM Orders WHERE OrderID10248--SET LOCK_TIMEOUT -1执行结果消息 1222级别 16状态 51第 3 行已超过了锁请求超时时段。语句已终止。-------------------------------------------------------------------------------------------另根据我之前写的文章「30 分钟快快乐乐学 SQL Performance Tuning」所述30 分钟快快乐乐学 SQL Performance Tuning - WizardWu - 博客园撰写不当的 SQL 语句会让数据库的索引无法使用造成全表扫描或全聚集索引扫描。例如不当的NOT、OR 算符使用或是直接用 号做来串接两个字段当作 WHERE 条件都可能造成索引失效变成全表扫描除了性能变差之外此时若这句不良的 SQL 语句是本帖前述会话 B 的语句由于会造成全表扫描或聚集索引扫描因此就一定会被会话 A 的事务阻塞 (因为扫描全表时一定也会读到 OrderID10248 这一条会话 A 正在锁定的记录)。下方的 SQL 语句由于 OrderID 字段有设索引因此下图 1 的「执行计划」会以算法中的「二分查找法」在索引中快速查找 OrderID10250 的记录。SELECT * FROM Orders WHERE OrderID10250SELECT * FROM Orders WHERE OrderID10250 AND ShipCountryBrazil图 1 有正确使用到索引的 SQL 语句以垂直的方向使用索引。用 AND 算符时只要有任一个字段有加上索引就能受惠于索引的好处并避免全表扫描此时若我们将这句 SQL 语句当作前述会话 B 的语句由于它和会话 A 所 UPDATE 的 OrderID10248 不是同一条记录因此不会受会话 A 事务未回滚的影响会话 B 能正常执行 SELECT 语句。但若我们将会话 B 的 SQL 语句改用如下的 OR 算符由于 ShipCountry 字段没有加上索引此时会造成聚集索引扫描 (和全表扫描一样会对整个表做逐条记录的 scan)。如此一来除了性能低落以外还会因为在逐条扫描时读到会话 A 中锁定的 OrderID10248 那一条记录造成阻塞让会话 B 永远呈现「等待中」的状态。SELECT * FROM Orders WHERE OrderID10250 OR ShipCountryBrazil图 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_locksEXEC 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 OrderID10248类似的功能也可如下在 SQL 语句前先设置「事务隔离级别」为可「脏读 (dirty read)」。SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM Orders WHERE OrderID10248两种做法的效果类似让会话 B 即使读到被锁阻塞的记录也永远不必等待但可能读到别人未提交的数据。虽然说这种做法让会话 B 不用请求共享锁亦即永远不会和其他事务发生冲突但应考虑项目开发实际的需求若会话 B 要查询的是原物料的库存量或银行系统的关键数据就不适合用这种做法而应改用第一种做法的 SET LOCK_TIMEOUT 命令明确让数据库抛回等候逾时的错误代号 1222再自己写代码做处理。