SQL server--Transaction Nesting

Http://www.cnblogs.com/Kymo/archive/2008/05/14/1194161.html

Let's take a look at the SQL Server Online Help related instructions

1. begin Transaction: marks the starting point for an explicit local transaction. BEGIN TRANSACTION The @ @TRANCOUNT by 1 increments.
2. Rollback Transaction: Rolls back an explicit or implicit transaction to the start of a transaction or to a savepoint within a transaction. (When a transaction is nested, the statement rolls back all the inner transactions to the outermost BEGIN TRANSACTION statement.) In either case, the ROLLBACK TRANSACTION reduces the @ @TRANCOUNT system function to 0. ROLLBACK TRANSACTION Savepoint_name does not decrease @ @TRANCOUNT. ）
3. Commit Transaction: Flags The end of a successful implicit transaction or explicit transaction. If @ @TRANCOUNT is 1,commit TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, freeing up the resources occupied by the transactions and reducing the @ @TRANCOUNT to 0. If the @ @TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements the @ @TRANCOUNT by 1 and the transaction remains active.

The code is explained in code according to the code of the online Help Commit transaction section, first creating a table, then starting three trasaction, the middle man triggers some errors, and then observes the results of the operation.

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
21st
22RAISERROR (' Inner2 error ', 16, 1)
23IF @ @ERROR = 0
COMMIT TRANSACTION INNER2;
25ELSE
ROLLBACK TRANSACTION;
27
28INSERT into Testtran VALUES (2, ' BBB ');--inner1
29
30IF @ @ERROR = 0
COMMIT TRANSACTION INNER1;
32ELSE
ROLLBACK TRANSACTION;
34
35INSERT into Testtran VALUES (1, ' AAA ');--outertran
36
37--raiserror (' Outertran error ', 16, 1)
38
39IF @ @ERROR = 0
TRANSACTION Outertran;
41ELSE
ROLLBACK TRANSACTION;
43
44SELECT * from Testtran (NOLOCK);
45SELECT @ @Trancount;

The above code when the internal transaction error, and can not be normal rollback, because rollback the @ @Trancount into 0, so the subsequent commit statement will not find the corresponding transaction. The key to solve the problem is to judge @ @Trancount when rollback, when @ @Trancount equals 1 o'clock for rollback to rollback, otherwise execute commit @ @Trancount-1, At the same time, the @ @Error to the outer transaction to the outer transaction processing. Microsoft's original text is no problem, but this situation is relatively simple, we can see at a glance which is the inner transaction, which is the outer layer of the transaction, a total of nested layers, if the SP call it? You don't know who your SP will be called, or how many layers will be nested.

Here's a look at how to handle an inner transaction error (when rollback, commit, and error delivery)

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,
Ten 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
$ COMMIT TRAN INNER2;
27ELSE
IF @ @TRANCOUNT > 1
COMMIT TRANSACTION INNER2;
-ELSE
ROLLBACK TRANSACTION INNER2;
32
33INSERT into Testtran VALUES (2, ' BBB ');--inner1
34
35IF @_error = 0
SET @_error = @ @ERROR
37IF @_error = 0
COMMIT TRAN INNER1;
39ELSE
IF @ @TRANCOUNT > 1
INNER1 COMMIT TRANSACTION;
"ELSE"
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
Outertran COMMIT TRAN;
54ELSE
IF @ @TRANCOUNT > 1
TRANSACTION COMMIT;
$ ELSE
ROLLBACK TRANSACTION Outertran;
59
60SELECT * from Testtran (NOLOCK)

SQL server--Transaction Nesting