Nested transactions in SQL Server with @ @TranCount

Source: Internet
Author: User

When dealing with transactions, it is common to roll back and forth with rollback TRANSACTION, but if this is used in nested transactions, an error occurs.

In SQL Server, the hierarchy of nested transactions is reflected by the @ @TranCount global variables. each time the BEGIN transaction will cause @ @TranCount plus 1. Each commit transaction the @ @TranCount minus 1, while the rollback TRANSACTION rolls back all nested transactions, including committed transactions and uncommitted transactions, leaving the @ @TranCount at 0. For example:
Begin Transaction-@ @TranCount = 1
         BeginTransaction--@ @TranCount = 2   
                   BeginTransaction--@ @TranCount = 3
                   Commit Transaction--@ @TranCount = 2
         Commit Transaction--@ @TranCount = 1
Commit Transaction--@@ Trancount = 0

If there is an error rollback transaction 
:
Begin TRANSACTION-@ @TranCount = 1
          BeginTransaction-@ @TranCount = 2   
                   BeginTransaction--@ @TranCount = 3
                   ROLLBACK transaction --@ @TranCount = 0
         Commit TRANSACTION--@ @TranCount = 0---error
Transaction count After EXECUTE indicates, a COMMIT or ROLLBACK Transaction statement is Miss Ing. Previous count = 1, current count = 0.
         If an error occurs in a nested transaction, the simplest method should be to commit it anyway, and return the error code (a code that is not normally possible, such as- 1) Let the previous transaction handle this error, so that the @ @TranCount minus 1. This allows the outer transaction to be rolled back or committed to ensure that the outer transaction is consistent at the beginning and at the end. Because the inner transaction returns an error code, the outer transaction (outermost layer) can roll back the transaction so that the committed transaction can be rolled back without error.

This should often happen in a project, where a stored procedure uses a transaction, but there is no guarantee that it will be called by another stored procedure with a transaction, and if it is called separately, an error can be directly rolled back, but if it is called by another stored procedure with a transaction, RollBack will go wrong. Therefore, a mechanism is needed to differentiate, create a temporary variable to differentiate whether nested, and nested layers, as follows:

DECLARE @TranCounter INT;
SET @TranCounter = @ @TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION Proceduresave;
ELSE
BEGIN TRANSACTION;
............
--code to be executed within the transaction
............

IF @ @ERROR <>0
Goto Error
Commit Transaction
Commit Transaction
--The following returns the value to return 0 is just an example
Return 0

Error:
IF @TranCounter = 0
ROLLBACK TRANSACTION;
Else

ROLLBACK TRANSACTION Proceduresave;

Return @Error

Nested transactions in SQL Server with @ @TranCount (GO)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.