首页 > 代码库 > 查找阻塞语句
查找阻塞语句
此篇扩展随笔事务隔离级别与阻塞中的例子
按照会话1->会话2的顺序执行，会话1(spid=53)开启事务更新数据尚未提交
--会话1开启事务更新数据尚未提交USE AdventureWorks2008;GOBEGIN TRANSACTION; -- 修改1 -- 休假时间减8 UPDATE HumanResources.Employee SET VacationHours = VacationHours - 8 WHERE BusinessEntityID = 4;
会话2(spid=54)读取会话1中修改的行
--会话2读取会话1中修改的行USE AdventureWorks2008;GOBEGIN TRANSACTION; -- 查询1 -- 这个查询会被会话1阻塞 SELECT BusinessEntityID, VacationHours FROM HumanResources.Employee WHERE BusinessEntityID = 4;
查看两个会话的连接信息
select spid,kpid,blocked,waittime,lastwaittype,waitresource,dbid,login_time,last_batch,open_tran,status,loginamefrom sys.sysprocesses where spid in(53,54)select session_id,most_recent_session_id,connect_time,last_read,last_write,client_net_addressfrom sys.dm_exec_connections where session_id in(53,54)
会话1(spid=53)在2016-11-11 17:09:30建立连接，最后一次读(last_read)是2016-11-11 17:41:45
会话2(spid=54)在2016-11-11 17:28:14建立连接，最后一次读(last_read)是2016-11-11 17:42:27
实际情况是会话1、会话2在分别在2016-11-11 17:09:30、2016-11-11 17:28:14建立连接；会话1在2016-11-11 17:41:45执行更新操作，会话2在2016-11-11 17:42:27执行查询操作，会话1会阻塞会话2。
注意，上图中同一会话sys.sysprocesses的last_batch与sys.dm_exec_connections的last_read看似非常接近，但如果会话2中没有GO关键字，在阻塞的某个点取消执行查询，等上一分钟再次执行会话2的语句，就会出现last_batch是取消执行查询的时间点，last_read是一分钟后的时间。如果会话2使用下面的语句
select top 1 * from AdventureWorks2008.dbo.DatabaseLog--USE AdventureWorks2008R2;--GO--BEGIN TRANSACTION; -- 查询1 -- 这个查询会被会话1阻塞 SELECT BusinessEntityID, VacationHours FROM AdventureWorks2008.HumanResources.Employee WHERE BusinessEntityID = 4;
请问会话2能返回DatabaseLog中的一条数据吗？答案是不能。这里不展开讨论，有兴趣的可自行测试。
针对开始的会话2语句，可用下面语句查看阻塞信息
SELECT ec1.session_id AS BlockedSessionId ,db.name AS DatabaseName ,wt.wait_type AS WaitType ,ec1.last_read AS BlockedTime ,wt.wait_duration_ms/1000 AS [WaitDuration(s)] ,ec1.client_net_address AS BlockedClientAddress ,h1.text AS BlockedSQLText ,wt.blocking_session_id AS BlockingSessionId ,h2.text AS BlockingSQLText ,sp.program_name AS BlockingProgramName ,COALESCE(sp.loginame, sp.nt_username) AS BlockingLoginame FROM 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 wt.resource_address = tl.lock_owner_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 h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
Blocked*代表被阻塞的连接，Blocking*代表阻塞的"源头"。阻塞发生都是一个会话(spid=53)先执行，另一个会话(spid=54)后执行，阻塞发生的时间(BlockedTime)应该理解成后执行的会话的last_read时间。因为会话spid54的连接在被阻塞之前就已经创建(connect_time)，并且还有可能执行过其他语句。
完成上面操作后，我们回滚会话1和会话2中的事务。然后按照会话2->会话1的顺序执行
会话2(spid=54)修改隔离级别可重复读，开启事务读取数据
--会话2在可重复读下读取数据set transaction isolation level repeatable readgoUSE AdventureWorks2008;GOBEGIN TRANSACTION; -- 查询2 -- 休假时间为48 SELECT BusinessEntityID, VacationHours FROM HumanResources.Employee WHERE BusinessEntityID = 4;
会话1(spid=53)开启事务更新会话2中读取的行
--会话1开启事务更新会话2中读取的行USE AdventureWorks2008;GOBEGIN TRANSACTION; -- 修改2 -- 这个更新会被会话2阻塞 UPDATE HumanResources.Employee SET VacationHours = VacationHours - 8 WHERE BusinessEntityID = 4;
查看两个会话连接信息
会话1(spid=53)在2016-11-11 17:09:30建立连接，最后一次读(last_read)是2016-11-11 18:05:56
会话2(spid=54)在2016-11-11 17:28:14建立连接，最后一次读(last_read)是2016-11-11 18:04:14
实际情况是会话1、会话2在分别在2016-11-11 17:09:30、2016-11-11 17:28:14建立连接；会话2在2016-11-11 18:04:14在可重复读隔离级别下执行查询操作，会话1在2016-11-11 18:05:56执行更新操作，会话2会阻塞会话1。
此时可用下面语句查看阻塞信息，此语句参考SQL Server 监控统计阻塞脚本信息修改
SELECT ec1.session_id AS BlockedSessionId ,db.name AS DatabaseName ,wt.wait_type AS WaitType ,ec1.last_read AS BlockedTime ,wt.wait_duration_ms/1000 AS [WaitDuration(s)] ,ec1.client_net_address AS BlockedClientAddress ,h1.text AS BlockedSQLText ,wt.blocking_session_id AS BlockingSessionId ,h2.text AS BlockingSQLText ,sp.program_name AS BlockingProgramName ,COALESCE(sp.loginame, sp.nt_username) AS BlockingLoginame FROM 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 wt.resource_address = tl.lock_owner_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 h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
阻塞的"源头"并没有体现出事务隔离级别，如何获取连接的事务隔离级别。sys.dm_exec_requests中的transaction_isolation_level只对正在运行连接有效，sleeping状态根本无法查询。而DBCC USEROPTIONS要到对应会话上执行才会返回结果，如果是客户端的连接呢？难道只能等用户再次执行语句时才能捕获到其隔离级别？
查找阻塞语句