寻找阻塞的原因，解决阻塞问题

来源：互联网发布：apache ab源码编辑：程序博客网时间：2024/04/30 07:05

http://bvbook.javaeye.com/blog/429579

寻找阻塞的原因，解决阻塞问题

本文节选自《Microsoft SQL Server 2005技术内幕：查询、调整和优化》第6章“并发问题”

6.3.2 寻找阻塞的原因

基于锁的阻塞由锁定资源的进程冲突引起。只知道锁是不够的，为了找到问题的根源，我们需要知道涉及到的查询。一旦识别了session id或spid，我们就可以跟踪这些查询。SQL Server 2005能得到被阻塞的实际任务，可以获取比以前的版本更多的信息。

使用被阻塞进程报告

被阻塞进程报告TextData列的XML输出会显示阻塞和被阻塞session的查询文本。目前是查询最方便的途径，但对使用SQL Trace来说它并不是最方便的。那样的话，我们可以连接sys.dm_os_waiting_tasks和sys.dm_tran_locks这两个DMV，通过一些额外的工作，提取与它们相关的查询。

连接sys.dm_os_waiting_tasks和sys.dm_tran_locks

分两步完成这个任务。首先，建立两个DMV的连接，从它们中得到最佳的信息。然后，添加子查询来提取查询文本。可用join on等待任务的resource_address和lock_owner_address，其中resource_address属于sys.dm_os_waiting_tasks，lock_owner_address属于sys.dm_tran_locks。示例如下：

SELECT

WT.session_id AS waiting_session_id,

DB_NAME(TL.resource_database_id) AS DatabaseName,

WT.wait_duration_ms,

WT.waiting_task_address,

TL.request_mode,

TL.resource_type,

TL.resource_associated_entity_id,

TL.resource_description AS lock_resource_description,

WT.wait_type,

WT.blocking_session_id,

WT.resource_description AS blocking_resource_description

FROM sys.dm_os_waiting_tasks AS WT

JOIN sys.dm_tran_locks AS TL

ON WT.resource_address = TL.lock_owner_address

WHERE WT.wait_duration_ms > 5000

AND WT.session_id > 50;

上面的查询中，结果是成对的。将sys.dm_os_waiting_tasks当作表，列出了等待session及其任务信息，然后添加了sys.dm_tran_locks，等待任务的锁定信息。最后的列包含阻塞session信息，也来自于sys.dm_os_waiting_tasks。

现在是第二步：获取每个session的实际查询文本。通过添加连接sys.dm_exec_requests DMV和sys.dm_exec_sql_text() DMF的子查询，关联回到加入等待任务的session_id，我们可以提取查询文本。下面是一个实例，改编自《一种解决性能问题的方法》第1章中一个类似的查询。

SELECT

WT.session_id AS waiting_session_id,

DB_NAME(TL.resource_database_id) AS DatabaseName,

WT.wait_duration_ms,

WT.waiting_task_address,

TL.request_mode,

(SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,

((CASE ER.statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE ER.statement_end_offset

END - ER.statement_start_offset)/2) + 1)

FROM sys.dm_exec_requests AS ER

CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

WHERE ER.session_id = TL.request_session_id)

AS waiting_query_text,

TL.resource_type,

TL.resource_associated_entity_id,

WT.wait_type,

WT.blocking_session_id,

WT.resource_description AS blocking_resource_description,

CASE WHEN WT.blocking_session_id > 0 THEN

(SELECT ST2.text FROM sys.sysprocesses AS SP

CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2

WHERE SP.spid = WT.blocking_session_id)

ELSE NULL

END AS blocking_query_text

FROM sys.dm_os_waiting_tasks AS WT

JOIN sys.dm_tran_locks AS TL

ON WT.resource_address = TL.lock_owner_address

WHERE WT.wait_duration_ms > 5000

AND WT.session_id > 50;

请注意这两个子查询：为了阻塞查询和被阻塞查询而检索查询文本。第一个子查询检索与等待任务关联的查询文本，这些等待任务使用和SQL Server 2005在线帮助中描述的sys.dm_exec_sql_text逻辑一样。通过使用tatement_starting_offset值和statement_end_offset值的SUBSTRING

函数，查询返回和等待任务关联的准确查询文本。

为了得到阻塞任务的查询，我们首先需要检查blocking_session_id是否为非空非负数。（本章前面讨论了blocking_session_id的3个负值。）如果blocking_session_id为正数，我们还可以使用主数据库进程视图中的sql_handle

，它会使其返回整个批次。

一旦知道了所有涉及的查询、被锁定的资源和涉及的锁定类型，我们就可以定位如何解决阻塞问题。

6.3.3 解决阻塞问题

如前面所述，当我们发现了基于锁的阻塞问题后知道，阻塞可能由以下原因引起：写操作阻塞写操作、读操作阻塞写操作和写操作阻塞读操作。每种阻塞类型都有其自身的潜在解决方案。

杀死Session

解决阻塞最简单也是最满意的方法是使用KILL命令杀死一个session或spid。有时候在紧急情况下这也是最佳方案，在产品系统中找到提交非法查询的人比较寻常，因此可以安全地杀死它。但是如果查询都是合法的，杀死其中一个session可能对访问数据库的应用程序造成意外的危害。

当阻塞产生于查询之中，所有的查询都是合法的，我们需要通过查看阻塞的根源来进一步寻找长期的解决方案。如果一个特殊的阻塞进程是长时间运行的更新或删除进程，杀死session会导致回滚事务，直到回滚完成锁不会被释放；杀死上下文中的spid，直到回滚完成阻塞不会解除。

解决写/写阻塞

在SQL Server中没有多少解决写/写阻塞的选项，因为独占锁总是在修改数据时被session请求。当两个session都需要修改同一个数据时，两个session都需要获得同样资源的独占锁，这样的话，我们就需要重写事务或修改他们的运行方式，以此来避免阻塞问题。建议如下：

n 使写事务短一些。解决写操作阻塞写操作最有效的方法之一就是使事务短一些。事务中需要的独占锁被持有至事务结束，由此减少了事务的持续时间，就可以减少独占锁需要的时间。

n 减少写操作使用锁的数量。我们可以使用同样的技术来降低锁的数量。例如，可以减少批更新进程修改的行的数目，进而更少的行在给定时间内被修改（插入、更新或删除），因而更少的行需要被锁定，需要减少或去掉锁升级。我们还可以试着分离有冲突的写操作，通过在不同的计划时间内运行它们，如将大型加载操作转移到一个系统使用少的期间。

解决读/写阻塞

SQL Server中解决读/写阻塞的选项更多一些，主要因为我们可以调整涉及事务的隔离级别，减少或去掉读操作需要的共享锁。建议如下：

n 降低隔离级别到READ UNCOMMITTED。解决读/写阻塞最常用的方法之一是降低隔离级别，不管是通过设置读操作的隔离级别为READ UNCOMMITTED，还是在读操作查询上设置NOLOCK提示，都会导致SELECT语句读取没有提交的数据。直到SQL Server 2005的出现，它是解决读/写阻塞的唯一有效方法，但它是有风险的。
首先，NOLOCK/READ UNCOMMITTED的好处在于SELECT语句中不使用任何共享锁，有读取还未提交数据的风险。在一个很少或没有事务回滚的系统中，这也说明风险很低。即使这样，查询可能从标题表中读取一个新插入的行，但没有看到任何具体的还未插入的表格行。对于一个重要的查询，它必须返回基于一致的已提交数据的正确汇总或计算结果，读取未提交的数据是不被接受的。

使用NOLOCK提示或READ UNCOMMITTED隔离级别的语句有失败的小风险。这会发生于SQL Server在读一个已经被删除的页时，因缺少已连接列表和读取请求页的正确连接。这种情况发生时，我们会看到错误601，而且查询也会被中止。错误601的内容是：

Could not continue scan with NOLOCK due to data movement.

我们并不会经常看到这个错误，但如果应用程序使用READ UNCOMMITTED的NOLOCK提示，就应该测试它，并且当错误发生时还应重新提交查询。

这不是NOLOCK唯一的问题。在线教程指出过（参见主题“缺少被更新的行或者两次看到被更新的行”），Lubor Kollar也曾指出过（在他的SQL Server用户咨询小组日志中），SELECT语句可以使用NOLOCK提示或READ UNCOMMITTED隔离级别来跳过某些行，这些行是在执行SELECT时进行页拆分的结果。这会发生在查询分析器选择一个分配扫描器为SELECT语句扫描表格的时候，页拆分发生于将新页插入到分配表较早的部分。SELECT语句只读取分配表之前的，所以会缺少这些页拆分。反过来，由于页拆分，SELECT语句可能将这些行读两遍（参见参考书目“其他资源和引用”中的Lubor Kollar日志）。

n 检查正确的隔离级别。我们可以看到在不需要的地方使用REPEATABLE READ或SERIALIZABLE隔离级别的事务。将这些隔离级别降低到默认的READ COMMITTED级别，可以使SQL Server在事务结束前释放共享锁，且持有较短的时间。“被阻塞进程报告”XML输出文件的隔离级别的列会说明每个事务的隔离级别。阻塞中涉及的范围锁定表明事务正使用SERIALIZABLE隔离级别。在某些情况下，开发人员会误用这个隔离级别。

n 在SQL Server 2005上使用基于行版本的快照隔离级别，可以避免使用一个或两个基于快照的隔离级别时与读取未提交的数据关联的问题。目前最有效的方法是修改默认的READ COMMITTED隔离级别的工作方式，通过设置READ_COMMITTED_ SNAPSHOT的值为ON来修改数据库。这改变了SELECT语句读取已提交数据的方式，也替代了使用共享锁的方式。它们会读取数据的前一个版本，这些数据在SELECT语句执行开始时或者稍后，被事务在SELECT语句运行时间内修改了。当选项打开时，所有的DML事务（修改数据的）会造成行版本的变化；所有在READ COMMITTED隔离级别下运行的SELECT语句可能会读取变形的数据。这个选项的成本是tempdb中增加的活动，版本库位于tempdb中，检索特定行合适版本的遍历指针的成本也一样。此外，如果这些触发器只依赖于读取当前数据，已提交的读快照和触发器还有一些潜在的问题，在“解决基于行版本的基于快照的隔离级别问题”中有相关讨论。

n 将读操作从写操作中分离出来。为了得到长期的解决方案，我们可能需要考虑完全将冲突的读查询从写查询中分离出来。有时候这被称为“从写操作中分离读操作”，但那可能过于简化了，因为，通常只有所有读查询的一个子集可以重定向到数据库的只读拷贝。通常即使在最活跃的OLTP数据库中，读活动的数量也远远超过写活动的数量。这些读操作大多数会读取另一个服务器或数据库中的数据。在主服务器之外再创建一个报告服务器，通过事务复制来传送数据（举例来说），这会去掉许多有冲突的读查询。不幸的是，那些相同的查询尝试锁定复制存储过程中正在更新的相同数据，因此，为了消除冲突，我们需要在数据库用户上应用READ_COMMITTED_SNAPHOT。

另一种从读操作中分离写操作的方法是，在当前数据库实例中创建一个数据库快照，一个有当前特定时间数据的快照。SELECT语句运行在数据库快照（只读的）上，不会有在可读写数据库上同样多的共享锁。

有时，冲突最多的读查询是那些需要最新数据的查询，因此要从写操作中分离有冲突的读操作是不可能的。那样的话，READ_COMMITTED_SNAPSHOT确实是最佳选项。

《Microsoft SQL Server 2005技术内幕》系列丛书，微软官方权威参考书！