SQL Server——事務嵌套

標籤：style   blog   http   os   io   ar   strong   for   資料   

http://www.cnblogs.com/Kymo/archive/2008/05/14/1194161.html

 

先看一下SQL Server Online Help相關的說明

1. Begin Transaction:標記一個顯式本地事務的起始點。BEGIN TRANSACTION 使 @@TRANCOUNT 按 1 遞增。
2. Rollback Transaction: 將明確交易或隱性交易回復到事務的起點或事務內的某個儲存點。（嵌套事務時，該語句將所有內層交易回復到最外面的 BEGIN TRANSACTION 語句。無論在哪種情況下，ROLLBACK TRANSACTION 都將 @@TRANCOUNT 系統函數減小為 0。ROLLBACK TRANSACTION savepoint_name 不減小 @@TRANCOUNT。）
3. Commit Transaction:標誌一個成功的隱性事務或明確交易的結束。如果 @@TRANCOUNT 為 1，COMMIT TRANSACTION 使得自從事務開始以來所執行的所有資料修改成為資料庫的永久部分，釋放事務所佔用的資源，並將 @@TRANCOUNT 減少到 0。如果 @@TRANCOUNT 大於 1，則 COMMIT TRANSACTION 使 @@TRANCOUNT 按 1 遞減並且事務將保持活動狀態。

下面用代碼進行解釋，代碼是根據Online Help Commit Transaction一節的代碼修改而成，首先建立一個Table，然後開始三個Trasaction，中間人為觸發一些錯誤，然後觀察運行結果。

 

 

 1--Bad code
 2USE NORTHWIND;
 3--Create test table
 4IF Object_id(N‘TestTran‘,N‘U‘) IS NOT NULL
 5  DROP TABLE TESTTRAN;
 6
 7CREATE TABLE TESTTRAN (
 8  COLA INT   PRIMARY KEY,
 9  COLB CHAR(3));
10
11--Variable for keeping @@ERROR
12DECLARE  @_Error INT;
13SET @_Error = 0;
14
15--Begin 3 nested transaction
16BEGIN TRANSACTION OUTERTRAN;
17BEGIN TRANSACTION INNER1;
18BEGIN TRANSACTION INNER2;
19
20INSERT INTO TESTTRAN VALUES     (3,‘ccc‘);--Inner2
21
22RAISERROR(‘Inner2 error‘, 16, 1)
23IF @@ERROR = 0
24    COMMIT TRANSACTION INNER2;
25ELSE
26    ROLLBACK TRANSACTION ;
27  
28INSERT INTO TESTTRAN VALUES     (2,‘bbb‘);--Inner1
29
30IF @@ERROR = 0
31    COMMIT TRANSACTION INNER1;
32ELSE
33    ROLLBACK TRANSACTION ;
34  
35INSERT INTO TESTTRAN VALUES     (1,‘aaa‘);--OuterTran
36
37--RAISERROR (‘OuterTran error‘,16,1)
38                        
39IF @@ERROR = 0
40    COMMIT TRANSACTION OuterTran;
41ELSE
42    ROLLBACK TRANSACTION;    
43
44SELECT * FROM   TESTTRAN (NOLOCK);
45SELECT @@Trancount;

上述代碼當內層事務發生錯誤時，並不能正常Rollback，因為Rollback把@@Trancount變成了0，所以後面的Commit語句就找不到對應的Transaction了。解決問題的關鍵就是Rollback時要判斷@@Trancount，當@@Trancount等於1時進行Rollback進行復原，否則執行Commit把@@Trancount-1，同時把@@Error傳到外層事務交給外層交易處理。微軟的原文是沒有問題的，但是這種情況比較簡單，我們一眼就能看出哪個是內層事務，哪個是外層事務，一共嵌套了幾層，如果是SP調用呢？你不知道你的SP會被誰調用，也不知道會被嵌套幾層。

下面看一下怎麼處理內層事務的錯誤(何時Rollback, Commit及錯誤的傳遞)

 

 1--Good code
 2USE NORTHWIND;
 3
 4--Create test table
 5IF Object_id(N‘TestTran‘,N‘U‘) IS NOT NULL
 6  DROP TABLE TESTTRAN;
 7
 8CREATE TABLE TESTTRAN (
 9  COLA INT   PRIMARY KEY,
10  COLB CHAR(3));
11
12--Variable for keeping @@ERROR
13DECLARE  @_Error INT;
14SET @_Error = 0;
15
16--Begin 3 nested transaction
17BEGIN TRANSACTION OUTERTRAN;
18BEGIN TRANSACTION INNER1;
19BEGIN TRANSACTION INNER2;
20
21INSERT INTO TESTTRAN VALUES     (3,‘ccc‘);--Inner2
22
23--raiserror(‘Inner2 error‘, 16, 1)
24SET    @_Error = @@ERROR              
25IF    @_Error = 0
26    COMMIT TRAN INNER2;
27ELSE
28  IF @@TRANCOUNT > 1
29    COMMIT TRANSACTION INNER2;
30  ELSE
31       ROLLBACK TRANSACTION INNER2;
32  
33INSERT INTO TESTTRAN VALUES     (2,‘bbb‘);--Inner1
34
35IF @_Error = 0
36    SET @_Error = @@ERROR                
37IF @_Error = 0
38    COMMIT TRAN INNER1;
39ELSE
40  IF @@TRANCOUNT > 1
41    COMMIT TRANSACTION INNER1;
42  ELSE
43    ROLLBACK TRANSACTION INNER1;
44  
45INSERT INTO TESTTRAN VALUES     (1,‘aaa‘);--OuterTran
46
47RAISERROR (‘OuterTran error‘,16,1)
48
49--    rollback transaction OuterTran
50SET @_Error = @_Error + @@ERROR
51                        
52IF @_Error = 0
53    COMMIT TRAN OUTERTRAN;
54ELSE
55    IF @@TRANCOUNT > 1
56        COMMIT TRANSACTION;
57    ELSE
58        ROLLBACK TRANSACTION OUTERTRAN;    
59
60SELECT * FROM   TESTTRAN (NOLOCK)

SQL Server——事務嵌套