SqlServer嵌套事务机制

收藏学习：

今天我们主研究一下SqlServer中嵌套事务使用。代码能说明大多数问题，看代码。

1.嵌套事务提交原理测试

[c-sharp] view plain copy

1. PRINT 'Trancount before transaction: ' + CAST(@@trancount as char(1))   
2.   
3. BEGIN TRAN  
4. PRINT 'After first BEGIN TRAN: ' + CAST(@@trancount as char(1))  
5.   
6. BEGIN TRAN  
7. PRINT 'After second BEGIN TRAN: ' + CAST(@@trancount as char(1))  
8. COMMIT TRAN  
9.   
10. PRINT 'After first COMMIT TRAN: ' + CAST(@@trancount as char(1))  
11. COMMIT TRAN  
12.   
13. PRINT 'After second COMMIT TRAN: ' + CAST(@@trancount as char(1))  

结果：

Trancount before transaction: 0
After first BEGIN TRAN: 1
After second BEGIN TRAN: 2
After first COMMIT TRAN: 1
After second COMMIT TRAN: 0

我们可以得出：

1.可以看到每一个BEGIN TRAN语句都会使@@TRANCOUNT增加1；

2.每一个COMMIT TRAN语句都会使@@TRANCOUNT减少1；

3.如前所述，一个值为0的@@TRANCOUNT意味着没有打开的事务;

4.因此，在@@TRANCOUNT值从1降到0时结束的事务发生在最外层事务提交的时候。

2. 嵌套事务回滚

2.1  嵌套事务回滚最外面事务，对内部事务有什么影响？

我们再来看一段代码：

[c-sharp] view plain copy

1. -- 创建临时表  
2. CREATE TABLE #TestTrans(Cola INT PRIMARY KEY,  
3.                Colb varchar(20) NOT NULL);  
4. /* 外部事务 */  
5. BEGIN TRANSACTION OutOfProc;  
6. --内部事务  
7.     BEGIN TRANSACTION InProc  
8.       INSERT INTO #TestTrans VALUES (1,'aaaa');  
9.     COMMIT TRANSACTION InProc;  
10.       
11. /* 回滚外部事务，也会回滚内部事务 */  
12. ROLLBACK TRANSACTION OutOfProc;  
13.   
14. /*无数据，1说明最外层事务回滚，里面所有的事务都会回滚 */  
15. SELECT * FROM #TestTrans;  
16.   
17. drop table #TestTrans  

结果：没有数据。

现在看来：无论数据是否提交，只要最外层回滚了就会导致所有内部所有嵌套类回滚。

2.2 嵌套事务回滚内部嵌套事务呢？

再看一段代码：

[c-sharp] view plain copy

1. -- 创建临时表  
2. CREATE TABLE #TestTrans(Cola INT PRIMARY KEY,  
3.                Colb varchar(20) NOT NULL);  
4. /* 外部事务 */  
5. BEGIN TRANSACTION OutOfProc;  
6. --内部事务  
7.     BEGIN TRANSACTION InProc  
8.       INSERT INTO #TestTrans VALUES (1,'aaaa');  
9.     ROLLBACK TRANSACTION InProc;  
10.       
11.     --内部事务2  
12.     BEGIN TRANSACTION InProc2  
13.       INSERT INTO #TestTrans VALUES (2,'222');  
14.     COMMIT TRANSACTION InProc2;  
15.       
16. /* 提交外部事务 */  
17. COMMIT TRANSACTION OutOfProc;  
18.   
19. /*出错：内部事务未回滚，内部事务不能回滚，"找不到该名称的事务或保存点。" */  
20. SELECT * FROM #TestTrans;  
21.   
22. drop table #TestTrans  

结果：

有异常信息：

(1 行受影响)
消息 6401，级别 16，状态 1，第 9 行
无法回滚 InProc。找不到该名称的事务或保存点。

(1 行受影响)

(2 行受影响)

我们可以看到：ROLLBACK TRANSACTION InProc　是错误的。原因是没有保存还原点 InProc。代码应该改为如下（具体原因请往下看）：

[sql] view plain copy

1. BEGIN TRANSACTION InProc  
2.      save tran InProc;  
3.   INSERT INTO #TestTrans VALUES (1,'aaaa');  
4. ROLLBACK TRANSACTION InProc;  

操作前保存好回滚点（save tran InProc），回滚时指定当时保存的位置，SqlServer才知道回滚到哪儿去。

3. 事务原理

往下读之前必须了解：全局变量@@trancount 可以确定是否存在打开的事务及其嵌套的深度。

 

提交的事务不能撤销或回滚。

当不存在打开的事务时，@@trancount 等于 0。

执行 begin tran [tranName]语句将 @@trancount 增加 1。

执行commit tran [tranName]语句将 @@trancount 减小 1。

执行 rollback tran  会回滚整个事务并设置@@trancount 为 0。

 

执行 " rollback tran  tranName"语句时有两种情况：

if(tranName 之前　是用 " Save Tran tranName" )  @@trancount值不变

否则，@trancount 减小1

 

具体测试代码：

[sql] view plain copy

1. -- 创建临时表  
2. CREATE TABLE #TestTrans(Cola INT PRIMARY KEY,  
3.                Colb varchar(20) NOT NULL);  
4.                  
5.  select @@TRANCOUNT '未开外部事务';  
6.   
7. /* 外部事务 */  
8. BEGIN TRANSACTION OutOfProc;  
9.  select @@TRANCOUNT '开外部事务';  
10.    
11. --内部事务  
12.     BEGIN TRANSACTION InProc  
13.       
14.    select @@TRANCOUNT '开内部事务1';  
15.   
16.       save tran InProc;  
17.         
18.        select @@TRANCOUNT '保存内部事务1';  
19.       INSERT INTO #TestTrans VALUES (1,'aaaa');  
20.     ROLLBACK TRANSACTION InProc;  
21.       
22.     select @@TRANCOUNT '回滚内部事务1';  
23.       
24.     --内部事务2  
25.     BEGIN TRANSACTION InProc2  
26.       INSERT INTO #TestTrans VALUES (2,'222');  
27.         
28.       --内部事务21  
29.        BEGIN TRANSACTION InProc21  
30.        select @@TRANCOUNT '开内部事务21';  
31.          INSERT INTO #TestTrans VALUES (3,'2422');  
32.          COMMIT TRANSACTION InProc21;  
33.        select @@TRANCOUNT '提交内部事务21';  
34.            
35.     COMMIT TRANSACTION InProc2;  
36.     select @@TRANCOUNT '提交内部事务2';  
37.       
38. /* 提交外部事务 */  
39. COMMIT TRANSACTION OutOfProc;  
40.   
41. select @@TRANCOUNT '提交外部事务';  
42.   
43. SELECT * FROM #TestTrans;  
44.   
45. drop table #TestTrans  

结果：

其他：

保存事务回滚点--可以有选择的提交或回滚内部嵌套事务。

思路

检查@@TRANCOUNT的值，以此来确定是否需要开始一个事务。如果@@TRANCOUNT大于0，则不开启新事务,只需要保存的个回滚位置即可；否则，开启新事务。下面是操作:

 

1.保存回滚点：

[javascript] view plain copy

1. declare @trancount int --commit,rollback只控制本存储过程  
2.    set @trancount = @@trancount   
3.    if (@trancount=0) /*判断事务记数，根据情况确定使用保存点或者新建一个事务*/   
4.     begin tran curtran--当前事务点，rollback、commit都从这里开始   
5.    else  
6.     save tran curtran  

2. 回滚指定“回滚点”：

[sql] view plain copy

1. if(@error <> 0 or @pay_way_error = 0) --@pay_way_error 决定了是否需要回滚  
2.     begin  
3.         rollback tran curtran  
4.         set @result = -1 --异常  
5.     end  

[sql] view plain copy

1. if(@error <> 0 or @pay_way_error = 0) --@pay_way_error 决定了是否需要回滚  
2.     begin  
3.         rollback tran curtran  
4.         set @result = -1 --异常  
5.     end  

---http://blog.csdn.net/xiaoyong322/article/details/7355426