SQLException - Transaction BEGIN / COMMIT mismatch

Question

Have you encountered this exception for a stored procedure which does indeed have a balanced transaction block?

I double-checked the stored procedure and it has exactly one TRANSACTION BEGIN and cooresponding TRANSACTION END

Error logged

SqlException - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. - Delete failed - stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     at System.Data.SqlClient.S ... [Rest of stack trace truncated by logging system]`

Additional Info

The stored procedure does contain EXEC calls to another stored procedure. Would a mismatched transaction pair here cause the error to be surfaced in this way?

Update It turns out that there was a violation of a foreign key constraint within the nested stored procedure. The outer transaction did not include a Try/Catch block and had SET XACT_ABORT ON specified, which did not properly handle either a commit or rollback. Also added a check for @@TransactionCount > 0 before attempting a rollback

The foreign key constraint was a huge help for me here! I ran into a similar problem with sqlalchemy from python — Karl P, Aug 19 '11 at 13:35

score 14 · Accepted Answer · answered Sep 16 '10 at 17:01

14

Yes it would. Each BEGIN increments @@trancount, each commit decrements it. Only when the count gets to 0 is the transaction really committed. Your procedure, as a caller, cannot control this. It is the job of the called procedures to behave properly and balance the BEGIN and COMMIT count, if any of the called procedures has a imbalance, you'll see this error.

answered Sep 16 '10 at 17:01

Remus Rusanu

288,378
40
442
569

http://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm I have posted here – Vignesh Kumar A Feb 21 '14 at 09:23

score 6 · Answer 2 · answered Sep 16 '10 at 17:04

6

Are you sure you don't have path that produces this

BEGIN TRAN

ROLLBACK TRAN

COMMIT TRAN

answered Sep 16 '10 at 17:04

Conrad Frix

51,984
12
96
155

score 2 · Answer 3 · answered Sep 16 '10 at 17:00

2

Yes, you're going down the right path. If a nested procedure call creates transactions, they affect the calling procedure.

Check that other procedure

answered Sep 16 '10 at 17:00

Mike Forman

4,367
1
21
18

score 1 · Answer 4 · answered Oct 28 '14 at 17:36

1

Make sure you don't have inadvertently written

 return
 commit

in place of

 commit
 return

For me, that was the problem.

answered Oct 28 '14 at 17:36

seguso

2,024
2
18
20

score 0 · Answer 5 · answered Dec 03 '13 at 12:48

0

Add this on top of PROCEDURE creation text

SET XACT_ABORT ON;

It will ensure that if nothing got executed, the transaction is aborted entirely.

MSDN Doc: http://technet.microsoft.com/en-us/library/ms188792(v=sql.105).aspx

answered Dec 03 '13 at 12:48

yonsk

163
1
8

SQLException - Transaction BEGIN / COMMIT mismatch

5 Answers5