查看活动事务，会话信息

时间：2020-12-02 06:18:16

无论是有意无意，如果事务在数据库中保持打开，则它会阻塞其他进程对修改后的数据进行操作。同样，对事务日志进行备份也只会截断不活动事务的那部分事务日志，所以打开的事务会导致日志变多（甚至达到物理限制），直到事务被提交或回滚。

要找到最早的活动事务，可以使用DBCC OPENTRAN命令。详细用法见MSDN:http://msdn.microsoft.com/zh-cn/library/ms182792.aspx

给出一个示例：

[python] view plaincopyprint?
  1. REATE TABLE T_Product(PKID int, PName Nvarchar(50));  
  2. GO  
  3. BEGIN TRAN  
  4. INSERT INTO T_Product VALUES (101, '嫦娥四号');  
  5. GO  
  6. DBCC OPENTRAN;  
  7. ROLLBACK TRAN;  
  8. GO  
  9. DROP TABLE T_Product;  
  10. GO  

 

执行结果：

[python] view plaincopyprint?
  1. /*  
  2. (1 row(s) affected)  
  3. 数据库 'Testdb' 的事务信息。  
  4. 最早的活动事务:  
  5.     SPID (服务器进程 ID): 54  
  6.     UID (用户 ID): -1  
  7.     名称          : user_transaction  
  8.     LSN           : (295:6687:1)  
  9.     开始时间    : 12 24 2010  2:50:15:607PM  
  10.     SID           : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000  
  11. DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。  
  12. */  

 

结果显示了最早活动日志的相关信息，包括服务器进程ID、用户ID、和事务的开始时间。关键是SPID和Start Time。
拥有这些信息后，可以使用动态管理视图(DMV)来检验正在执行的T-SQL,以及在必要时关闭这个过程
DBCC　OPENTRAN对于孤立连接（在数据库中是打开的，但与应用程序或客户端已经断开的连接）是非常有用的，并能帮助我们找出遗漏了COMMIT或ROLLBACK的事务。该命令也返回在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务。如果没有活动事务，则显示信息性消息，而不返回会话级数据。

我们看一个实例：

[python] view plaincopyprint?
  1. SET Transaction  isolation level serializable  
  2. BEGIN TRAN  
  3. select * from T_Product  
  4. Insert into T_Product   
  5. select 'OATest' union all  
  6. select 'OAPlay'  

 

这是一个未提交的事务,在另一个查询窗口执行如下：

[python] view plaincopyprint?
  1. select session_id,transaction_id,is_user_transaction,is_local   
  2. from sys.dm_tran_session_transactions  
  3. where is_user_transaction=1  

 

执行结果：

[python] view plaincopyprint?
  1. /*返回结果  
  2. session_id    transaction_id    is_user_transaction    is_local  
  3. 54    489743    1    1  
  4. */  

返回会话ID后，可以通过sys.dm_exec_connections和sys.dm_exec_sql_text来挖掘最近执行的查询的详细信息。

[python] view plaincopyprint?
  1. select s.text from sys.dm_exec_connections c   
  2. cross apply sys.dm_exec_sql_text(c.most_recent_sql_Handle) s   
  3. where session_id=54  

 

这个查询返回最后执行的语句。也可以使用sys.dm_exec_requests。

因为也从sys.dm_tran_session_transactions的第一个查询中得知事务ID，所以可以使用sys.dm_tran_active_transactions来了解更多事务本身的内容。

[python] view plaincopyprint?
  1. select transaction_begin_time,  
  2. case transaction_type   
  3.     when 1 then 'Read/Write transaction'  
  4.     when 2 then 'Read-Only transaction'  
  5.     when 3 then 'System transaction'  
  6.     when 4 then 'Distributed transaction'  
  7.     end tran_Type,  
  8. case transaction_state  
  9.     when 0 then  'not been comoletely initaialiaed yet'  
  10.     when 1 then  'initaialiaed but ha notstarted'  
  11.     when 2 then  'active'  
  12.     when 3 then  'ended (read-only transaction)'  
  13.     when 4 then  'commit initiated for distributed transaction'  
  14.     when 5 then  'transaction prepared and waiting resolution'  
  15.     when 6 then  'commited'  
  16.     when 7 then  'being rolled back'  
  17.     when 0 then  'been rolled back'  
  18.     end transaction_state  
  19.  from   
  20. sys.dm_tran_active_transactions  
  21. where transaction_ID=455520  

 

[python] view plaincopyprint?
  1. /*结果：  
  2. transaction_begin_time    tran_Type    transaction_state  
  3. 2010-12-24 14:05:29.170    Read/Write transaction    active  
  4. */  

 

小结：这里演示了使用DMV 排除故障和调查长时间的活动事务的一般技巧。基本步骤如下：
1、查询sys.dm_tran_session_transactions获取会话ID和事务ID之间的映射。
2、查询sys.dm_exec_connections和sys.dm_exec_sql_text查找会话最新执行的命令（most_recent_sql_Handle列）
3、最后，查询sys.dm_tran_active_transactions确定事务被打开了多少时间、事务的类型和事务的状态。
使用这个技巧可以回到应用程序去查明调用的被抛弃的事务（打开但从未提交）以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。

标签：

相关文章