一个特殊的SQL Server阻塞案例分析 |
您所在的位置:网站首页 › 线程阻塞状态实例分析 › 一个特殊的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);GOINSERT INTO TEST VALUES(1);
然后再通过2个会话,模拟这样的阻塞案例,如下所示:
会话A: SET IMPLICIT_TRANSACTIONS ON;GOINSERT INTO TEST VALUES(2)GOSELECT * 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 BlockingSQLTextFROM sys.dm_tran_locks AS tl WITH(NOLOCK)INNER JOIN sys.databases AS db WITH(NOLOCK) ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK) ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.dm_exec_connections ec1 WITH(NOLOCK) ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 WITH(NOLOCK) ON ec2.session_id = wt.blocking_session_idLEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK) ON SP.spid = wt.blocking_session_idCROSS 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语句,如下所示:
SELECT * FROM TEST WHERE ID =2; SELECT * FROM TEST WHERE ID =1;
最后,只能使用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 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |