SQLException - Transaction BEGIN / COMMIT mismatch

sql sql-server-2008 transactions sqlexception
Chris Ballance picture Chris Ballance · Sep 16, 2010 · Viewed 21.8k times · Source

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

Answer

Remus Rusanu picture Remus Rusanu · Sep 16, 2010

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.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More