一个特殊的SQL Server阻塞案例分析

栏目: 数据库 · 发布时间: 5年前

内容简介:上周,在SQL Server数据库下面遇到了一个有意思的SQL阻塞(SQL Blocking)案例。其实个人对SQL Server的阻塞还是颇有研究的。写过好几篇相关文章。 至于这里为什么要总结一下这个案例,因为这个案例有点意思:1:使用DMV视图捕获到的Blocking SQL为一个查询语句(这个不是真正引起阻塞的源头SQL语句),等待事件为LCK_M_S。2:出现阻塞的会话非常多,阻塞的量非常大,使用DMV视图始终捕获不到相关表的DML操作语句。捕获到全是SELECT语句。

上周,在SQL Server数据库下面遇到了一个有意思的 SQL 阻塞(SQL Blocking)案例。其实个人对SQL Server的阻塞还是颇有研究的。写过好几篇相关文章。 至于这里为什么要总结一下这个案例,因为这个案例有点意思:

1:使用DMV视图捕获到的Blocking SQL为一个查询语句(这个不是真正引起阻塞的源头SQL语句),等待事件为LCK_M_S。

2:出现阻塞的会话非常多,阻塞的量非常大,使用DMV视图始终捕获不到相关表的DML操作语句。捕获到全是SELECT语句。

一个特殊的SQL Server阻塞案例分析

我们知道,在SQL Server中SELECT查询是不会阻塞SELECT查询的(不了解这个问题的,具体可以参考博客 SQL Server中SELECT会真的阻塞SELECT吗? ) 。而且被阻塞的语句的等待类型为LCK_M_S,那么可以肯定:那个阻塞的源头会话中,存在对该表的DML操作,而且事务由于某些原因未提交。只是那个会话最后执行的SQL语句为一个SELECT查询。因为有时候,我们根本不能定位到SQL阻塞的源头SQL语句,具体参考我的博客 为什么数据库有时候不能定位阻塞(Blocker)源头的SQL语句 ,此处不做重复赘述。

我们用一个简单的例子来模拟这个真实的案例,如需所示,首先创建一个测试表:

CREATE TABLE TEST (id    INT);
GO
INSERT INTO TEST VALUES(1);

然后再通过 2 个会话,模拟这样的阻塞案例,如下所示:

会话 A

SET IMPLICIT_TRANSACTIONS ON;
GO
INSERT INTO TEST VALUES(2)
GO
SELECT  * FROM TEST WHERE ID =1;

注意:先设置开启隐式事务,第一次执行插入数据语句,然后执行SELECT查询。

   会话 B

SELECT * FROM TEST WHERE id=1;

   会话C:

SELECT wt.blocking_session_id                    AS BlockingSessesionId
      ,sp.program_name                        AS Blocking_ProgramName
      ,COALESCE(sp.LOGINAME, sp.nt_username)    AS Blocking_HostName    
      ,ec1.client_net_address                    AS ClientIpAddress
      ,db.name                                AS DatabaseName        
      ,wt.wait_type                            AS WaitType                    
      ,ec1.connect_time                        AS BlockingStartTime
      ,wt.WAIT_DURATION_MS/1000                AS WaitDuration
      ,ec1.session_id                            AS BlockedSessionId
      ,h1.TEXT                                AS BlockedSQLText
      ,h2.TEXT                                AS BlockingSQLText
FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db  WITH(NOLOCK)
  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
  ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
  ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 

在会话C中查看SQL阻塞情况,如下截图所示,我们看到是SELECT查询阻塞了SELECT查询。

一个特殊的SQL Server阻塞案例分析

如上测试所示,因为是构造模拟案例,开启了 上帝视角 ,所以你不会觉得有什么问题,实际情况是:应用程序是一个 Java 应用程序,而且是O/R Mapping框架(com.j256.ormlite),我将上面情况反馈给开发、Support人员,明确告诉他们阻塞的会话肯定有一个DML操作。让他们查找定位代码。但是诸多原因、因素叠加在一起(外包项目;沟通问题;对数据库的阻塞的的理解),沟通的效果让人很是无语。只能拿出确切的证据。由于那个框架开启了隐性事务(事后跟踪、分析发现的),而且UPDATE语句非常快,你使用DMV视图定位到的阻塞源头都是SELECT语句。显然这个不是我想要的。

于是,我打算使用SQL Server Profiler里面的 Blocked process report 事件来定位阻塞的源头,在跟踪之前,修改'blocked process threshold (s)'的值。如下所示

exec sp_configure 'show advanced options',1;
 
reconfigure with override
 
exec sp_configure 'blocked process threshold (s)',4
 
reconfigure with override

然后设置 SQL Server Profiler 的相关选项和过滤条件。就像我下面的测试的一样 Blocked process report 依然无法定位到阻塞的源头 SQL 语句 如下所示

一个特殊的SQL Server阻塞案例分析

<blocked-process-report monitorLoop="3262026">
 <blocked-process>
  <process id="process1f9b22ca8" taskpriority="0" logused="0" waitresource="RID: 1:1:574:1" waittime="10174" ownerId="2680347313" transactionname="SELECT" lasttranstarted="2018-11-26T14:09:55.130" XDES="0x123391a20" lockMode="S" schedulerid="2" kpid="8604" status="suspended" spid="104" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-11-26T14:09:55.120" lastbatchcompleted="2018-11-26T14:09:37.887" lastattention="1900-01-01T00:00:00.887" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GETNB00021" hostpid="10560" loginname="xxxx" isolationlevel="read committed (2)" xactid="2680347313" currentdb="1" lockTimeout="4294967295" clientoption1="671091040" clientoption2="390200">
   <executionStack>
    <frame line="1" stmtstart="24" stmtend="90" sqlhandle="0x02000000d8cd0821e4e62d2f8f098831e46f98ca20ba31ae0000000000000000000000000000000000000000"/>
    <frame line="1" stmtend="60" sqlhandle="0x0200000012eff610783e49a674c5361fd9c8bb38747a966a0000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
SELECT  * FROM TEST WHERE ID =2;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="89" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-11-26T14:09:52.927" lastbatchcompleted="2018-11-26T14:09:52.927" lastattention="1900-01-01T00:00:00.927" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GETNB00021" hostpid="10560" loginname="xxxx" isolationlevel="read committed (2)" xactid="2680346553" currentdb="1" lockTimeout="4294967295" clientoption1="671091040" clientoption2="390202">
   <executionStack/>
   <inputbuf>
SELECT  * FROM TEST WHERE ID =1;
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

一个特殊的SQL Server阻塞案例分析

最后,只能使用SQL Server Profiler跟踪某个登录名(Login Name)一小段时间,根据会话ID一段时间内执行过的SQL来推理、判断定位阻塞的源头,从而找到具体原因,这个例子里面,ORM框架开启了隐性事务,在SQL Server Profiler里面捕获到开启隐性事务以及UPDATE操作。至此。问题终于解开了谜底。当然本文不是按照当时的逻辑来写的,而是在知道原因后,模拟构造案例重新的。所以总感觉字里行间写不出那种味道。

SET IMPLICIT_TRANSACTIONS   ON;

.............

IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Servlet与JSP核心编程

Servlet与JSP核心编程

(美国)霍尔等著、赵学良译 / 霍尔 / 清华大学出版社 / 2004-06-01 / 59.0

《Servlet与JSP核心编程》(第2版)叙述详尽,条理清晰。对于初学者来说是一本不可多得的入门书籍,经验丰富的Servelet和JSP开发人员也可以通过阅读《Servlet与JSP核心编程》(第2版)得到巩固和提高。一起来看看 《Servlet与JSP核心编程》 这本书的介绍吧!

SHA 加密
SHA 加密

SHA 加密工具

html转js在线工具
html转js在线工具

html转js在线工具

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具