SQL Server嵌套事务探讨 (转贴)

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

上述代码当内层事务发生错误时，并不能正常 Rollback ，因为 Rollback 把 @@Trancount 变成了 0 ，所以后面的 Commit 语句就找不到对应的 Transaction 了。解决问题的关键就是 Rollback 时要判断 @@Trancount ，当 @@Trancount 等于 1 时进行 Rollback 进行回滚，否则执行 Commit 把 @@Trancount-1 ，同时把 @@Error 传到外层事务交给外层事务处理。 微软的原文是没有问题的，但是这种情况比较简单，我们一眼就能看出哪个是内层事务，哪个是外层事务，一共嵌套了几层，如果是 SP 调用呢？你不知道你的 SP 会被谁调用，也不知道会被嵌套几层。

下面看一下怎么处理内层事务的错误(何时Rollback, Commit及错误的传递)

 

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

考虑到SP的调用，我们开发SP时应该在最后把@@ERROR返回供调用者检查。另外测试注意检查一下@@Trancount，有时结果看似正确，但是如果@@Trancount不等于0，说明我们的代码出了问题。