快捷導(dǎo)航

SQLServer 2008中通過(guò)DBCC OPENTRAN和會(huì)話(huà)查詢(xún)事務(wù)

更新時(shí)間：2011年05月09日 00:24:56 作者：

無(wú)論是有意無(wú)意，如果事務(wù)在數(shù)據(jù)庫(kù)中保持打開(kāi)，則它會(huì)阻塞其他進(jìn)程對(duì)修改后的數(shù)據(jù)進(jìn)行操作。同樣，對(duì)事務(wù)日志進(jìn)行備份也只會(huì)截?cái)嗖换顒?dòng)事務(wù)的那部分事務(wù)日志，所以打開(kāi)的事務(wù)會(huì)導(dǎo)致日志變多（甚至達(dá)到物理限制），直到事務(wù)被提交或回滾。

要找到最早的活動(dòng)事務(wù)，可以使用DBCC OPENTRAN命令。詳細(xì)用法見(jiàn)MSDN:http://msdn.microsoft.com/zh-cn/library/ms182792.aspx

給出一個(gè)示例：

復(fù)制代碼代碼如下:

 
CREATE TABLE T_Product(PKID int, PName Nvarchar(50)); 
GO 

BEGIN TRAN 
INSERT INTO T_Product VALUES (101, '嫦娥四號(hào)'); 
GO 
DBCC OPENTRAN; 
ROLLBACK TRAN; 
GO 
DROP TABLE T_Product; 
GO 

執(zhí)行結(jié)果：

復(fù)制代碼代碼如下:

 
/* 
(1 row(s) affected) 
數(shù)據(jù)庫(kù) 'Testdb' 的事務(wù)信息。 

最早的活動(dòng)事務(wù): 
SPID (服務(wù)器進(jìn)程 ID): 54 
UID (用戶(hù) ID): -1 
名稱(chēng) : user_transaction 
LSN : (295:6687:1) 
開(kāi)始時(shí)間 : 12 24 2010 2:50:15:607PM 
SID : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000 
DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。 
*/ 

結(jié)果顯示了最早活動(dòng)日志的相關(guān)信息，包括服務(wù)器進(jìn)程ID、用戶(hù)ID、和事務(wù)的開(kāi)始時(shí)間。關(guān)鍵是SPID和Start Time。
擁有這些信息后，可以使用動(dòng)態(tài)管理視圖(DMV)來(lái)檢驗(yàn)正在執(zhí)行的T-SQL,以及在必要時(shí)關(guān)閉這個(gè)過(guò)程
DBCC　OPENTRAN對(duì)于孤立連接（在數(shù)據(jù)庫(kù)中是打開(kāi)的，但與應(yīng)用程序或客戶(hù)端已經(jīng)斷開(kāi)的連接）是非常有用的，并能幫助我們找出遺漏了COMMIT或ROLLBACK的事務(wù)。該命令也返回在指定數(shù)據(jù)庫(kù)內(nèi)存在最早的活動(dòng)事務(wù)和最早的分布式和非分布式復(fù)制事務(wù)。如果沒(méi)有活動(dòng)事務(wù)，則顯示信息性消息，而不返回會(huì)話(huà)級(jí)數(shù)據(jù)。

我們看一個(gè)實(shí)例：

復(fù)制代碼代碼如下:

 
SET Transaction isolation level serializable 
BEGIN TRAN 

select * from T_Product 

Insert into T_Product 
select 'OATest' union all 
select 'OAPlay' 

這是一個(gè)未提交的事務(wù),在另一個(gè)查詢(xún)窗口執(zhí)行如下：

復(fù)制代碼代碼如下:

 
select session_id,transaction_id,is_user_transaction,is_local 
from sys.dm_tran_session_transactions 
where is_user_transaction=1 

執(zhí)行結(jié)果：

復(fù)制代碼代碼如下:

 
/*返回結(jié)果 
session_id transaction_id is_user_transaction is_local 
54 489743 1 1 
*/ 

返回會(huì)話(huà)ID后，可以通過(guò)sys.dm_exec_connections和sys.dm_exec_sql_text來(lái)挖掘最近執(zhí)行的查詢(xún)的詳細(xì)信息。

復(fù)制代碼代碼如下:

 
select s.text from sys.dm_exec_connections c 
cross apply sys.dm_exec_sql_text(c.most_recent_sql_Handle) s 
where session_id=54 

這個(gè)查詢(xún)返回最后執(zhí)行的語(yǔ)句。也可以使用sys.dm_exec_requests。
因?yàn)橐矎膕ys.dm_tran_session_transactions的第一個(gè)查詢(xún)中得知事務(wù)ID，所以可以使用sys.dm_tran_active_transactions來(lái)了解更多事務(wù)本身的內(nèi)容

復(fù)制代碼代碼如下:

 
select transaction_begin_time, 
case 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 tran_Type, 
case transaction_state 
when 0 then 'not been comoletely initaialiaed yet' 
when 1 then 'initaialiaed but ha notstarted' 
when 2 then 'active' 
when 3 then 'ended (read-only transaction)' 
when 4 then 'commit initiated for distributed transaction' 
when 5 then 'transaction prepared and waiting resolution' 
when 6 then 'commited' 
when 7 then 'being rolled back' 
when 0 then 'been rolled back' 
end transaction_state 
from 
sys.dm_tran_active_transactions 
where transaction_ID=455520 

復(fù)制代碼代碼如下:

 
/*結(jié)果： 
transaction_begin_time tran_Type transaction_state 
2010-12-24 14:05:29.170 Read/Write transaction active 
*/ 

小結(jié)：這里演示了使用DMV 排除故障和調(diào)查長(zhǎng)時(shí)間的活動(dòng)事務(wù)的一般技巧?；静襟E如下：
1、查詢(xún)sys.dm_tran_session_transactions獲取會(huì)話(huà)ID和事務(wù)ID之間的映射。
2、查詢(xún)sys.dm_exec_connections和sys.dm_exec_sql_text查找會(huì)話(huà)最新執(zhí)行的命令（most_recent_sql_Handle列）
3、最后，查詢(xún)sys.dm_tran_active_transactions確定事務(wù)被打開(kāi)了多少時(shí)間、事務(wù)的類(lèi)型和事務(wù)的狀態(tài)。
使用這個(gè)技巧可以回到應(yīng)用程序去查明調(diào)用的被拋棄的事務(wù)（打開(kāi)但從未提交）以及那些運(yùn)行時(shí)間太長(zhǎng)或?qū)τ趹?yīng)用程序來(lái)說(shuō)是不必要的不恰當(dāng)事務(wù)。

邀月注：本文版權(quán)由邀月和博客園共同所有，轉(zhuǎn)載請(qǐng)注明出處。