SQLserver阻塞.docx

1、SQLserver阻塞SQL server阻塞（来自微软技术支持人员）作者:lyf1840阻塞定义 = 当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时，将发生阻塞。其结果是强制第二个连接等待，而在第一个连接上阻塞。不管是来自同一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另一个连接。说明 一些需要锁保护的操作可能不明显，例如系统目录表和索引上的锁。 大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的进程链都在其它进程上等待锁。 常见的阻塞情形包括 = 1 .提交执行时间长的查询。 长时间运行的查询会阻塞其它查询。例如，影响很多行的 DELETE

2、或 UPDATE 操作能获取很多锁，这些锁不论是否升级到表锁都阻塞其它查询。因此，一般不要将长时间运行的决策支持查询和联机事务处理 (OLTP) 查询混在一起。解决方案是想办法优化查询，如更改索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机上运行查询。 2 .查询不适当地使用游标。游标可能是在结果集中浏览的便利方法，但使用游标可能比使用面向集合的查询慢。 3 .取消没有提交或回滚的查询。 如果应用程序取消查询（如使用开放式数据库连接 (ODBC) sqlcancel 函数）但没有同时发出所需数目的 ROLLBACK 和 COMMIT 语句，则会发生这种情况。取消查询并不自动回滚或提

3、交事务。取消查询后，所有在事务获取的锁都将保留。应用程序必须提交或回滚已取消的事务，从而正确地管理事务嵌套级。4 .应用程序没处理完所有结果。 将查询发送到服务器后，所有应用程序必须立即完成提取所有结果行。如果应用程序没有提取所有结果行，锁可能会留在表上而阻塞其他用户。如果使用的应用程序将 Transact-SQL 语句透明地提交给服务器，则该应用程序必须提取所有结果行。如果应用程序没这样做（如果无法配置它执行此操作），则可能无法解决阻塞问题。为避免此问题，可以将这些应用程序限制在报表或决策支持数据库上。5 .分布式客户端/服务器死锁。 与常规死锁不同，分布式死锁无法由 Microsoft S

4、QL Server? 2000 自动检测到。如果应用程序打开多个与 SQL Server 的连接并异步提交查询，则可能会发生分布式客户端/服务器死锁。 例如，一个客户端应用程序线程有两个开放式连接。该线程异步启动事务并在第一个连接上发出查询。应用程序随后启动其它事务，在另一个连接上发出查询并等待结果。当 SQL Server 返回其中一个连接的结果时，应用程序开始处理这些结果。应用程序就这样处理结果，直到生成结果的查询被另一个连接上执行的查询阻塞而导致再没有可用的结果为止。此时第一个连接阻塞，无限期等待处理更多的结果。第二个连接没有在锁上阻塞，但仍试图将结果返回给应用程序。然而，由于应用程序阻

5、塞而在第一个连接上等待结果，第二个连接的结果将得不到处理。避免阻塞方法 = 1 .对每个查询使用查询超时。 2 .对每个查询使用锁定超时。有关更多信息，请参见自定义锁超时。 3 .使用绑定连接。有关更多信息，请参见使用绑定连接。 4 .SQL Server 本质上是受客户端应用程序操纵的傀儡。客户端应用程序对服务器上获取的锁几乎有完全的控制（并对锁负责）。虽然 SQL Server 锁管理器自动使用锁保护事务，但这受客户端应用程序发出的查询类型和对结果的处理方式的直接鼓动。因此，大多数阻塞问题的解决方案都涉及检查客户端应用程序。5 .阻塞问题常要求检查应用程序提交的 SQL 语句本身，以及检查

6、与连接管理、所有结果行的处理等有关的应用程序行为本身。如果开发工具不允许显式控制连接管理、查询超时、结果处理等，阻塞问题可能得不到解决。设计应用程序以避免阻塞的准则包括 = 1. 不要使用或设计使用户得以填写编辑框的应用程序，编辑框会生成长时间运行的查询。例如，不要使用或设计提示用户输入的应用程序，允许某些字段保留空白或允许输入通配符。这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。2 .不要使用或设计使用户得以在事务输入容的应用程序。 3 .允许取消查询。 4 .使用查询或锁定超时，防止失控查询和避免分布式死锁。 5 .立即完成提取所有结果行。 6 .使事务尽可能简短。 7 .显

7、式控制连接管理。 8 .在所预计的并发用户全负荷下对应用程序进行应力测试。 以下是一些相关的技术文档。 Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems HOW TO: Troubleshoot Application Performance with SQL Server 了解和解决 SQL Server 7.0 或 2000 阻塞问题- - 本文的发布号曾为 CHS224453本页容概要更多信息概要本文是对如下 Microsoft Knowledge Base 文章（针对 SQL Server 6.x）

8、中的 SQL Server 7.0 的更新： 162361 INF： 了解和解决 SQL Server 阻塞问题 以上文章所包含的很多信息已经更新，并包括在 SQL Server 7.0 Books Online 的“Understanding and Avoiding Blocking”主题中。 继续阅读本文之前，请仔细检查这些信息，本文将不再重复这些容。 本文重点讨论如何监视 SQL Server 捕获相关的系统信息，以及如何分析信息以便成功地解决阻塞问题。 本文使用的标准术语与上面信息中定义的相同。 在本文的讨论中，术语“连接”是指数据库的单个已登录会话。 每个连接均以一个系统进程 ID

9、 (SPID) 出现。 每个 SPID 通常作为一个进程来引用，尽管在一般意义上它不是一个独立的进程环境。 更准确地说，每个 SPID 由响应指定客户端请求的单个连接时所需要的服务器资源和数据结构组成。 单个客户端应用程序可能有一个或多个连接。 从 SQL Server 的角度看，来自单个客户端计算机的单个客户端应用程序的多个连接，与来自多个客户端应用程序或多个客户端计算机的多个连接之间，没有什么区别。 一个连接可以阻塞另一个连接，这与它们是否出自同一个应用程序或出自两个不同的客户端计算机上的独立应用程序无关。 更多信息阻塞是具有基于锁定的并发特性的任何关系数据库管理系统 (RDBMS) 所不

10、可避免的特征。 在 SQL Server 上，当一个 SPID 锁定了一个特定资源，而第二个 SPID 试图获得对同一资源的冲突锁定类型时，就会发生阻塞。 通常，第一个 SPID 锁定资源的时间围非常小。 当它释放锁定时，第二个连接即可顺利地获得对资源的自有锁定，并继续下一步操作。 这是正常情况，在一天中可能发生许多次，对系统性能没有任何明显的影响。 查询的持续时间和事务环境决定了锁定被占据的时间长短，因而也决定了对其它查询的影响。 如果查询不是在事务执行（并且没有使用任何锁定提示），对于SELECT语句引起的锁定，则只有当某个资源被实际读取时才会对该资源锁定，而不会在整个查询持续期间锁定该资

11、源。 至于 INSERT、UPDATE 和 DELETE 语句，则在查询期间锁定它们，这既是为了数据的一致性，也是为了允许在必要时进行回滚查询。 对于事务执行的查询，决定锁定持续时间长短的因素包括：查询类型、事务隔离级别以及查询中是否使用了锁定提示。 有关锁定、锁定提示和事务隔离级别的说明，请参见 SQL Server 7.0 Books Online 中的如下主题： “Understanding Locking in SQL Server” “Locking Architecture” “Lock Compatibility” “Locking Hints” “Changing Defaul

12、t Locking Behavior in Oracle and SQL Server” 当锁定和阻塞增加到对系统性能产生不利影响的时候，通常是由于如下原因所至： 一个 SPID 锁定了一组资源，并且在释放锁之前持续了很长一段时间。 这种类型的阻塞在一段时间后自行消失，但会导致性能降低。 一个 SPID 锁定了一组资源，并且不再释放它们。 这种类型的阻塞不会自行消失，并且会无限期地阻止对受影响资源的访问。 在上面第一种情况中，一段时间后 SPID 释放锁定时阻塞问题将自行消失。 但是，情况可能是变化多端的，因为一段时间后不同的 SPID 会阻塞不同的资源，总是产生变化的目标。 由于这个原因，这

13、些情况很难使用 SQL Server Enterprise Manager 或单独的 SQL 查询来解决问题。 而第二种情况则产生更便于诊断的持续状态。 收集阻塞信息 要减少解决阻塞问题的难度，数据库管理员可以使用 SQL 脚本连续地监视 SQL Server 上的锁定和阻塞状态。 这些脚本可以提供一段时间后指定实例的快照，从而帮助用户全面了解存在的问题。 关于如何用 SQL 脚本监视阻塞的说明，请参见下面的 Microsoft Knowledge Base 文章： 251004 INF： 如何监视 SQL Server 7.0 阻塞 271509 INF： 如何监视 SQL Server 2

14、000 阻塞 本文中的脚本将执行下面的任务。 在可能的情况下，我们还提供通过 Enterprise Manager 或特定 SQL 查询来获得该信息的方法。 1.找出阻塞链源头的 SPID。除了使用上面提到的脚本外，还可以使用 SQL Enterprise Manager 找出阻塞链源头的 SPID，方法如下： a. 展开服务器组，然后展开服务器。 b. 展开 Management，然后展开 Current Activity。 c. 展开 Locks / Process ID。 SPID 及其阻塞信息将显示在详细信息窗格中。 正在造成阻塞的 SPID 将显示为“(Blocking)”。 但请注

15、意，有时需要使用脚本查询而不使用 Enterprise Manager，因为某些类型的 tempdb 阻塞问题可能会阻止您运行使用临时表操作的查询。 使用直接查询可以给您提供必要的控制能力，以便避免出现该问题。 2.查找引起阻塞的 SPID 正在运行的查询。脚本方法使用如下查询来确定特定 SPID 发出的命令： DBCC INPUTBUFFER ()另外一种方法，可以按如下步骤使用 SQL Enterprise Manager： a. 展开服务器组，然后展开服务器。 b. 展开 Management，然后展开 Current Activity。 c. 单击 Process Info。 SPID

16、 将显示在详细信息窗格中。 d. 双击引起阻塞的 SPID 即可看见该 SPID 执行的最后一个 Transact-SQL 命令组。 3.查找引起阻塞的 SPID 当前占用的锁定类型。执行 sp_lock 系统存储过程，即可找到该信息。 另外一种方法，可以按如下步骤使用 Enterprise Manager 获得该信息： a. 展开服务器组，然后展开服务器。 b. 展开 Management，然后展开 Current Activity。 c. 展开 Locks / Process ID。 SPID 以及正在使用的锁定的相关信息将显示在详细信息窗格中。 4.查找引起阻塞的 SPID 的事务嵌套级

17、别和进程状态。SPID 的事务嵌套级别可以在 TRANCOUNT 全局变量中找到。 但是，通过按如下方式查询 sysprocesses 表，可以从 SPID 以外找到它。 SELECT open_tran FROM SYSPROCESSES WHERE SPID=go返回的值为该 SPID 的 TRANCOUNT 值。 该值显示阻塞 SPID 的事务嵌套级别，反过来可以解释为什么 SPID 正在使用锁定。 例如，如果该值大于零，则 SPID 位于事务中间（这种情况下，根据事务隔离级别，可以预计它将保持已经获得的某些锁定）。 要查看数据库中是否存在任何长时间打开的事务，还可以使用 DBCC OP

18、ENTRAN database_name。 收集 SQL Server 事件探查器跟踪信息 除以上信息外，通常还需要捕获服务器上各种活动的“事件探查器”跟踪文件，以便彻底调查 SQL Server 上的阻塞问题。 如果 SPID 在事务执行了多个语句，那么只有最后一个语句出现在 DBCC INPUTBUFFER 输出中。 但是，有可能是更前面的某个命令导致锁定仍然被占据。 “事件探查器”跟踪文件将使您能看见由 SPID 在当前事务执行的所有命令。 下面的步骤将帮助您设置 SQL Server 事件探查器以便捕获踪迹。 1.打开 SQL Server 事件探查器。 2.在 Tools 菜单上，单

19、击 Options。 3.确保选中 All Event Classes 和 All Data Columns 选项。 4.单击 OK。 5.在 File 菜单中，指向 New，然后单击 Trace。 6.在 General 选项卡上，指定要捕获数据的跟踪名称和文件。 7.在 Events 选项卡上，将如下事件类型添加到跟踪中： Error and Warning Exception 该事件表示发生了异常错误。 严重度低于 25 的异常错误表示错误已从 SQL Server 返回客户端。 严重度为 25 的异常错误是部的 SQL Server 异常错误，如下所述应当被筛选掉。 Misc. Att

20、ention 该事件表示已经发出一个注意信号。 发出注意信号的一般原因是发生客户端取消操作或查询超时。 Sessions Connect 该事件表示已建立一个新连接。 Sessions Disconnect 该事件表示一个客户端已经断开连接。 Sessions Existing Connection 该事件表示当“SQL 事件探查器”跟踪启动时存在一个连接。 TSQL RPC:Starting 该事件表示已经开始执行远程过程调用 (RPC)。 TSQL SQL:BatchStarting 该事件表示已经开始执行 Transact-SQL 命令组。 Stored Procedures SP: S

21、tmtStarting 该事件表示存储过程中的语句正在开始执行。 存储过程名位于事件的 Text 部分的开头。 另外，您可以包括如下事件以便获得更进一步的信息。 如果当前的运行环境是高数据量运营环境，那么可以决定只使用以上事件，因为有了它们足以能解决阻塞问题了。 如果还包括以下的附加事件，可以使您能更方便迅速地找出问题的根源，但这也会增加系统负载并增加跟踪输出的大小。 Misc. Execution Plan 该事件显示被执行的 Transact-SQL 语句的计划树。 Transactions DTCTransaction 该事件跟踪两个或多个数据库或服务器之间的 Microsoft 分布式

22、事务处理协调器 (MS DTC) 事务。 Transactions SQLTransaction 该事件跟踪 SQL BEGIN、SAVE、COMMIT 和 ROLLBACK TRANSACTION 语句。 TSQL RPC:Completed 该事件表示远程过程调用 (RPC) 已经执行完毕。 TSQL SQL:BatchCompleted 该事件表示 Transact-SQL 命令组已经执行完毕。 Stored Procedures SP: StmtCompleted 该事件表示存储过程中的语句已经执行完毕。 8.在 Data Columns 选项卡上，确保包括了如下列： Start Ti

23、me、End Time、Connection ID、SPID, Event Class、Text、Integer Data、Binary Data、Application Name、NT User Name 和 SQL User Name。 如果包括上面第二个表中的附加事件，则还要包括以下数据列： Duration、CPU、Reads 和 Writes。 9.在 Filters 选项卡上，排除 SQL Server 部异常错误。 在 Trace Event Criteria 框中，选择 Severity 并在 Maximum 框中键入 24。 然后单击 OK。 有关监视从 SQL Server

24、 发送到客户端的错误消息的详细信息，请参见下面的 Microsoft Knowledge Base 文章： 199037 INF： 跟踪从 SQL Server 发送到客户端的错误消息 有关使用“事件探查器”的信息，请参见 SQL Server Books Online。 识别和解决常见的阻塞情形 通过查看以上信息，可以找出大多数阻塞问题的原因。 本文下面的容将讨论如何使用这些信息来识别并解决常见的阻塞情形。 该讨论假设您已经使用文章 Q251004（前面已提到）中的阻塞脚本来捕获有关阻塞 SPID 的信息，并已经使用上面提到的事件制作了“事件探查器”跟踪。 查看阻塞脚本输出 检查 syspr

25、ocesses 输出，以找到阻塞链的开头。如果没有为阻塞脚本指定快速模式，则在脚本输出中会有一个标题为“SPIDs at the head of blocking chains”的段落列出引起阻塞的 SPID： 在阻塞链的开头的 SPID spid - 9 10如果指定了快速选项，仍然可以通过查看 sysprocesses 输出来找出阻塞头。 以下容摘自 sysprocesses 输出： spid status blocked 9 sleeping 0 10 sleeping 0 11 sleeping 13 12 sleeping 10 13 sleeping 9 14 sleeping 1

26、2其中，可以在 blocked 列中看见 SPID 9 和 SPID 10 均为 0，这表示它们当前没有被阻塞，但它们出现在其它 SPID 的 blocked 列中。 这表示 SPID 9 和 SPID 10 分别位于独立阻塞链的开头。 检查 sysprocesses 输出中有关位于阻塞链开头的 SPID 的信息。一定要检查如下 sysprocesses 字段： Status 该列快速展示特定 SPID 的状态。 通常，状态 sleeping 表示 SPID 已经执行完毕，并且正在等候应用程序提交另一个查询或命令组。 状态 runnable 表示 SPID 当前正在处理查询。 下表简单解释各种

27、状态值。 Background SPID 正在执行后台任务。 Sleeping SPID 当前没有执行任务。 通常，这表示 SPID 正在等待应用程序的命令。 Runnable SPID 当前正在执行任务。 Dormant 与 Sleeping 相同，但 Dormant 还表示在完成 RPC 事件后 SPID 已被重置。 重置将清除 RPC 事件期间所使用的资源。 这是个正常状态，并且 SPID 可用，正在等待执行后续命令。 Rollback SPID 在事务的回滚过程中。 Defwakeup 表示 SPID 正在等待处于释放过程中的资源。 waitresource 字段应当表示提到的资源。

28、Spinloop 在试图获得用于 SMP 系统并发控制的旋转锁定 (spinlock) 时进程正处于等待中。 Open_tran 该字段告诉您 SPID 的事务嵌套级别。 如果该值大于 0，则 SPID 位于一个打开的事务中，并且可能正在占用由事务中的任何语句获得的锁定。 Lastwaittype、waittype 和 waittime lastwaittype 字段告诉您 SPID 的最后一个 waittype 或当前 waittype。 该字段是 SQL Server 7.0 中的新字段，是 waittype 字段的字符串表现形式（waittype 是被保留的部二进制列）。 如果 wait

29、type 是 0x0000，那么 SPID 当前没有等待任何任务，并且 lastwaittype 的值表示 SPID 拥有的最后一个 waittype。 如果 waittype 为非零，则 lastwaittype 的值表示 SPID 的当前 waittype。 有关不同 lastwaittype 和 waittype 值的简要说明，请参见下面的 Microsoft Knowledge Base 文章： 244455 INF： Sysprocesses Waittype 和 Lastwaittype 字段的定义 waittime 值可以用来确定 SPID 是否正在执行任务。 如果在查询 sys

30、processes 表时返回 waittime 列中的值，而该值小于从上一个 sysprocesses 查询所获得的 waittime 值，那么，这表示前面的锁定已被获得并被释放，现在正在等候新的锁定（假设是非零的 waittime）。 通过比较各个 sysprocesses 输出中的 waitresource，可以对此进行确认。 Waitresource 该字段表示 SPID 正在等候的资源。 下表列出了 waitresource 常见格式及其意义： Table DatabaseID:ObjectID TAB: 5:261575970 这里，数据库 ID 5 是 pubs 示例数据库，对象 ID 261575970 是 titles 表。 Page DatabaseID:FileID:PageID PAG: 5:1:104 这里，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，而页 104 是属于 titl