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