我在做一个WinForm程序，操作完数据库后界面卡死了，后来发现是一个transaction中，代码设计不当，begin transaction，过程中抛出异常，并没有commit或者rollback操作。所以阻塞了后续的对于数据库中的某些表的select操作。
其实，transaction没有关闭这个问题如果不是因为界面卡死了，是很难发现的。
有没有一种方法，能够查看当前数据库的所有没有关闭的transaction呢？
------解决方案--------------------
方法1：DBCC OPENTRAN()和DBCC INPUTBUFFER()
方法2：sys.dm_tran_database_transactions and sys.dm_tran_session_transactions
SELECT dt.transaction_id,
st.session_id,
database_transaction_begin_time,
CASE database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END database_transaction_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log recorst.'
WHEN 4 THEN 'The transaction has generated log recorst.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
END database_transaction_state,
database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved
FROM sys.dm_tran_database_transactions dt
INNER JOIN sys.dm_tran_session_transactions st
ON st.transaction_id = dt.transaction_id
------解决方案--------------------
希望对你有用：
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 '事务已回滚'