我在做一个WinForm程序,操作完数据库后界面卡死了,后来发现是一个transaction中,代码设计不当,begin transaction,过程中抛出异常,并没有commit或者rollback操作。所以阻塞了后续的对于数据库中的某些表的select操作。其实,transaction没有关闭这个问题如果不是因为界面卡死了,是很难发现的。有没有一种方法,能够查看当前数据库的所有没有关闭的transaction呢?
解决方案 »
- sql function 和并列问题
- 关于SQL JOIN ON 查寻的问题.
- 有2个表 请问怎么根据user_id 把a表的值复制到B表中啊???
- select datepart(week,getdate()) 这句话有问题
- sql求比赛冠军
- 小小数据问题
- 怎么在 win2003 上安装 sql server 200 个人版
- 请问:那里有关于sql server2000数据库和oracle数据库性能比较的文章??
- 高分征求powerdesigner 9.0的学习资料!!!
- ******** 怪事情了,SQLSERVER 企业管理器打不开? ******
- 对于数据库受损后,表删除不掉的情况
- 跪求 sqlserver中删除根据多个字段查询出来的重复记录保留一条就可以了。
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
谢谢。这个是我的操作截图对数据库不太熟悉,你能说说各个字段的大致意思么。 database_taransaction_state中,这两个记录
都是什么状态? 能不能获取是哪个程序或者进程向数据库发车的这个transaction呢?
dbcc inputbuffer(55),看看是干嘛的
from sys.sysprocesses
where spid>50 and open_tran>0select * from sys.dm_tran_session_transactionsselect * from sys.dm_tran_locks
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_idleft 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) ttwhere st.session_id >= 50
and database_id <> 32767
谢谢。这里查出了transaction. 用什么语句查出一个transaction锁定了那些表呢?
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]
这个挺好的,能查出那个数据库的那个对象,被会话加了什么锁,这个请求是否已经grant。