SQL Server嵌套事务探讨
先看一下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
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;
--Bad code2
USE NORTHWIND;3
--Create test table4
IF Object_id(N'TestTran',N'U') IS NOT NULL5
DROP TABLE TESTTRAN;6
7
CREATE TABLE TESTTRAN (8
COLA INT PRIMARY KEY,9
COLB CHAR(3));10
11
--Variable for keeping @@ERROR12
DECLARE @_Error INT;13
SET @_Error = 0;14
15
--Begin 3 nested transaction16
BEGIN TRANSACTION OUTERTRAN;17
BEGIN TRANSACTION INNER1;18
BEGIN TRANSACTION INNER2;19
20
INSERT INTO TESTTRAN VALUES (3,'ccc');--Inner221
22
RAISERROR('Inner2 error', 16, 1)23
IF @@ERROR = 024
COMMIT TRANSACTION INNER2;25
ELSE26
ROLLBACK TRANSACTION ;27
28
INSERT INTO TESTTRAN VALUES (2,'bbb');--Inner129
30
IF @@ERROR = 031
COMMIT TRANSACTION INNER1;32
ELSE33
ROLLBACK TRANSACTION ;34
35
INSERT INTO TESTTRAN VALUES (1,'aaa');--OuterTran36
37
--RAISERROR ('OuterTran error',16,1)38
39
IF @@ERROR = 040
COMMIT TRANSACTION OuterTran;41
ELSE42
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
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)
2
USE NORTHWIND;3
4
--Create test table5
IF Object_id(N'TestTran',N'U') IS NOT NULL6
DROP TABLE TESTTRAN;7
8
CREATE TABLE TESTTRAN (9
COLA INT PRIMARY KEY,10
COLB CHAR(3));11
12
--Variable for keeping @@ERROR13
DECLARE @_Error INT;14
SET @_Error = 0;15
16
--Begin 3 nested transaction17
BEGIN TRANSACTION OUTERTRAN;18
BEGIN TRANSACTION INNER1;19
BEGIN TRANSACTION INNER2;20
21
INSERT INTO TESTTRAN VALUES (3,'ccc');--Inner222
23
--raiserror('Inner2 error', 16, 1)24
SET @_Error = @@ERROR 25
IF @_Error = 026
COMMIT TRAN INNER2;27
ELSE28
IF @@TRANCOUNT > 129
COMMIT TRANSACTION INNER2;30
ELSE31
ROLLBACK TRANSACTION INNER2;32
33
INSERT INTO TESTTRAN VALUES (2,'bbb');--Inner134
35
IF @_Error = 036
SET @_Error = @@ERROR 37
IF @_Error = 038
COMMIT TRAN INNER1;39
ELSE40
IF @@TRANCOUNT > 141
COMMIT TRANSACTION INNER1;42
ELSE43
ROLLBACK TRANSACTION INNER1;44
45
INSERT INTO TESTTRAN VALUES (1,'aaa');--OuterTran46
47
RAISERROR ('OuterTran error',16,1)48
49
-- rollback transaction OuterTran50
SET @_Error = @_Error + @@ERROR51
52
IF @_Error = 053
COMMIT TRAN OUTERTRAN;54
ELSE55
IF @@TRANCOUNT > 156
COMMIT TRANSACTION;57
ELSE58
ROLLBACK TRANSACTION OUTERTRAN; 59
60
SELECT * FROM TESTTRAN (NOLOCK)
考虑到SP的调用，我们开发SP时应该在最后把@@ERROR返回供调用者检查。另外测试注意检查一下@@Trancount，有时结果看似正确，但是如果@@Trancount不等于0，说明我们的代码出了问题。
