About nesting of database transactions

first, the basic concept

The SQL Server database engine ignores the commit of the internal transaction. The internal transaction is committed or rolled back according to the action taken at the end of the most external transaction. If an external transaction is committed, the internal nested transaction is also committed. If the external transaction is rolled back, all internal transactions are also rolled back, regardless of whether the internal transaction was committed separately.

The procedure for executing a nested transaction should be: As the transaction executes, the number of nested layers is incremented by one and then gradually decremented to the first layer. Whether the entire transaction is committed depends on the last layer running commit (commit) or rollback (rollback).

1. Each invocation of COMMIT TRANSACTION is applied to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statement is nested, then the COMMIT statement applies only to the last nested transaction, which is the most internal transaction. Even if the commit TRANSACTION transaction_name statement inside the nested transaction references the transaction name of the external transaction, the commit is applied only to the most internal transaction.

2. The transaction_name parameter of the ROLLBACK TRANSACTION statement refers to a set of internal transactions that name nested transactions is illegal, and transaction_name can only reference the transaction name of the most external transaction. If you execute an ROLLBACK TRANSACTION transaction_name statement that uses an external transaction name at any level of a set of nested transactions, all nested transactions are rolled back. If you execute an ROLLBACK TRANSACTION statement with no transaction_name parameter at any level of a set of nested transactions, all nested transactions are rolled back, including the most external transactions. (This paragraph means that only the first layer of the nested layer is allowed to use rollback rollback)

3, @ @TRANCOUNT function records the nesting level of the current transaction. Each BEGIN TRANSACTION statement increases the @ @TRANCOUNT by 1. Each COMMIT transaction statement causes the @ @TRANCOUNT minus 1.

A, a rollback TRANSACTION statement without a transaction name will roll back all nested transactions and reduce the @ @TRANCOUNT to 0.

B, ROLLBACK TRANSACTION, which uses a set of transaction names for the most external transactions in a nested transaction, rolls back all nested transactions and reduces the @ @TRANCOUNT to 0.

C, you can use the SELECT @ @TRANCOUNT to determine if the @ @TRANCOUNT is equal to 1 or greater than 1 when it is not possible to determine whether it is already in the transaction. If @ @TRANCOUNT equals 0, it indicates that it is not in a transaction.

Note: The above content is excerpted from--http://msdn.microsoft.com/zh-cn/library/ms189336.aspx

Second, the example illustrates:

1. Create a table to experiment with inserting data

CREATE TABLE Trantesttable (

T datetime

)

2. No transaction is used and the program runs with errors. However, the first statement inserts a record, and the second one does not insert a record.

Alter PROC TRANTEST2 (@errorTag int output)

As

BEGIN

INSERT into trantesttable values (GETDATE ())

INSERT into trantesttable values (' ... ')

END

3, see the following code, there are two layers of transaction nested use, TranTest1 nested call TranTest2, according to "one" in 1, 2 can draw the following code. Call TranTest1 is running normally and no records are inserted.

Alter PROC TranTest1 (@errorTag int output)

As

BEGIN

Set @errorTag = 0

Begin Transaction

DECLARE @errorTag2 int

EXEC TranTest2 @errorTag2 output–– call TranTest2 stored procedure

If @errorTag2 <0

BEGIN

Set @errorTag =-1

ROLLBACK TRANSACTION

RETURN

END

IF @ @TRANCOUNT > 0

COMMIT TRANSACTION

END

-----------TranTest2 Code------------

Alter PROC TRANTEST2 (@errorTag int output)

As

BEGIN

Set @errorTag = 0

Begin Transaction

INSERT into trantesttable values (GETDATE ())

INSERT into trantesttable values (' ... ')

If @ @error <> 0

BEGIN

Set @errorTag =-1

COMMIT TRANSACTION

RETURN

END

IF @ @TRANCOUNT > 0

COMMIT TRANSACTION

END

4, but TranTest2 is a separate stored procedure, may also be used directly to the use of the (outer layer does not nest transaction), TranTest2 is TranTest1 called and directly called the difference between the transaction nesting level is different. is called directly (there is no nested call), at this point in the TRANTEST2 is the first layer of transactions, encountered an exception should run ROLLBACK TRANSACTION the whole transaction rollback; called by TranTest1 (there is a nested call), The level of TranTest2 (@ @TRANCOUNT value) is 2, at which point the exception should run commit TRANSACTION, reduce the number of nesting layers by 1, and leave the transaction to the previous level, and finally the commit depends on whether the first layer is committed or rolled back;

To make TranTest2 stored procedures universal, you can decide whether to call commit or rollback based on the number of nested layers. If it is greater than 1, it indicates that there is a transaction package on it, and the transaction should be handed over to the previous layer. If it is 1, the entire transaction needs to be rolled back because there is no other place to roll back the transaction.

Alter PROC TRANTEST2 (@errorTag int output)

As

BEGIN

Set @errorTag = 0

Begin Transaction

INSERT into trantesttable values (GETDATE ())

INSERT into trantesttable values (' ... ')

If @ @error <> 0

BEGIN

Set @errorTag =-1

IF @ @TRANCOUNT > 1

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

RETURN

END

IF @ @TRANCOUNT > 0

COMMIT TRANSACTION

END

5, finally will TranTest1 like TranTest2 change to have universality, because TranTest1 also do not know who will be called.

Alter PROC TranTest1 (@errorTag int output)

As

BEGIN

Set @errorTag = 0

Begin Transaction

DECLARE @errorTag2 int

EXEC TranTest2 @errorTag2 output

If @errorTag2 <0

BEGIN

Set @errorTag =-1

IF @ @TRANCOUNT > 1

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

RETURN

END

IF @ @TRANCOUNT > 0

COMMIT TRANSACTION

END

Iii. Summary and finishing

 

To start a transaction in a stored procedure:

Following a principle, when a transaction is started, the value of @ @TRANCOUNT and the value of @ @TRANCOUNT at the end of the transaction are equal. Whether to commit or rollback in a transaction in the previous layer is determined by the rollback commit flag returned by the inner layer.

1, at the end of the normal completion of the transaction. At normal end of transaction, @ @TRANCOUNT minus 1.

IF @ @TRANCOUNT > 0

COMMIT TRANSACTION

A, if the outer layer does not have a nested transaction, then @ @TRANCOUNT = 1 Normal commit;

B, if the outer layer nested transactions, then @ @TRANCOUNT >1, run commit so that @ @TRANCOUNT-1, and return the correct system to run normal information, the real commit of the transaction to the outer transaction.

2. Exit the transaction midway through the transaction, and return the error flag (rollback flag).

IF @ @TRANCOUNT > 1

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

RETURN

A, if the outer layer nested transactions, then satisfy if @ @TRANCOUNT > 1 conditions, Run commit so that @ @TRANCOUNT-1, and return an error message, the real rollback to the top-level transaction (by the upper layer according to this error message to determine the rollback).

B, if the outer layer does not have nested transactions, then @ @TRANCOUNT = 1, satisfy else, directly rollback;