SqlServer嵌套事务机制

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

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

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 ?嵌套事务回滚最外面事务，对内部事务有什么影响？

我们再来看一段代码：

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 嵌套事务回滚内部嵌套事务呢？

再看一段代码：

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

结果：

有异常信息：

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

(1 行受影响)

(2 行受影响)

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

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

?

具体测试代码：

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.保存回滚点：

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. 回滚指定“回滚点”：

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!