侦测阻塞语句

前言：

如果一个事务正在等待一些给其他事务锁定的资源。这个事务就被成为“被阻塞的事务”。反过来，引起阻塞的事务，也就是锁定资源并造成其他事务等待的事务叫做“正在阻塞的事务”。

长时间运行事务会阻塞其他事务和查询，使他们等待长时间。在繁重的系统中，很多时候我们会遇到阻塞问题，如果一个事务因为阻塞未完成。会造成一些列的等待链。

本文将介绍如何发现并马上解决这方面的问题。

 

准备工作：

本例依旧使用SQLServer2012上的AdventureWorks2012数据库。

 

步骤：

1、 连到SQLServer2012的AdventureWorks2012数据库。

2、 新建窗口并输入：

[sql] view plaincopyprint?

1. USE AdventureWorks2012  
2. GO  
3. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
4. GO  
5.   
6. --开启事务  
7. BEGIN TRANSACTION  
8.   
9. --获取会话ID  
10. SELECT  @@SPID AS Connection1_SessionID  
11.   
12. SELECT  *  
13. FROM    Sales.SalesOrderDetail  
14. WHERE   SalesOrderDetailID = 121316  

3、 执行完之后,截图如下:

4、 新开另外一个窗口，输入下面代码去开启另外一个事务，留意UPDATE语句，将不会执行，因为在等待第二步中的事务：

[sql] view plaincopyprint?

1. USE AdventureWorks2012  
2. GO  
3.   
4. --开启事务  
5. BEGIN TRANSACTION  
6.   
7. UPDATE  Sales.SalesOrderDetail  
8. SET     OrderQty = 10  
9. WHERE   SalesOrderDetailID = 121316  
10.   
11. COMMIT TRANSACTION  

5、 再开启一个事务，输入以下代码查询被阻塞和正在阻塞的查询： 

[sql] view plaincopyprint?

1. SELECT  R.session_id AS BlockedSessionID ,  
2.         S.session_id AS BlockingSessionID ,  
3.         Q1.text AS BlockedSession_TSQL ,  
4.         Q2.text AS BlockingSession_TSQL ,  
5.         C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,  
6.         C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,  
7.         S.original_login_name AS BlockingSession_LoginName ,  
8.         S.program_name AS BlockingSession_ApplicationName ,  
9.         S.host_name AS BlockingSession_HostName  
10. FROM    sys.dm_exec_requests AS R  
11.         INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id  
12.         INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id  
13.         INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id  
14.         CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1  
15.         CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2  

--为了方面了解查询出来的字段含义，个人在原来语句基础上稍微改动了下：

SELECT R.blocking_session_id 阻塞会话进程ID,
  R.session_id 被阻塞会话进程ID,
  t1.text 阻塞进程SQL语句,
  T2.text 被阻塞进程SQL语句,
  s.host_name 阻塞进程主机名 ,
  s.login_name 阻塞进程登录名,
  S2.host_name 被阻塞进程主机名,
  s2.login_name 被阻塞进程登录名
FROM SYS.dm_exec_requests R
INNER JOIN SYS.dm_exec_sessions S ON R.blocking_session_id=S.session_id
INNER JOIN SYS.dm_exec_connections C ON S.session_id=C.most_recent_session_id
CROSS APPLY SYS.dm_exec_sql_text(C.MOST_RECENT_SQL_HANDLE) T1
INNER JOIN SYS.dm_exec_connections C2 ON C2.MOST_RECENT_SESSION_ID=R.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C2.MOST_RECENT_SQL_HANDLE) T2
INNER JOIN SYS.dm_exec_sessions S2 ON R.session_id=S2.session_id

--cross applay若看不懂的话，请参考：http://blog.csdn.net/yabingshi_tech/article/details/24399243

6、 因为第一个连接占用了资源，阻塞了其他事务，所以这里要结束这个进程：

[sql] view plaincopyprint?

1. KILL 68  
2. GO  

7、 换回第二个查询界面，发现update操作已经成功完成。上面的进程号根据不同机器而定。

分析：

在本例中，把事务隔离级别设为REPEATABLE READ，因为在这个隔离级别中，在资源上的共享锁将持续到事务完成。所以当从表中查找数据是，该值上会加上共享锁。在事务提交或回滚前不会释放。

当执行第二个连接的update语句时，不能完成，因为被第一个事务阻塞了，且在REPEATABLE READ下共享锁不释放。

为了标识阻塞和被阻塞的请求，需要用到下面的DMO：

1、 dm_exec_requests

返回有关在 SQL Server 中执行的每个请求的信息。

可以看到阻塞进程相关信息。

2、 dm_exec_sessions

sys.dm_exec_sessions 是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。

 此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。

使用 sys.dm_exec_sessions，首先可以查看当前的系统负荷并标识相关会话，然后可以通过其他动态管理视图或动态管理函数了解有关该会话的详细信息。

3、 dm_exec_connections

返回有关与此 SQL Server 实例建立的连接的信息以及每个连接的详细信息。

4、 dm_exec_sql_text

返回由指定的 sql_handle 标识的 SQL 批处理的文本

本篇文章转自：http://blog.csdn.net/dba_huangzj/article/details/8697578