SQL server阻塞（来自微软技术支持人员）
lyf1840
阻塞定义===============当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时，将发生阻塞。其结果是强制第二个连接等待，而在第一个连接上阻塞。不管是来自同一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另一个连接。
说明 一些需要锁保护的操作可能不明显，例如系统目录表和索引上的锁。大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的进程链都在其它进程上等待锁。
常见的阻塞情形包括=============== 1 .提交执行时间长的查询。 长时间运行的查询会阻塞其它查询。例如，影响很多行的 DELETE 或 UPDATE 操作能获取很多锁，这些锁不论是否升级到表锁都阻塞其它查询。因此，一般不要将长时间运行的决策支持查询和联机事务处理 (OLTP)
查询混在一起。解决方案是想办法优化查询，如更改索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机上运行查询。 2 .查询不适当地使用游标。游标可能是在结果集中浏览的便利方法，但使用游标可能比使用面向集合的查询慢。 3 .取消没有提交或回滚的查询。 如果应用程序取消查询（如使用开放式数据库连接 (ODBC) sqlcancel 函数）但没有同时发出所需数目的 ROLLBACK 和 COMMIT 语句，则会发生这种情况。取消查询并不自动回滚或提交事务。取消查询后，所有在事务获取的锁都将保留。应用程序必须提交或回滚已取消的事务，从而正确地管理事务嵌套级。
4 .应用程序没处理完所有结果。 将查询发送到服务器后，所有应用程序必须立即完成提取所有结果行。如果应用程序没有提取所有结果行，锁可能会留在表上而阻塞其他用户。如果使用的应用程序将
Transact-SQL 语句透明地提交给服务器，则该应用程序必须提取所有结果行。如果应用程序没这样做（如果无法配置它执行此操作），则可能无法解决阻塞问题。为避免此问题，可以将这些应用程序限制在报表或决策支持数据库上。
5 .分布式客户端/服务器死锁。 与常规死锁不同，分布式死锁无法由 Microsoft SQL Server? 2000 自动检测到。如果应用程序打开多个与 SQL Server 的连接并异步提交查询，则可能会发生分布式客户端/服务器死锁。 例如，一个客户端应用程序线程有两个开放式连接。该线程异步启动事务并在第一个连接上发出查询。应用程序随后启动其它事务，在另一个连接上发出查询并等待结果。当 SQL Server 返回其中一个连接的结果时，应用程序开始处理这些结果。应用程序就这样处理结果，直到生成结果的查询被另一个连接上执行的查询阻塞而导致再没有可用的结果为止。此时第一个连接阻塞，无限期等待处理更多的结果。第二个连接没有在锁上阻塞，但仍试图将结果返回给应用程序。然而，由于应用程序阻塞而在第一个连接上等待结果，第二个连接的结果将得不到处理。
避免阻塞方法===============1 .对每个查询使用查询超时。
2 .对每个查询使用锁定超时。有关更多信息，请参见自定义锁超时。 3 .使用绑定连接。有关更多信息，请参见使用绑定连接。
4 .SQL Server 本质上是受客户端应用程序操纵的傀儡。客户端应用程序对服务器上获取的锁几乎有完全的控制（并对锁负责）。虽然 SQL Server
锁管理器自动使用锁保护事务，但这受客户端应用程序发出的查询类型和对结果的处理方式的直接鼓动。因此，大多数阻塞问题的解决方案都涉与检查客户端应用程序。
5 .阻塞问题常要求检查应用程序提交的 SQL 语句本身，以与检查与连接管理、所有结果行的处理等有关的应用程序行为本身。如果开发工具不允许显式控制连接管理、查询超时、结果处理等，阻塞问题可能得不到解决。
设计应用程序以避免阻塞的准则包括===============1. 不要使用或设计使用户得以填写编辑框的应用程序，编辑框会生成长时间运行的查询。例如，不要使用或设计提示用户输入的应用程序，允许某些字段保留空白或允许输入通配符。这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。
2 .不要使用或设计使用户得以在事务输入容的应用程序。 3 .允许取消查询。 4 .使用查询或锁定超时，防止失控查询和避免分布式死锁。 5 .立即完成提取所有结果行。 6 .使事务尽可能简短。 7 .显式控制连接管理。 8 .在所预计的并发用户全负荷下对应用程序进行应力测试。
以下是一些相关的技术文档。Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems<support.microsoft./default.aspx?scid=kb;en-us;224453>
HOW TO: Troubleshoot Application Performance with SQL Server<support.microsoft./default.aspx?scid=kb;en-us;224587>
了解和解决 SQL Server 7.0 或 2000 阻塞问题- -
                                     
本文的发布号曾为 CHS224453
本页容
概要
更多信息
概要
本文是对如下 Microsoft Knowledge Base 文章（针对 SQL Server 6.x）中的 SQLServer 7.0 的更新：
162361INF： 了解和解决 SQLServer 阻塞问题
以上文章所包含的很多信息已经更新，并包括在 SQL Server 7.0 Books Online的“Understanding and Avoiding Blocking”主题中。 继续阅读本文之前，请仔细检查这些信息，本文将不再重复这些容。本文重点讨论如何监视 SQL Server 捕获相关的系统信息，以与如何分析信息以便成功地解决阻塞问题。本文使用的标准术语与上面信息中定义的相同。 在本文的讨论中，术语“连接”是指数据库的单个已登录会话。 每个连接均以一个系统进程 ID(SPID) 出现。 每个 SPID 通常作为一个进程来引用，尽管在一般意义上它不是一个独立的进程环境。 更准确地说，每个 SPID由响应指定客户端请求的单个连接时所需要的服务器资源和数据结构组成。 单个客户端应用程序可能有一个或多个连接。 从 SQL Server的角度看，来自单个客户端计算机的单个客户端应用程序的多个连接，与来自多个客户端应用程序或多个客户端计算机的多个连接之间，没有什么区别。一个连接可以阻塞另一个连接，这与它们是否出自同一个应用程序或出自两个不同的客户端计算机上的独立应用程序无关。
更多信息
阻塞是具有基于锁定的并发特性的任何关系数据库管理系统 (RDBMS) 所不可避免的特征。 在 SQL Server 上，当一个SPID 锁定了一个特定资源，而第二个 SPID 试图获得对同一资源的冲突锁定类型时，就会发生阻塞。 通常，第一个 SPID 锁定资源的时间围非常小。当它释放锁定时，第二个连接即可顺利地获得对资源的自有锁定，并继续下一步操作。 这是正常情况，在一天中可能发生许多次，对系统性能没有任何明显的影响。查询的持续时间和事务环境决定了锁定被占据的时间长短，因而也决定了对其它查询的影响。如果查询不是在事务执行（并且没有使用任何锁定提示），对于SELECT语句引起的锁定，则只有当某个资源被实际读取时才会对该资源锁定，而不会在整个查询持续期间锁定该资源。至于 INSERT、UPDATE 和 DELETE 语句，则在查询期间锁定它们，这既是为了数据的一致性，也是为了允许在必要时进行回滚查询。对于事务执行的查询，决定锁定持续时间长短的因素包括：查询类型、事务隔离级别以与查询中是否使用了锁定提示。有关锁定、锁定提示和事务隔离级别的说明，请参见 SQL Server 7.0 Books Online 中的如下主题：
•
“Understanding Locking in SQL Server”
•
“Locking Architecture”
•
“Lock Compatibility”
•
“Locking Hints”
•
“Changing Default Locking Behavior in Oracle and SQL Server”
当锁定和阻塞增加到对系统性能产生不利影响的时候，通常是由于如下原因所至：
•
一个 SPID 锁定了一组资源，并且在释放锁之前持续了很长一段时间。 这种类型的阻塞在一段时间后自行消失，但会导致性能降低。
•
一个 SPID 锁定了一组资源，并且不再释放它们。 这种类型的阻塞不会自行消失，并且会无限期地阻止对受影响资源的访问。
在上面第一种情况中，一段时间后 SPID 释放锁定时阻塞问题将自行消失。但是，情况可能是变化多端的，因为一段时间后不同的 SPID 会阻塞不同的资源，总是产生变化的目标。 由于这个原因，这些情况很难使用 SQL ServerEnterprise Manager 或单独的 SQL 查询来解决问题。 而第二种情况则产生更便于诊断的持续状态。
收集阻塞信息
要减少解决阻塞问题的难度，数据库管理员可以使用 SQL 脚本连续地监视 SQL Server
上的锁定和阻塞状态。这些脚本可以提供一段时间后指定实例的快照，从而帮助用户全面了解存在的问题。 关于如何用 SQL 脚本监视阻塞的说明，请参见下面的 MicrosoftKnowledge Base 文章：
251004INF： 如何监视 SQLServer 7.0 阻塞
271509INF： 如何监视 SQLServer 2000 阻塞
本文中的脚本将执行下面的任务。 在可能的情况下，我们还提供通过 Enterprise Manager 或特定 SQL查询来获得该信息的方法。
1.
出阻塞链源头的 SPID。除了使用上面提到的脚本外，还可以使用 SQL EnterpriseManager 出阻塞链源头的 SPID，方法如下：
a.
展开服务器组，然后展开服务器。
b.
展开 Management，然后展开 Current Activity。
c.
展开 Locks / Process IDsqltransaction什么意思。 SPID 与其阻塞信息将显示在详细信息窗格中。 正在造成阻塞的 SPID 将显示为“(Blocking)”。
但请注意，有时需要使用脚本查询而不使用 EnterpriseManager，因为某些类型的 tempdb 阻塞问题可能会阻止您运行使用临时表操作的查询。 使用直接查询可以给您提供必要的控制能力，以便避免出现该问题。
2.
查引起阻塞的 SPID 正在运行的查询。脚本方法使用如下查询来确定特定 SPID 发出的命令：
DBCC INPUTBUFFER (<spid>)
另外一种方法，可以按如下步骤使用 SQL Enterprise Manager：
a.
展开服务器组，然后展开服务器。
b.
展开 Management，然后展开 Current Activity。
c.
单击 Process Info。 SPID 将显示在详细信息窗格中。
d.
双击引起阻塞的 SPID 即可看见该 SPID 执行的最后一个 Transact-SQL 命令组。
3.
查引起阻塞的 SPID 当前占用的锁定类型。执行sp_lock系统存储过程，即可到该信息。 另外一种方法，可以按如下步骤使用 Enterprise Manager 获得该信息：
a.
展开服务器组，然后展开服务器。
b.
展开 Management，然后展开 Current Activity。
c.
展开 Locks / Process ID。 SPID 以与正在使用的锁定的相关信息将显示在详细信息窗格中。
4.
查引起阻塞的 SPID 的事务嵌套级别和进程状态。SPID 的事务嵌套级别可以在 TRANCOUNT全局变量中到。 但是，通过按如下方式查询sysprocesses表，可以从 SPID 以外到它。
SELECT open_tran FROM SYSPROCESSES WHERE SPID=<blocking SPID number>
go
返回的值为该 SPID 的 TRANCOUNT 值。 该值显示阻塞 SPID 的事务嵌套级别，反过来可以解释为什么 SPID 正在使用锁定。 例如，如果该值大于零，则 SPID 位于事务中间（这种情况下，根据事务隔离级别，可以预计它将保持已经获得的某些锁定）。

要查看数据库中是否存在任何长时间打开的事务，还可以使用 DBCC OPENTRAN database_name。
收集 SQL Server 事件探查器跟踪信息

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。