Sqlserver的Transaction做Rollback的時候要小心（轉載）

標籤：ddn   archive   事務   解決   lte   hat   沒有   back   必須   

仔細研究了下，發現sql server裡面的explicit transaction（顯示事務）還是有點複雜的。以下是有些總結：

Commit transaction 會提交所有嵌套的transaction修改。但是如果嵌套的transaction裡面有rollback tran to save point, 那麼save point之後的部分會revert掉。

delete from dbo.numbertablebegin tran out1     insert into dbo.numbertable values(1)     insert into dbo.numbertable values(2)          begin tran inn1          insert into dbo.numbertable values(3)          insert into dbo.numbertable values(4)     save tran inn1SavePoint          insert into dbo.numbertable values(5)     rollback tran inn1SavePoint     commit tran inn1commit tran out1

 

@@TRANCOUNT可以用來記錄當前session transaction的個數，對於嵌套的transaction來講，每次begin transaction都讓它加一，每次commit tran都會讓它減一。所以在語句裡面可以通過select @@TRANCOUNT 來檢查當前是否在一個transaction裡面。如果當前@@TRANCOUNT為0，那調用commit還是rollback都會出現語句錯誤。在嵌套的transaction裡面，rollback是很特殊的，它會直接把@@TRANCOUNT設定為0。

begin tranbegin tranbegin tranprint @@trancountrollback tranprint @@trancount

 

對於嵌套的transaction來講，rollback的寫法是很特殊。如果嵌套，rollback transaction後面是不能帶transaction的name的，要帶也只能是最外面的transaction的name。Rollback會拋棄所有嵌套transaction在rollback語句之前的修改。不過Rollback之後的更新依然會提交就是了，原因在於：rollback之後，@@trancount為0，那麼rollback之後的語句就不屬於explicit transaction, 屬於autocmmit transaction了，自動認可。

delete from dbo.numbertablebegin tran t1     insert into dbo.numbertable values(1)          begin tran t2          insert into dbo.numbertable values(2)     rollback tran     print ‘after rollback in innert transaction, the transaction count is: ‘+cast(@@trancount, varchar(5))     insert into dbo.numbertable values(3)--commit transelect * from dbo.numbertable

預存程序裡面也可以begin transaction，如果調用預存程序的地方也begin transaction,那麼這種情況也屬於嵌套transaction，如果在預存程序裡面rollback，得到的結果和上面一樣。但是有一點特殊的地方在與，執行預存程序結束的時候會比較開始執行預存程序的@@trancount和結束時候@@trancount的值，如果不一樣，Sqlserver會給出一個訊息像“Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.”這個給出的訊息並不會影響其後的執行。

CREATE PROCEDURE [dbo].[AddNumber]       ASBEGIN     begin tran           insert into dbo.numbertable values(1)          insert into dbo.numbertable values(2)          insert into dbo.numbertable values(3)     rollback tranENDdelete from dbo.numbertablebegin tran out1exec dbo.addnumberprint @@trancountinsert into dbo.numbertable values(3)select * from dbo.numbertable

 

如果在預存程序裡面做rollback了，那到外面再做commit或者rollback都是沒有效果的並且會報錯，因為嵌套transaction內部的transaction一旦調用了rollback，@@trancount就為0了，在外面commit或rollback就會直接報錯。比如如下sp，我想像在最外面rollback，那就出錯了，因為sp裡面語句rollback了。最後表裡面始終會插入值3。

delete from dbo.numbertablebegin tran out1exec dbo.addnumberprint @@trancountinsert into dbo.numbertable values(3)rollback tran out1select * from dbo.numbertable

 

 

所以對於嵌套的transaction來講，如果內部transaction一旦rollback，就會給外部的transaction留下一個大坑。為瞭解決這個為題，有兩種解決方案：

1.在外部的transaction裡面檢查@@trancount，如果這個值跟你代碼begin tran的時候一致，那說明內部transaction沒有rollback，那可以繼續commit或者rollback。

delete from dbo.numbertablebegin tran t1     insert into dbo.numbertable values(1)          begin transaction t2          insert into dbo.numbertable values(2)     rollback tran      if @@trancount = 1      begin          insert into dbo.numbertable values(3)          commit tran     end

 

2.在所有的內部transaction裡面，只能commit，不能rollback。如果必須rollback，那怎麼辦？save point就可以派上用場了。比如sp改成這樣子：

ALTER PROCEDURE [dbo].[AddNumber]        ASBEGIN     begin tran      save tran pp          insert into dbo.numbertable values(1)          insert into dbo.numbertable values(2)          insert into dbo.numbertable values(3)     rollback tran pp     commit tranEND begin tran out1exec dbo.addnumberprint @@trancountinsert into dbo.numbertable values(3)commit tran out1

 

原文連結

 

Sqlserver的Transaction做Rollback的時候要小心（轉載）