SQL Server——事务嵌套(未完工)
http://www.cnblogs.com/Kymo/archive/2008/05/14/1194161.html
先看一下SQL Server Online Help相关的说明
- Begin Transaction:标记一个显式本地事务的起始点。BEGIN TRANSACTION 使 @@TRANCOUNT 按 1 递增。
- Rollback Transaction: 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。(嵌套事务时,该语句将所有内层事务回滚到最外面的 BEGIN TRANSACTION 语句。无论在哪种情况下,ROLLBACK TRANSACTION 都将 @@TRANCOUNT 系统函数减小为 0。ROLLBACK TRANSACTION savepoint_name 不减小 @@TRANCOUNT。)
- 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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
CREATE TABLE TESTTRAN (
8
COLA INT PRIMARY KEY,
9
COLB CHAR(3));
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
--Variable for keeping @@ERROR
12
DECLARE @_Error INT;
13
SET @_Error = 0;
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
--Begin 3 nested transaction
16
BEGIN TRANSACTION OUTERTRAN;
17
BEGIN TRANSACTION INNER1;
18
BEGIN TRANSACTION INNER2;
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
INSERT INTO TESTTRAN VALUES (3,'ccc');--Inner2
21![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
IF @@ERROR = 0
31
COMMIT TRANSACTION INNER1;
32
ELSE
33
ROLLBACK TRANSACTION ;
34
35
INSERT INTO TESTTRAN VALUES (1,'aaa');--OuterTran
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
--RAISERROR ('OuterTran error',16,1)
38
39
IF @@ERROR = 0
40
COMMIT TRANSACTION OuterTran;
41
ELSE
42
ROLLBACK TRANSACTION;
43![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
SELECT * FROM TESTTRAN (NOLOCK);
45
SELECT @@Trancount;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
上述代码当内层事务发生错误时,并不能正常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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
--Create test table
5
IF Object_id(N'TestTran',N'U') IS NOT NULL
6
DROP TABLE TE
STTRAN;
7![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
CREATE TABLE TESTTRAN (
9
COLA INT PRIMARY KEY,
10
COLB CHAR(3));
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
--Variable for keeping @@ERROR
13
DECLARE @_Error INT;
14
SET @_Error = 0;
15![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
--Begin 3 nested transaction
17
BEGIN TRANSACTION OUTERTRAN;
18
BEGIN TRANSACTION INNER1;
19
BEGIN TRANSACTION INNER2;
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
INSERT INTO TESTTRAN VALUES (3,'ccc');--Inner2
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
RAISERROR ('OuterTran error',16,1)
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
SELECT * FROM TESTTRAN (NOLOCK)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)