SQLException - Transaction BEGIN / COMMIT mismatch

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 coores开发者_StackOverflow中文版ponding 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

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.

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

BEGIN TRAN

ROLLBACK TRAN

COMMIT TRAN

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

Check that other procedure

Make sure you don't have inadvertently written

 return
 commit

in place of

 commit
 return

For me, that was the problem.

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