欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-28 20:41:47
上周,在SQL Server数据库下面遇到了一个有意思的SQL阻塞(SQL Blocking)案例。其实个人对SQL Server的阻塞还是颇有研究的。写过好几篇相关文章。 至于这里为什么要总结一下这个案例,因为这个案例有点意思: 1:使用DMV视图捕获到的Blocking SQL为一个查询语句(这... ......

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

 

 

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

 

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

 

 

 

 

 

我们知道,在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查询。

 

 

 

如上测试所示,因为是构造模拟案例,开启了上帝视角,所以你不会觉得有什么问题,实际情况是:应用程序是一个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语句如下所示

 

 

 

 

<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 profiler跟踪某个登录名(login name)一小段时间,根据会话id一段时间内执行过的sql来推理、判断定位阻塞的源头,从而找到具体原因,这个例子里面,orm框架开启了隐性事务,在sql server profiler里面捕获到开启隐性事务以及update操作。至此。问题终于解开了谜底。当然本文不是按照当时的逻辑来写的,而是在知道原因后,模拟构造案例重新的。所以总感觉字里行间写不出那种味道。

 

set implicit_transactions  on;

 

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

 

if @@trancount > 0 commit tran set implicit_transactions off