第十六章——处理锁、阻塞和死锁（1）——确定长时间运行的事务

---1)-改进语句----监控当前正在运行的事务：
select dbt.database_id,
       DB_NAME(dbt.database_id) '数据库名',
       dbt.transaction_id,
       at.name,
       at.transaction_begin_time,
       case at.transaction_type         --事务类型  
           when 1 then '读/写事务'  
           when 2 then '只读事务'  
           when 3 then '系统事务'  
           when 4 then '分布式事务'  
       end 'transaction类型',  
       case at.transaction_state  
           when 0 then '事务尚未完全初始化'  
           when 1 then '事务已初始化但尚未启动'  
           when 2 then '事务处于活动状态'  
           when 3 then '事务已结束。该状态用于只读事务'  
           when 4 then '已对分布式事务启动提交进程'  
           when 5 then '事务处于准备就绪状态且等待解析'  
           when 6 then '事务已提交'  
           when 7 then '事务正在被回滚'  
           when 8 then '事务已回滚'  
           end  'transaction状态',
       st.session_id,  
       tt.text as '最近执行的语句',
       es.program_name
from sys.dm_tran_database_transactions dbt
left join sys.dm_tran_active_transactions at
        on dbt.transaction_id = at.transaction_id
left join sys.dm_tran_session_transactions st
        on at.transaction_id = st.transaction_id
left join sys.dm_exec_sessions es
       on st.session_id = es.session_id
left join sys.dm_exec_connections ec
       on es.session_id = ec.session_id
outer apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) tt
where st.session_id >= 50
      --and database_id <> 32767
      
--杀死进程kill session_id
------1)----------------------监控当前正在运行的事务：
/*SELECT  ST.transaction_id AS TransactionID ,  
        DB_NAME(DT.database_id) AS DatabaseName ,  
        AT.transaction_begin_time AS TransactionStartTime ,  
        DATEDIFF(SECOND, AT.transaction_begin_time, GETDATE()) AS TransactionDuration ,  
        CASE AT.transaction_type  
          WHEN 1 THEN 'Read/Write Transaction'  
          WHEN 2 THEN 'Read-Only Transaction'  
          WHEN 3 THEN 'System Transaction'  
          WHEN 4 THEN 'Distributed Transaction'  
        END AS TransactionType ,  
        CASE AT.transaction_state  
          WHEN 0 THEN 'Transaction Not Initialized'  
          WHEN 1 THEN 'Transaction Initialized & Not Started'  
          WHEN 2 THEN 'Active Transaction'  
          WHEN 3 THEN 'Transaction Ended'  
          WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'  
          WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'  
          WHEN 6 THEN 'Transaction Committed'  
          WHEN 7 THEN 'Transaction Rolling Back'  
          WHEN 8 THEN 'Transaction Rolled Back'  
        END AS TransactionState  
FROM    sys.dm_tran_session_transactions AS ST  
        INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id  
        INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id  
ORDER BY TransactionStartTime  
GO  */
--这里查出了transaction. 用什么语句查出一个transaction锁定了那些表呢？
select db_name(resource_database_id) 'DatabaseName',
       object_name(resource_associated_entity_id) 'TableName',
       request_type,
       request_mode,
       request_status
 from sys.dm_tran_locks
 where resource_type='OBJECT' and request_session_id=[进程ID]