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

上周，在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