Blocked Process Report

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

当同个对象上有互斥的锁存在时,查询需要等待很长时间,我们是否可以收到来自SQL Server提醒?答案是可以的,做法非常简单,因为SQL Server为你提供了称为Blocked Process Report 的功能。很可惜这个功能默认是未启用的。因此这篇文章我想给你讲解下Blocked Process Report 的大致用法,当SQL Server出现锁时,你可以用它来做的进一步的故障排除。

Blocked Process Report本身是个简单的事件,你可以通过SQL Server Profiler或SQL Traces跟踪。这个事件被称为Blocked Process Report,你可以在Errors and Warnings事件组里找到它:

 但那个事件只被突出(跟踪),当你通过sp_configure选项指定blocked process threshold阀值在SQL Server实例级别启用Blocked Process Report 功能时。那个参数只接收0到86400的数字,查询必须要等到锁持久的秒数后,SQL Server才会生成Blocked Process Report事件。默认情况下那个配置选项值为0,就是这个事件不会触发。下面的代码设置阀值为10秒钟:

1 sp_configure 'blocked process threshold', 10
2 RECONFIGURE
3 GO

为了演示Blocked Process Report我通过一个UPDATE语句在AdventureWorks2008R2数据库内部创建一个新的事务:

复制代码
1 BEGIN TRANSACTION
2 
3 UPDATE Person.Person
4 SET Title = 'Mr'
5 WHERE BusinessEntityID = 1
复制代码

在这个语句执行后,这个查询现在需要在列BusinessEntityID值等于1的记录上获得一个排它锁(Exclusive Lock (X))。在第2个会话现在我尝试读取同样的记录。在读取期间,SQL Server尝试获取一个共享锁(Shared Lock (S)),这就导致了阻塞情形: 

1 SELECT * FROM Person.Person
2 WHERE BusinessEntityID = 1
3 GO

当你启动SQL Server Profiler且配置了Blocked Process Report事件,在10秒后,你就会看这个被报告:

 从上图可以看到,Blocked Process Report 本身就是一个XML数据,因此很容易对它进一步分析,如果你对XML和XQuery熟悉的话。

复制代码
 1 <blocked-process-report>
 2  <blocked-process>
 3   <process id="process35ab1c8" taskpriority="0" logused="0" waitresource="KEY: 11:72057594045333504 (8194443284a0)" waittime="16986" ownerId="49004" transactionname="SELECT" lasttranstarted="2015-06-23T14:09:38.900" XDES="0x5f02138" lockMode="S" schedulerid="1" kpid="8512" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-06-23T14:09:38.900" lastbatchcompleted="2015-06-23T14:09:10.877" lastattention="2015-06-23T14:09:10.877" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="WXGFZCXXZX81-18" hostpid="4492" loginname="sa" isolationlevel="read committed (2)" xactid="49004" currentdb="11" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
 4    <executionStack>
 5     <frame line="1" stmtstart="24" sqlhandle="0x020000006063873a3a5f7e72ad0b55e66df822bf70e6f14c"/>
 6     <frame line="1" sqlhandle="0x0200000066bba411d9c6966611de8194e81441d7836a9554"/>
 7    </executionStack>
 8    <inputbuf>
 9 SELECT * FROM Person.Person
10 WHERE BusinessEntityID = 1
11    </inputbuf>
12   </process>
13  </blocked-process>
14  <blocking-process>
15   <process status="sleeping" spid="57" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2015-06-23T14:09:36.050" lastbatchcompleted="2015-06-23T14:09:36.050" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="WXGFZCXXZX81-18" hostpid="4492" loginname="sa" isolationlevel="read committed (2)" xactid="47048" currentdb="11" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
16    <executionStack/>
17    <inputbuf>
18 BEGIN TRANSACTION
19 
20 UPDATE Person.Person
21 SET Title = &apos;Mr&apos;
22 WHERE BusinessEntityID = 1   </inputbuf>
23   </process>
24  </blocking-process>
25 </blocked-process-report>
复制代码

XML数据里有2个重要节点– <blocked-process> 和<blocking-process>。第1个– <blocked-process>描述阻塞的会话。在这里是对AdventureWorks2008R2执行SELECT语句查询的会话。这里最重要的是waitresource的XML属性,它包含会话上等待的锁资源,超过了blocked process threshold 配置选项

第2个<blocking-process>描述当前在资源上持互斥锁的会话,在这个资源上其他会话需要获取锁。这里最重要的是<inputbuf>的XML元素,它显示了SQL 语句需要的互斥锁。手上有了这些信息,就很容易进一步进行故障排除,就是为什么阻塞阀值被超出,还有下一步对它如何处理(这里的会话是一个孤立的事务(orphaned transaction),杀掉了其他会话)。

当你使用Blocked Process Report时,你需要记住的最重要的是,SQL Server值为你生成对应的XML报告,不会为你解决锁/死锁。这就是,在生成Blocked Process Report后,SELECT语句的会话还是继续运行——SQL Server这里不会杀掉这个会话——SQL Server只报告有个会话超过了blocked process threshold——剩下的一切都还是要你自己去处理。


本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4595137.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
机器学习/深度学习 TensorFlow 算法框架/工具
Gaussian Process
【6月更文挑战第14天】
38 4
|
10月前
|
前端开发
The following tasks did not complete: first Did you forget to signal async completion?
The following tasks did not complete: first Did you forget to signal async completion?
|
安全 Windows
CRITICAL_PROCESS_DIED
CRITICAL_PROCESS_DIED
3903 2
|
关系型数据库 Java MySQL
|
.NET Windows 开发框架