Nested transactions in SQL Server use--transaction count indicates a mismatch between the number of BEGIN and COMMIT statements--The underlying problem

problem:
1. System.Data.SqlClient.SqlException (0x80131904): The transaction count after EXECUTE indicates that the number of BEGIN and COMMIT statements does not match. Previous count = 1, current count = 0.

2. The transaction count after EXECUTE indicates that the number of BEGIN and COMMIT statements does not match. Previous count = 0, current count = 1.

The following content is what I wrote before, mainly some test code, but I don't understand it very deeply. Now clarify the essence directly, and put the essence behind it again.

Committed transactions cannot be undone or rolled back.
@@ trancount is equal to 0 when there are no open transactions.
Execute the begin tran [tranName] statement to increase @@ trancount by one.
Execute the commit tran [tranName] statement to decrease @@ trancount by one.
Performing a rollback tran rolls back the entire transaction and sets @@ trancount to 0.
There are two cases when executing the "rollback tran tranName" statement:
if (tranName was previously created with "Save Tran tranName") @@ trancount value unchanged

Otherwise, @trancount decreases by 1

      Note: The save tran command does not increase @@ trancount by 1.

analysis:
As long as the transaction parameter @@ TRANCOUNT is changed internally after the transaction is committed or rolled back, the above error will occur, without exception.

Trying to use Sql "set @@ trancount = 1;", this is not allowed by sqlserver.

Everyone, the above error appears, most probably in nested transactions.

 

If not nested: before begin tran, @@ trancount is 0; after begin tran, @@ trancount is 1 at this time; after completion, commit or rollback, @@ trancount is 0 at this time; otherwise we write Not bad.

What about nesting:

Look at a stored procedure I wrote before:

              

[sql] view plaincopy
declare @trancount int --commit, rollback only controls this stored procedure
    set @trancount = @@ trancount;
      
    if (@ trancount = 0) / * Determine the transaction count, and use the savepoint or create a new transaction according to the situation * /
        begin tran current_tran--The current transaction point, rollback and commit all start here
    else
        save tran current_tran
[sql] view plain copy
declare @trancount int --commit, rollback only controls this stored procedure
    set @trancount = @@ trancount;
      
    if (@ trancount = 0) / * Determine the transaction count, and use the savepoint or create a new transaction according to the situation * /
        begin tran current_tran--The current transaction point, rollback and commit all start here
    else
        save tran current_tran
.......

.... go to work

.......

 

[sql] view plaincopy
if @error_code! = 0 or @logErrorCode! = 1
        begin
            rollback tran current_tran
            set @error_code = -1;-failed
        end
    else
        begin
            commit tran current_tran
            set @error_code = 1;-success
         end
[sql] view plain copy
if @error_code! = 0 or @logErrorCode! = 1
        begin
            rollback tran current_tran
            set @error_code = -1;-failed
        end
    else
        begin
            commit tran current_tran
            set @error_code = 1;-success
         end
any questions? (Current_tran is a save point, I don't understand, there is a more detailed introduction later)

I used it for a long time (in a project), but suddenly one day, that is, today, something went wrong. The reason is that although it is written as nested, it has not been adjusted before.

I opened a transaction on the periphery, and then call this stored procedure. When it commits tran current_tran (rollback tran current_tran will be fine), what will go wrong? If you can't tell me very clearly, it means that you have not understood deeply. Make a choice?

1. "... the number of BEGIN and COMMIT statements does not match. Last count = 0, current count = 1."

2. "... BEGIN and number of COMMIT statements do not match. Last count = 1, current count = 0.

 

Answer: [2].

Clue analysis: I opened a transaction externally, so the value of @@ trancount should be 1 before entering the stored procedure; when entering, the value of save tran current_tran, @@ trancount has not changed; to the end, execute commit tran The value of current_tran, @@ trancount should be 0;-So, before entering, after coming out, the value of @@ trancount has changed, and SqlServer does not work (the reason, think for yourself: the begin tran pairing was broken up).

How to deal with it:

 1. Record @@ trancount before entering the sub-transaction, we use the variable @trancount to record.

 2. Before committing a sub-transaction, first determine whether the previous @trancount is 0; a value of 0 indicates that there is no transaction call before "this transaction", and the transaction can be submitted directly; otherwise, it indicates that there is already a transaction before entering the transaction. It is a subtransaction and cannot be committed.

[sql] view plaincopy
-If the current count is 0, submit.
         -Because of Commit tran, @@ TRANCOUNT will decrease by 1. When a transaction is nested, the existing procedure is called (as a sub-procedure, @@ TRANCOUNT> 0),
         -Just save the tran, @@ TRANCOUNT has not changed; direct Commit will reduce @@ TRANCOUNT by 1 and will break the transaction pair (Begin Tran)
        if (@trancount = 0)
        begin
            commit tran current_tran
        end
        set @error_code = 1;-success
[sql] view plain copy
-If the current count is 0, submit.
         -Because of Commit tran, @@ TRANCOUNT will decrease by 1. When a transaction is nested, the existing procedure is called (as a sub-procedure, @@ TRANCOUNT> 0),
         -Just save the tran, @@ TRANCOUNT has not changed; direct Commit will reduce @@ TRANCOUNT by 1 and will break the transaction pair (Begin Tran)
        if (@trancount = 0)
        begin
            commit tran current_tran
        end
        set @error_code = 1;-success
 
The use of nested transactions in SqlServer-the transaction count indicates that the number of BEGIN and COMMIT statements do not match-the underlying problem