On the Journey to be a SQL Server Professional: SQL Server 2012实施与管理实战指南摘要

第15章阻塞与死锁——问题定位与解决 646

15.1. 阻塞问题定位方法及实例演示 647

· 阻塞是事务隔离带来的副作用，是一个数据库系统常见的现象。所以在遇到性能问题的时候，要首先确认性能问题是不是由于阻塞直接导致以及阻塞在多大程度上影响了SQL的性能。不要一看到SQL里发生了阻塞，就认为它是性能问题的主要原因。

· 哪些应用出了问题，这些应用是在使用同一个数据库还是不同的数据库？

· 应用端问题的现象是什么？

o 如果应用遇到一些错误，那至少要得到应用从数据库端收到的错误原文是什么，

o 如果应用端的现象是长时间没有响应（hang）或很慢，那首先要从应用端分析。它是运行到哪一步以后才出问题，这一步是不是在做数据库操作，是不是等待数据库返回。造成应用没有响应或响应很慢，数据库只是可能的因素之一。

· 什么因素让你认为阻塞是问题的主要原因？

一般阻塞会导致命令不能及时完成。所以得到的错误应该是运行时错误。如果错误不是SQL返回的，或者不是运行超时，那么问题的直接原因不是阻塞，要从其它角度去分析问题

· 阻塞发生的特征 - 如果确定是阻塞和当前问题相关,DBA要用以下问题来定位：

o SQL里有没有阻塞发生?

Check the blocked column from sysprocesses.

o 什么时候发生？

Check the waittime column from sysprocesses

o 在哪个数据库上？

SELECT name,dbid FROM sys.[sysdatabases] WHERE dbid=XXX

o 在哪个表、哪些资源上？

--This option insert the returned results from sys.sp_lock into a temp table using OPENROWSET. Need to turn on the option first.

sp_configure 'Show Advanced Options', 1

RECONFIGURE

sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

SELECT * INTO #TestTable FROM OPENROWSET('SQLNCLI', 'Server=localhost\SQL5Instance1;Trusted_Connection=yes;',

'EXEC sys.sp_lock')

--the blokced ones have the 'WAIT' status

SELECT *

FROM #TestTable

WHERE STATUS='WAIT'

--find the object name from object ID

SELECT OBJECT_NAME(1179)

--find the indexes of a table by using object_ID

SELECT * FROM sys.[indexes] WHERE [object_id]=1179

o 和阻塞有关的连接是从哪些客户应用来的？

Check the hostname, program_name, loginname etc. from sysdatabases

o 为什麽阻塞会发生？

ü 阻塞的源头是在做什么事情时候申请了这些锁，为什麽会申请这些锁？

Blocking may occur in the running statements of the current connection – use the following script to find all of the running connections including blocked or suspended ones. The idle/sleep connections will not be returned.

SELECT p.[session_id] ,

p.[request_id] ,

p.[start_time] ,

p.[status] ,

p.[command] ,

p.[blocking_session_id] ,

p.[wait_type] ,

p.[wait_time] ,

p.[wait_resource] ,

p.[total_elapsed_time] ,

p.[open_transaction_count] ,

p.[transaction_isolation_level] ,

SUBSTRING(qt.[text], p.[statement_start_offset] / 2,

( CASE WHEN p.[statement_end_offset] = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2

ELSE p.[statement_end_offset]

END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,

p.[statement_end_offset] ,

batch = qt.[text]

FROM master.sys.[dm_exec_requests] p

CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt

WHERE p.[session_id] > 50

Blocking may also occur before the running statements of the current connection – use SQL Trace before blocking occur. If SQL Trace did not start, we would not know. But we can find the last executed statement before it became idle using DBCC INPUTBUFFER.

/* 运行DBCC INPUTBUFFER (SPID) 可以获得从客户端发送到SQL实例的最后一个批处理语句。这句话的优点是不管连接是否正在运行，都会返回结果。缺点是它返回的是整个批处理语句 (All statements in a batch)，而不是当前正在执行的子句。所以对于正在运行的连接，第一种方法比较好。*/

DBCC INPUTBUFFER(@spid)

ü 阻塞的源头当前的状态是什么，是一直在执行，还是已经进入空闲状态？Check kpid and waittype in the sysprocesses.

Ø If both are 0, the thread is idle or in sleeping.

Ø If kpid !=0 and waittype=0, it is running or runnable.

ü 如果它一直在执行，为什麽要执行这麽久？

If both kpid and waittype are not 0, the thread is waiting for resources, most likely in the suspended state.

ü 如果已经进入空闲状态，那为什么没有释放锁资源？

If both are 0, the thread is idle or in sleeping. It should release the resources, if not, check the open_tran column. If it is >0, it indicates an open tran has not been committed. 然后通过DBCC INPUTBUFFER(58)也可以知道最后发过来的那句话是什么

ü 其它阻塞的连接它们想要做什么？为什麽也要申请这些锁资源？

使用以下脚本可以知道正在被堵塞住的语句，然后再去比较sp_lock的结果，就能大致判断它申请的锁数量是否合理，如果不合理，可以通过优化语句，加合适索引解决。

SELECT p.[session_id] ,

p.[request_id] ,

p.[start_time] ,

p.[status] ,

p.[command] ,

p.[blocking_session_id] ,

p.[wait_type] ,

p.[wait_time] ,

p.[wait_resource] ,

p.[total_elapsed_time] ,

p.[open_transaction_count] ,

p.[transaction_isolation_level] ,

SUBSTRING(qt.[text], p.[statement_start_offset] / 2,

( CASE WHEN p.[statement_end_offset] = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2

ELSE p.[statement_end_offset]

END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,

p.[statement_end_offset] ,

batch = qt.[text]

FROM master.sys.[dm_exec_requests] p

CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt

WHERE p.[session_id] > 50

15.2.  如何捕捉不定时出现的阻塞信息 (详见BLOCKING信息的抓取方法) http://blogs.msdn.com/b/apgcdsd/archive/2011/12/12/sql-server-blocking.aspx)

· 方法一, 抓取SQL Profiler - SQL Profiler里面包含大量的信息。其中有一个事件在Errors and Warnings->Blocked Process Report专门用来获得blocking的情况。但是因为信息量比较大，而且我们并不能很好的估算在什么时候会产生blocking，另外在生产环境使用Profiler, 对性能可能会有影响，所以SQL Profiler并不是最合适的工具。

· 方法二, 执行查询 - 如果我们检查问题的时候，blocking还存在，那么，我们可以直接可以运行几个查询，得知BLOCKING HEADER的信息SELECT * FROM sys.sysprocesses where spid>50

上述查询只是告诉我们，BLOCKING HEADER的头是SPID=53, 但是并没有告诉我们SPID=53在做什么事情。我们可以用下面的查询，得到SPID=53的信息

DBCC INPUTBUFFER(53)

我们可以把上述的两个查询合并起来，用下面的查询:

SELECT SPID = p.spid ,

DBName = CONVERT(CHAR(20), d.name) ,

ProgramName = program_name ,

LoginName = CONVERT(CHAR(20), l.name) ,

HostName = CONVERT(CHAR(20), hostname) ,

Status = p.status ,

BlockedBy = p.blocked ,

LoginTime = login_time ,

QUERY = CAST(text AS VARCHAR(MAX))

FROM master.dbo.sysprocesses p

INNER JOIN master.dbo.sysdatabases d ON p.dbid = d.dbid

INNER JOIN master.dbo.syslogins l ON p.sid = l.sid

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE p.blocked = 0

AND EXISTS ( SELECT 1

FROM master..sysprocesses p1

WHERE p1.blocked = p.spid )

这样，一次执行，就能告诉我们BLOCKING header的SPID信息，以及该SPID在做的语句。我们可以进一步研究该语句，以理解为什么该语句执行很慢。用这个方法有一个缺点，就是使用的时候，要求BLOCKING是存在的。如果BLOCKING已经消失了，那么我们的方法就不管用了

· 方法三,长期执行一个BLOCKING SCRIPT

因为我们通常无法知道BLOCKING什么时候会产生，所以通常的办法是我们长期运行一个BLOCKING SCRIPT, 这样，等下次发生的时候，我们就会有足够的信息。长期运行BLOCKING SCRIPT对性能基本上是没有影响的。因为我们每隔10秒钟抓取一次信息。缺点是，如果问题一个月才发生一次，那么，我们的BLOCKING日志信息会很大。所以这种方法适用于几天之内能重现问题。

运行方法如下:

如果要停止运行，我们按ctrl+c就可以了。

BLOCKING的信息存在log.out这个文件中。打开log.out这个文件, 就会发现谁被谁BLOCKED住了,并且，随后的文内容会显示那个阻塞的源头在干吗。

下面是BLOCKING SCRIPT的脚本, 我们可以把它存为blocking.sql

USE master

WHILE 1 = 1

BEGIN

PRINT 'Start time: ' + CONVERT(VARCHAR(26), GETDATE(), 121)

PRINT 'Running processes'

SELECT spid ,

blocked ,

waittype ,

waittime ,

lastwaittype ,

waitresource ,

dbid ,

uid ,

cpu ,

physical_io ,

memusage ,

login_time ,

last_batch ,

open_tran ,

status ,

hostname ,

program_name ,

cmd ,

net_library ,

loginame

FROM sysprocesses

--where (kpid <> 0 ) or (spid < 51)

-- Change it if you only want to see the working processes

PRINT '*********lockinfor***********'

SELECT CONVERT (SMALLINT, req_spid) AS spid ,

rsc_dbid AS dbid ,

rsc_objid AS ObjId ,

rsc_indid AS IndId ,

SUBSTRING(v.name, 1, 4) AS Type ,

SUBSTRING(rsc_text, 1, 16) AS Resource ,

SUBSTRING(u.name, 1, 8) AS Mode ,

SUBSTRING(x.name, 1, 5) AS Status

FROM master.dbo.syslockinfo ,

master.dbo.spt_values v ,

master.dbo.spt_values x ,

master.dbo.spt_values u

WHERE master.dbo.syslockinfo.rsc_type = v.number

AND v.type = 'LR'

AND master.dbo.syslockinfo.req_status = x.number

AND x.type = 'LS'

AND master.dbo.syslockinfo.req_mode + 1 = u.number

AND u.type = 'L'

ORDER BY spid

PRINT 'inputbuffer for running processes'

DECLARE @spid VARCHAR(6)

DECLARE ibuffer CURSOR FAST_FORWARD

FOR

SELECT CAST (spid AS VARCHAR(6)) AS spid

FROM sysprocesses

WHERE spid > 50

OPEN ibuffer

FETCH NEXT FROM ibuffer INTO @spid

WHILE ( @@fetch_status != -1 )

BEGIN

PRINT ''

PRINT 'DBCC INPUTBUFFER FOR SPID ' + @spid

EXEC ('dbcc inputbuffer (' + @spid + ')')

FETCH NEXT FROM ibuffer INTO @spid

END

DEALLOCATE ibuffer

WAITFOR DELAY '0:0:10'

END

这种方法的缺陷就是，log.out会比较巨大，会占用很大的空间，如果blocking一个月甚至更长时间才发生一次，那我们的这个方法就不太适宜。

· 方法四,我们用Agent Job来检查BLOCKING

长期运行一个BLOCKING SCRIPT的缺点是我们每隔一段时间，去查询信息，但是大多数收集的信息是无用的。所以会导致日志文件巨大，对于一个生产系统来讲，磁盘空间满可不是个好事情，另外，有一些客户对于用命令行来长期运行TSQL脚本有所顾忌，所以我们做了一个改进。这次，我们只收集有用的信息。对于无用的信息我们不关注。这样能极大减少日志大小。

我们首先创建一个观察数据库，然后建立两张表格 Blocking_sysprocesses和Blocking_SQLText, 建立一个存储过程和一个Job, 该Job每隔一段时间去调用存储过程。只有发现有blocking的，我们才记录到表格Blocking_sysprocesses和Blocking_SQLText这两个表格中。如果跟blocking无关，我们就不对它进行记录。下面是TSQL语句:

CREATE DATABASE [MonitorBlocking]

USE [MonitorBlocking]

CREATE TABLE Blocking_sysprocesses

(

[spid] SMALLINT ,

[kpid] SMALLINT ,

[blocked] SMALLINT ,

[waitType] BINARY(2) ,

[waitTime] BIGINT ,

[lastWaitType] NCHAR(32) ,

[waitResource] NCHAR(256) ,

[dbID] SMALLINT ,

[uid] SMALLINT ,

[cpu] INT ,

[physical_IO] INT ,

[memusage] INT ,

[login_Time] DATETIME ,

[last_Batch] DATETIME ,

[open_Tran] SMALLINT ,

[status] NCHAR(30) ,

[sid] BINARY(86) ,

[hostName] NCHAR(128) ,

[program_Name] NCHAR(128) ,

[hostProcess] NCHAR(10) ,

[cmd] NCHAR(16) ,

[nt_Domain] NCHAR(128) ,

[nt_UserName] NCHAR(128) ,

[net_Library] NCHAR(12) ,

[loginName] NCHAR(128) ,

[context_Info] BINARY(128) ,

[sqlHandle] BINARY(20) ,

[CapturedTimeStamp] DATETIME

)

CREATE TABLE [dbo].[Blocking_SqlText]

(

[spid] [SMALLINT] ,

[sql_text] [NVARCHAR](2000) ,

[Capture_Timestamp] [DATETIME]

)

CREATE PROCEDURE [dbo].[checkBlocking]

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Duration INT -- in milliseconds, 1000 = 1 sec

DECLARE @now DATETIME

DECLARE @Processes INT

SELECT @Duration = 100 -- in milliseconds, 1000 = 1 sec

SELECT @Processes = 0

SELECT @now = GETDATE()

CREATE TABLE #Blocks_rg

(

[spid] SMALLINT ,

[kpid] SMALLINT ,

[blocked] SMALLINT ,

[waitType] BINARY(2) ,

[waitTime] BIGINT ,

[lastWaitType] NCHAR(32) ,

[waitResource] NCHAR(256) ,

[dbID] SMALLINT ,

[uid] SMALLINT ,

[cpu] INT ,

[physical_IO] INT ,

[memusage] INT ,

[login_Time] DATETIME ,

[last_Batch] DATETIME ,

[open_Tran] SMALLINT ,

[status] NCHAR(30) ,

[sid] BINARY(86) ,

[hostName] NCHAR(128) ,

[program_Name] NCHAR(128) ,

[hostProcess] NCHAR(10) ,

[cmd] NCHAR(16) ,

[nt_Domain] NCHAR(128) ,

[nt_UserName] NCHAR(128) ,

[net_Library] NCHAR(12) ,

[loginName] NCHAR(128) ,

[context_Info] BINARY(128) ,

[sqlHandle] BINARY(20) ,

[CapturedTimeStamp] DATETIME

)

INSERT INTO #Blocks_rg

SELECT [spid] ,

[kpid] ,

[blocked] ,

[waittype] ,

[waittime] ,

[lastwaittype] ,

[waitresource] ,

[dbid] ,

[uid] ,

[cpu] ,

[physical_io] ,

[memusage] ,

[login_time] ,

[last_batch] ,

[open_tran] ,

[status] ,

[sid] ,

[hostname] ,

[program_name] ,

[hostprocess] ,

[cmd] ,

[nt_domain] ,

[nt_username] ,

[net_library] ,

[loginame] ,

[context_info] ,

[sql_handle] ,

@now AS [Capture_Timestamp]

FROM master..sysprocesses

WHERE blocked <> 0

AND waittime > @Duration

SET @Processes = @@rowcount

INSERT INTO #Blocks_rg

SELECT src.[spid] ,

src.[kpid] ,

src.[blocked] ,

src.[waittype] ,

src.[waittime] ,

src.[lastwaittype] ,

src.[waitresource] ,

src.[dbid] ,

src.[uid] ,

src.[cpu] ,

src.[physical_io] ,

src.[memusage] ,

src.[login_time] ,

src.[last_batch] ,

src.[open_tran] ,

src.[status] ,

src.[sid] ,

src.[hostname] ,

src.[program_name] ,

src.[hostprocess] ,

src.[cmd] ,

src.[nt_domain] ,

src.[nt_username] ,

src.[net_library] ,

src.[loginame] ,

src.[context_info] ,

src.[sql_handle] ,

@now AS [Capture_Timestamp]

FROM master..sysprocesses src

INNER JOIN #Blocks_rg trgt ON trgt.blocked = src.[spid]

IF @Processes > 0

BEGIN

INSERT [dbo].[Blocking_sysprocesses]

SELECT *

FROM #Blocks_rg

DECLARE @SQL_Handle BINARY(20) ,

@SPID SMALLINT;

DECLARE cur_handle CURSOR

FOR

SELECT sqlHandle ,

spid

FROM #Blocks_rg;

OPEN cur_Handle

FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

INSERT [dbo].[Blocking_SqlText]

SELECT @SPID ,

CONVERT(NVARCHAR(4000), [text]) ,

@now AS [Capture_Timestamp]

FROM ::

fn_get_sql(@SQL_Handle)

FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID

END

CLOSE cur_Handle

DEALLOCATE cur_Handle

END

DROP TABLE #Blocks_rg

END

USE msdb;

EXEC dbo.sp_add_job @job_name = N'MonitorBlocking';

EXEC sp_add_jobstep @job_name = N'MonitorBlocking',

@step_name = N'execute blocking script', @subsystem = N'TSQL',

@command = N'exec checkBlocking', @database_name = N'MonitorBlocking';

EXEC sp_add_jobschedule @name = N'ScheduleBlockingCheck',

@job_name = N'MonitorBlocking', @freq_type = 4, -- daily

@freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1

EXEC sp_add_jobserver @job_name = N'MonitorBlocking',

@server_name = N'(local)'

当Blocking发生一段时间后，我们可以查询下面的两个表格，以得知当时问题发生时的blocking信息:

USE MonitorBlocking

SELECT *

FROM Blocking_SqlText

SELECT *

FROM Blocking_sysprocesses

15.3. 常见阻塞原因与解决方法 658

· 类型1：由于语句运行时间太长而导致阻塞，语句本身正常运行，只需等待某些系统资源。解决思路：

o 语句本身有没有可优化的空间？这里包括修改语句本身复杂度，修改表设计，调整索引

o SQL整体性能如何，是不是有资源瓶颈影响了语句执行速度？ - 当SQL遇到诸如内存，硬盘读写，CPU等资源瓶颈时，原来能很快完成的语句有可能会花很长时间

o 如果语句天生就很复杂，无法调优（很多处理报表的语句就是这样）就需考虑怎样把这一类应用（一般就是数据库仓库应用）从OLTP系统中隔离出来

· 类型2：由于一个未按预期提交的事务导致的阻塞。这一类阻塞的特征，就是问题连接早就进入了空闲状态（sysprocesses.status='sleeping'和sysprocesses.cmd=awaiting command）。但是如果检查open_tran，就会发现它不为0，事务没有提交。这类问题很多是因为应用端遇到了一个执行超时，或者其它原因，当时执行的语句被提前终止了，但是连接还保留着。应用没有跟随发来的事务提交或回滚，导致一个事务被遗留在SQL里。一般ADO或ADO.NET的连接默认超时时限是30秒。如果30秒以内SQLSERVER没有完成语句返回任何结果，客户端会发送一个ATTENTION的消息给SQL，告诉SQL它不想继续等下去了。SQL收到这个消息之后，会终止当前正在运行的语句（或批处理），但是为了维护客户端的逻辑，SQL默认不会自动回滚或提交这个连接已经打开的事务，而是等待客户端的后续决定。如果客户端不发来回滚或提交指令，SQL会永远把这个事务保持下去直到客户端断开连接为止。解决思路：

o 应用程序必须意识到任何语句都有可能意外终止的情况，做好错误处理工作，这些工作包括：

ü 在做SQL调用的时候，需加上错误捕捉和处理语句，例如

IF @@TRANCOUNT>0 ROLLBACK TRAN

ü 设置连接属性: SET XACT_ABORT ON

当为ON时，如果执行TSQL语句产生运行错误，则整个事务将终止并回滚。当为OFF时，处理方法是不唯一的。有时只回滚TSQL语句，而事务继续进行。如果错误很严重，即使SET XACT_ABORT 为OFF，也会回滚整个事务。

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

ü 考虑是否需要关闭连接池

一般SQL应用都会使用连接池来得到良好的性能。如果有一个连接忘记把事务关闭就退出连接，那么这个连接会被交还给连接池，但是这时候，事务不会被清理。客户端驱动程序会在这个。连接下一次被重用的时候（又有新的用户要建立连接），发一句sp_reset_connection命令（这是一个存储过程）清理当前连接上次遗留下来的所有对象，包括回滚未提交的事务。如果连接交还给连接池以后，很久都没有被重用，那它的事务就会持续很长时间，引起阻塞。有些JAVA程序使用的驱动程序，提供连接池功能，但是不提供连接重用时的事务清理功能。这样的连接池对应用开发质量要求很高，比较容易发生阻塞。如果不能很快实施建议（1）和（2），把连接池关闭能缩短事务持续时间，也能从一定程度上缓解阻塞问题。

o 分析为什麽会遇到异常终止

ü 如果是超时问题，可按照类型一处理。

ü 还有一种情况是孤儿事务造成的。连接开启了隐式事务（implicit transaction）但没有加入及时提交事务的机制。隐式事务很多是客户端的数据库连接驱动或者控件为了实现它的事务功能（注意不是SQL通过TSQL语句直接提供的）而选用了这个机制。如果应用程序出现意外，或者脚本没有处理好，会有应用层事务未提交的现象。在SQL里就体现为一个孤儿事务。严格约束应用层对事务的使用，直接使用SQL里面的事务，是避免这种问题出现的好方法。

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

o 语句在SQL内执行总时间不仅包含SQL的执行时间，还包含把结果集发给客户端的时间。如果结果集比较大，SQL会分几次打包发出，每发一次，都要等待客户端确认。只有确认之后，SQL才会发送下一个结果集包。所有结果都发完以后，SQL才认为语句执行完毕，释放执行申请的资源（包括锁资源）。如果出于某种原因，客户端应用处理结果非常慢甚至没有响应，或者干脆不理睬SQL发送结果集的请求，则SQL会耐心地等待，因此会导致语句长时间执行而产生阻塞。

o 解决办法：

ü 设计程序时，一定要慎重返回大结果集。这种行为不仅会对SQL和网络带宽带来很大负担，对应用程序来说，也要花很多资源去处理结果集。

ü 如果应用程序的确需要返回大结果集，例如一些报表系统，则要考虑报表数据库和生产数据库分开

ü 如果1和2在短期内不能实现，可以和最终用户协商，返回大结果集的连接使用read uncommitted事务隔离级别，这样查询语句就不会申请S锁了。

· 类型4:阻塞的源头连接一直处于rollback状态

o 这种情况是由第一类情况衍生出来的。有时候DBA发现一个连接阻塞住了别人，为了解决问题，会让连接主动退出（强制退出应用，或者直接在SQL端KILL掉连接）。对于大部分情况，这些措施会消除阻塞，但是要记住，不管是在客户端退出还是服务点KILL，为了维护数据库事务一致性。SQL都会对连接还没有来得及完成提交的事务做回滚操作。SQL要找到所有当前事务修改过的记录，把它们改回原来的状态。所以，如果一个delete,insert,update已经运行了一个小时，可能回滚也需要一个小时。在这个过程中，阻塞还会延续，我们只能等待。有些用户可能等不及，直接重启SQL。当SQL关闭的时候，回滚操作会被中断。SQL会被很快关掉。但是这个回滚动作在下次SQL重启的时候会重新开始（恢复数据库的时候），重启的时候如果回滚不能很快结束，整个数据库都不可用，可能会带来更严重的后果

o 解决办法；

ü 最好的方法就是在工作时间尽量不要做这种大的修改操作，这些操作尽量安排在半夜或周未的时间完成。如果操作已经做了很久，最好耐心等它做完。

ü 如果一定要在工作负荷的时候做，最好把一个大操作分成若干个小操作分步完成

· 类型5：应用程序运行中产生死锁，在SQL中以阻塞形式体现

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

ü 在应用的一个线程中开启不止一个数据库连接而产生死锁

ü 两个线程间的死锁

o 解决方法：为了避免死锁，应用在调用SQL的时候设置执行超时，并写好错误处理机制。

· 小结 – 阻塞很少由SQL Server本身引起，应该更多地从程序设计着手解决阻塞问题：

o 解决阻塞需要在应用程序层面做很多工作，不能光从SQL端努力是不能解决阻塞问题的。

ü 例如应用在做连接的时候选择什么隔离级别，事务开始和结束的时间点选择，连接的建立和回收机制，指令复杂度的控制等。应用程序还应该考虑到控制结果集的大小，并及时从SQL端取走数据。还要考虑SQL指令执行时间长短控制，以及发生超时或其它意外后的错误处理机制等。尤其是对高并发，高响应要求的关键业务系统，在设计应用选择的是能够满足业务需求的最低隔离级别，事务大小已经控制到了最小的粒度。而运行的语句也要有良好的数据库设计，保证它不会随着数据库的增大和用户量的增多占用更多资源和运行时间。如果做不到这几点，那么当用户量和数据量增大的时候，性能也越来越慢。

ü 有时候一些应用会利用现成的数据库控件，以提高开发效率。某些控件会把数据库访问功能打包，例如什么时候建立连接，什么时候开启事务，数据结果集用什么方式取回，都由控件底层完成，对开发者透明。如果是要求开发性能比较高的数据库应用，必须仔细评估这些控件的处理机制。例如，有些控件会默认开启隐式事务（implicit transaction）使得事务的长度大大加长。有些控件会大量使用数据库端游标，让SQL必须为这些游标维护相应的资源。有些控件默认使用Unicode传递参数，妨碍了非Unicode字段上的索引的使用。有些控件甚至默认使用最高的事务隔离级别，使得并发度很低。这些都需要开发者做到心知肚明，达到扬长避短的效果。

ü 对于一个设计良好的应用程序，SQL是完全有能力支持高并发的繁忙的业务系统的。

o 本人曾多次看到每秒能够处理完3000甚至4000多个批处理（BATCH）的SQL应用系统前端并发的SQL连接数目（不考虑连接池等客户端机制帮助SQL减少实际并发用户数目），可以超过2000多个并发连接。这种系统一般来讲设计得比较精细，事务控制紧凑，程序错误控制严密，并且严格分离了OLTP系统和数据仓库系统这两类需求，从而严格控制指令的复杂度，有些还会通过定期归档的方式控制OLTP数据库的大小。这些都能够帮助一个数据库应用有效地避免阻塞问题

15.4. 案例分析：连接池与阻塞 666

15.4.1. 连接池与sql server 666

· 为了提高应用程序效率，大部分SQL客户端编程接口，包括ADO和ADO.NET都支持连接池（connection pooling）这种机制，以减少SQL端的登入和登出数目。

· 如果不同用户先后使用同一个连接，会不会前一个用户设置的状态或申请的资源影响后一个用户正常使用呢？如果连接池的管理者不做特殊处理，的确会有这种情况发生。所以微软的连接池技术里包括ADO.NET和ADO，引入了一个特殊的指令：sp_reset_connection，来清除前一个用户做的绝大多数设置，避免这种问题。

o 清除连接现有所有内部数据结构。包括：

ü 清除所有openxml打开的document句柄

ü 关闭所有的游标（cursor）

ü 释放所有SQL语句句柄

ü 清除所有临时对象（临时表等）

ü 释放连接持有的所有锁

ü 清除缓存的所有安全上下文信息（security context）

o 重置连接设置。包括：

ü 重置连接的SQL Trace标志值（例如1204，1222，3604等）

ü 重置所有"SET" 选项值（SET IMPLICIT_TRANSACTIONS ON 等）

ü 重置连接的统计信息值

o 回滚所有SQLSERVER事务

需要说明的是，如果连接当前参与了一个由客户端发起的分布式事务，这个分布式事务不会受到影响。在SQL里的事务还会被保留。

o 把当前数据库切换到用户默认数据库

o SQLS ERVER会再次检查当前用户是否有权做数据库连接。如果这个权力已被移除SQL SERVER会中断这个物理连接（这样防止一个已经被取消访问权的用户还能长时间使用数据库的问题

· 完成这些事情以后，一个连接基本上已经和它先前做的事情不再有任何关系（分布式事务除外）。可以说，这个连接和一个新的连接已经没有什么大的区别了。通过这些，应用程序的用户。可以尽快地拿到和释放连接，而SQL SERVER这边也不会因为连接的重复使用而产生相互影响的问题。

15.4.2. 正常情况下连接池在sql server端的处理方式 667

· 在客户端建立4次连接，每次都一条查询。连接1和连接3的程序名称是一样的，连接2和连接4的程序名称是一样的。

· 在服务器端，开启SQL Profiler，可以看出SQL Server是如何处理这4个连接请求的。

o 连接1建立一个连接，结束后，该连接进入连接池。连接3重用这个连接包括SPID，但会先执行sp_reset_connection。

o 连接2和4以同样的方式运行。

· 但在生产环境中，很难保证每个查询都成功。如果错误处理机制没跟上，连接池可能会加剧阻塞。

15.4.3. 程序端意外情况下sql server端可能导致的问题——应用端超时

· 依然用SQL Profiler监测

· 整个批处理由于超时而直接中断结束（SQL：BatchCompleted），但Commit Tran子句没有运行到，所以，事务还是处于开启状态，而这时脚本已经结束连接，将物理连接交还给了连接池。

· 下一个连接的总长度包含两部分：事务在自己连接里的时间和连接交换给连接池以后等待直到下一次被重用的那段时间。但由于连接池会动态分配，很难预测连接从上一次交换到这一次重用会等多长时间。如果不凑巧，连接池比较空闲，就可能会等很久。先前开启的事务可能一直得不到清理。如果它持有一些锁资源没有释放，就很容易产生阻塞的问题，即所谓第二类阻塞的典型行为。

· 解决方法如前所述：（1）在脚本里加入错误处理语句，捕捉SQL Server的超时错误，（2）直接在连接里设置SET XACT_ABORT ON，SQL Server会在结束批处理的时候，自动回滚事务。

15.4.4. 程序端意外情况下sql server端可能导致的问题——应用层事物未提交 675

· 前面这种没有及时清理事务的事务是由TSQL语句开启的，它导致阻塞危害可能还不会很大，因为连接池总会在下一次重用这个连接的时候将上一个事务回滚。但是，有些程序员会在应用层直接控制事务。例如在ADO.NET里可以对每个连接调用BeginTrans和CommitTrans方法，或者甚至直接调用System.Transaction接口，发起发布式事务让SQLSERVER参与进来等。

· 这样做的好处是：在应用层就能够有事务的特性，而不仅仅是SQL SERVER内部的事务。可是这样做也是有风险的。如果应用程序由于意外因素或设计缺陷，在偶发的情况下，只开启了事务而没有关闭事务，那SQLSERVER这里的事务处理就混乱了。一个事务可能会长时间得不到提交，有些看上去成功的指令到最后会被意外回滚。

· 使用应用层事务需要在每个连接里发送SET IMPLICIT_TRANSACTIONS ON到SQL Server，应用层需要在结尾写上IF @@TRANCOUNT>0 COMMIT TRAN。

· Demo里面一共开了3个连接，公用一个连接池：

o 连接1使用了ADO的BeginTran的方法，语句运行完后，调用了CommitTran的方法，一切ＯＫ。

o 连接2模拟程序崩溃，CommitTran的方法没有被调用，而是直接Close连接。

o 连接3是个一般的调用。没有使用BeginTran和CommitTran。SQL Server Profiler显示，在这种情况下，sp_reset_connection没有被调用到。连接在等了设置的间隔时间后（模拟一个真实需要花点时间的transaction），直接运行连接3的查询语句。脚本完成后立刻关闭了连接。等待10s准备退出。但结束执行时，连接池须清理现有的连接，这时连接池发现这个连接还有一个上午未提交，危险的语句这时出现了：IF @@TRANCOUNT>0 ROLLBACK TRAN，非但把连接2做的修改回滚，连接3做的修改也被回滚。但这时连接2和连接3早已结束。最终用户认为修改已经成功。而事实上这些修改都因为应用端对意外处理不当，发生意外回滚而丢失。

· 如果这种情况出现在生产环境里，后果会非常严重

o 事务的长度会非常长

由于物理连接一直在被连接池重用，连接2遗留下来的事务可能始终得不到提交或回滚。这样产生的阻塞持续时间会很长，最后可能不得不重启应用（强制清理连接池）或者重启SQLSERVER来解决

o 意外的回滚

o 对于连接2，可能程序还有机会捕捉到错误信息，找出应该运行的committrans指令为什麽没有执行。对于连接3，从应用逻辑来讲，和连接2没有任何关系。它的整个执行过程没有任何错误，最后也成功地关闭了连接。可是，因为它重用了连接2的物理连接，导致了最后在程序清理连接池的时候，把整个事务回滚。而用户认为修改已经成功。无法意识到错误的存在。

· 小结

o 连接池虽然能够减少物理连接登入登出，提供应用速度，降低SQL SERVER负荷，但是当应用端对意外处理不当时，会带来额外的阻塞和其它危害。

o 所以应用程序开发人员一定要严格规范好程序，避免这类问题发生。从数据库角度出发，完全通过TSQL语句来管理事务是比较安全可靠的方法（存储过程）。如果一个应用决定要使用其它方法管理事务（不管是ADO事务还是MSDTC）都要慎重考虑。在程序关闭连接的时候，可能加一些额外的指令确保事务已经提交。

o 如果程序使用了非TSQL的事务而带来问题，那么跟踪可能是件非常困难和痛苦的事情，需要跟踪人员同时精通SQL SERVER和应用程序逻辑，通过阅读问题开始出现的时候抓到SQL Trace文件，慢慢分析问题究竟在哪一次连接调用里。如果条件不允许或时间紧迫，关闭连接池是最快的解决方法。

15.5. 死锁问题定位及解决方法 680

15.5.1. 死锁所在的资源和检测 680

· 可以发生死锁的资源 - 死锁不是只发生在锁资源上，以下类型的资源都可能会造成阻塞，并最终导致死锁

o 锁：例如：页，行，元数据和应用程序上的锁

o 工作线程：如果排队等待线程的任务拥有阻塞其它工作线程的资源，也会导致死锁。例如：会话1Session1启动事务并获取行1ROW1的共享锁（S锁）后，进入睡眠状态。在所有可用工作线程上运行的活动会话正尝试获取行ROW1的排它锁（X锁）。当会话Session1苏醒后，因为无法获取工作线程，所以无法提交事务并释放行ROW1的锁，导致死锁。

o 内存：当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。例如：两个并发查询（Q1和Q2）作为用户定义函数执行，分别获取10MB和20MB内存。如果每个查询需要30MB而可用总内存为20MB，则Q1和Q2必须等待对方释放内存，导致死锁。

o 并行查询执行的相关资源：当一条语句用多个线程运行时，线程和线程之间可能会发生死锁

· 死锁检测

o 死锁检测是由锁监视器这个系统线程执行的，它会定期搜索SQL里的所有任务。默认时间间隔是5秒。

o 检测到死锁后，SQL会选择其中一个线程作为死锁牺牲品来结束死锁。数据库引擎终止线程当前执行的批处理，回滚死锁牺牲品的事务，并将1205错误返回到应用程序。回滚死锁牺牲品会释放事务持有的所有锁。这使其它线程的事务解锁并继续运行。

o 默认情况下，数据库引擎会选择运行回滚开销最小的事务的会话作为死锁牺牲品

· 死锁与阻塞的差别 - 死锁 vs. 锁死

很多用户会把死锁和阻塞混淆起来。当一个事务请求被其它事务锁定的资源上的锁时，发出请求的事务会一直等待，直到该锁被别人释放，自己申请到为止。这时发生的是阻塞。默认情况下，除非设置了SET LOCK_TIMEOUT 30 30为超时时间值，否则SQL事务会一直等下去，直到它自己超时为止。所以阻塞往往会持续很长时间。对程序的并发性能影响很大。有些人称这种现象为“锁死”，因为这种等待会一直持续，SQL不会做出干预。

15.5.2. 问题定位 – 使用Trace Flag 1222(SQL Server2005之前是1204)和SQL Server Profiler中死锁图形事件。

· Trace 1222

o 打开跟踪标志：DBCC TRACEON(1222,-1)

o 1222的输出结果（在errorlog中）分成3个部分, 并且由于信息量比较多, 长度要比呈现的结果长很多。死锁的参与者不是用SPID来直接显示,而是用processXXXX的方法（e.g., process6891f8)

ü 第一部分：死锁牺牲进程

ü 第二部分：process-list（死锁发生的进程信息）

ü 第三部分：resource-list（发生死锁的资源信息）

· 死锁图形事件（locks-deadlock graph）

o 在SQL Trace中也有表示死锁所涉及的任务和资源的图形描述的事件

o 要分析死锁的时候，一般结合跟踪标志1222和SQL Trace。首先在errorlog里寻找跟踪标志1222的输出结果，根据输出的时间在跟踪里寻找相应的连接。接着就可以详细分析死锁产生的原因了

15.5.3. 解决办法

· 将死锁减至最少的办法

o 按同一顺序访问对象

o 避免事务中的用户交互

o 保持事务简短并处于一个批处理中

o 使用较低的隔离级别

o 调整语句的执行计划，减少锁的申请数目

· 避免死锁和阻塞的异同

o 相同处 - 除了按同一顺序访问对象这个要求外，避免阻塞和死锁很多地方是相通的，所以采用的手段也很相似。

o 不同处 - 有时候在高并发的情况下，可能死锁是很难避免的。这时候解决问题的手段之一，可以是提高连接申请的锁粒度，让死锁的一方先因为另一方已获取了一个粒度很高的锁（例如：页面锁甚至表锁）而先遇到阻塞。以暂时的阻塞代替死锁，使得双方能依次完成，虽然速度稍慢了一些。

15.5.4. 动手实验 687 – 死锁发生了，该如何解决？

· Option 1 - 调整索引，以调整执行计划，减少锁的申请数目，从而消除死锁

o 如果数据库设计者能够引导SQL使用seek的执行计划，只读取要返回的数据，那么申请的锁数量会大大降低，死锁的几率也会减少.这是一种比较好的方法, 能够提高语句执行效率, 又消除死锁。所以应该尽可能地使用.

o 但是它也有局限性，对语句和数据库有下面两个要求：

ü 死锁双方处理的数据本身没有交叉 - 如果双方都要修改或返回同样的数据，那么再优化索引，可能也没有用处。

ü DBA要有权力在数据库里调整索引设计

· Option 2 - 使用“nolock”参数，让select 语句不要申请S锁，减少锁的申请数目，从而消除死锁。

USE [AdventureWorks]

SET NOCOUNT ON

WHILE 1 = 1

BEGIN

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_Heap]

SET [BirthDate] = GETDATE()

WHERE [NationalIDNumber] = '480951955'

SELECT *

FROM [dbo].[Employee_Demo_Heap] WITH ( NOLOCK )

WHERE [NationalIDNumber] = '480951955'

COMMIT TRAN

END

o 这个方法解决死锁可以说是立竿见影，所以很多用户很喜欢使用它

o 但是缺点很明显：

ü With (nolock) 这个参数意味着，select语句将能接收脏读，这是SQL支持的最低一级事务隔离级别。用户要能够接受，才能使用这种方法只能解决S锁参与的死锁问题，

ü 如果死锁发生在U锁或X锁上，with (nolock)没有帮助

ü 这个方法要去修改语句本身。如果语句是由应用程序动态生成，而不是写在SQL的存储过程里，要应用开发人员才能够修改

· Option 3 - 升级锁粒度,将死锁转化为一个阻塞问题

o 死锁产生的原因是双方都申请到了一个资源，同时又要申请对方的资源。如果一方一个资源都没有申请到，那么发生的就是阻塞，而不是死锁。从这个角度讲，如果能想办法让一方被另一方阻塞住，什么资源都申请不到，那死锁就不会发生。

o 在书中的例子里，死锁发生在同一个page上的不同rid上。如果语句直接申请Page级别的锁，同时就只能有一个人得到锁资源，而另一个人会被阻塞住。因此，使用pagelock这个参数，也能解决这里的死锁问题。

o 这种方法虽然不会降低事务隔离级别，但是语句更容易被阻塞住，最终的并发度难免受到影响。因此实际上也是一种粗鲁的方法。没有第一种通过加索引的方法那么精细。如果能用第一种方法，还是使用第一种方法比较负责任。

USE [AdventureWorks]

SET NOCOUNT ON

WHILE 1 = 1

BEGIN

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_Heap] WITH ( PAGLOCK )

SET [BirthDate] = GETDATE()

WHERE [NationalIDNumber] = '480951955'

SELECT *

FROM [dbo].[Employee_Demo_Heap] WITH ( PAGLOCK )

WHERE [NationalIDNumber] = '480951955'

COMMIT TRAN

END

· Option 4 - 使用快照隔离级别

o 由于这个死锁里有S锁参与，这里把事务隔离级别从默认的已提交读改成快照，对缓解这个死锁也会有很好帮助。在语句的开头加上这句话就行。其它依旧。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

15.6. 小结 698

· 是否有阻塞？通过查看sysprocesses可知道。如果waittime很大，且blocked大于0，则较严重。

· 阻塞源头是什么？为什么长时间不能完成？是语句运行时间太长的问题还是应用程序没有正确处理事务所致？

· 阻塞是不是不定时发生？如是，可以事先运行阻塞监控脚本，主动去监测阻塞信息。

· 阻塞时单向的，死锁是双向的（相互阻塞）。绝大多数死锁SQL Server都可以用它的死锁检测机制检测出来并kill掉一个线程来结束死锁。如果你想具体知道SQL Server是如何检测的，可使用Trace Flag 1222和Profiler中死锁图形事件来了解。如你想减少或者消除死锁现象，还可以考虑调整索引，使用nolock参数，升级锁的粒度，或使用快照隔离等级。

SQL Server 2012实施与管理实战指南摘要 - 第 15 章