一个特殊的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