SQL Server Transaction Remains Open After Stored Procedure Error

By: Bhavesh Patel   |   Updated: 2017-05-02   |   Comments   |   Related: > Error Handling

Problem

We tried using Try-Catch exception handling in both SQL Server and in the application, but the Try-Catch couldn't trap everything inside the SQL code. As per MSDN, SQL errors that have a severity of 10 or lower are considered warnings or informational messages and are not handled by Try-Catch blocks. Errors that have a severity of 20 or higher that cause the database engine to close the connection will not be handled by the Try-Catch block. However, Try-Catch will handle errors with a severity of 20 or higher as long as the connection is not closed. In this tip will show the issue and how this was resolved.

Solution

We prefer using a Try-Catch block in data modification procedures which are actions like insert, update and delete, but we ran into a situation where the session ends, but the transaction stays open.

I will show you an example of this issue.

Sample Code

Here is code to create a database, a table and stored procedure.  The stored procedure will cause an error, because table TEST does not exist.

USE MASTER
GO

CREATE DATABASE Test
GO

USE Test
GO

Create Table Test1
(
   id int,
   list nvarchar(max)
)
GO

CREATE PROCEDURE USP_Test
AS
BEGIN
   BEGIN TRY
      BEGIN TRAN
         SELECT * FROM TEST1
         SELECT * FROM TEST
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      PRINT XACT_STATE()
      PRINT error_message()
      PRINT ERROR_SEVERITY()

      IF (XACT_STATE()) = -1
      BEGIN
         ROLLBACK TRANSACTION;
      END;
   END CATCH
END
GO

If I execute the stored procedure I get the following errors.

EXEC USP_Test

Now I am going to check the transaction state using DMV sys.dm_tran_session_transactions. Run the query below in another query window.

select * from sys.dm_tran_session_transactions

We can see the transaction is still open for session 53.

I am going to kill this open transaction using the below query.

USE Master
GO

KILL 53

Changing code to catch errors

There are two types of errors that will not be handled by Try-Catch if the error occurs in the same execution level as the Try-Catch construct:

• Compile errors, such as syntax errors that prevent a batch from executing.
• Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

When the procedure is run it raises an error like "deferred name resolution" with a severity below 10, so the Try-Catch could not trap the error.  To resolve this issues, we need to set an explicit transaction using "SET XACT_ABORT ON". Below is the same exact stored procedure from above, except for adding the highlighted line.

In the code below, I will drop the database and recreate the database and object and execute the stored procedure again.

USE MASTER
GO

DROP DATABASE Test
GO

CREATE DATABASE Test
GO

USE Test
GO

Create Table Test1
(
   id int,
   list nvarchar(max)
)
GO

CREATE PROCEDURE USP_Test
AS
SET XACT_ABORT ON
BEGIN
   BEGIN TRY
      BEGIN TRAN
         SELECT * FROM TEST1
         SELECT * FROM TEST
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      PRINT XACT_STATE()
      PRINT error_message()
      PRINT ERROR_SEVERITY()

      IF (XACT_STATE()) = -1
      BEGIN
         ROLLBACK TRANSACTION;
      END;
   END CATCH
END
GO

-- run the procedure
EXEC USP_Test

If I test again, the invalid object name error is still generated, but the stored procedure completes.

We will check DMV sys.dm_tran_session_transactions to see if the transaction is still open.

select * from sys.dm_tran_session_transactions

We can see there are no open transactions.

Summary

SET XACT_ABORT ON works as ambassador, when a run-time error occurs SQL Server will rollback the entire transaction and abort the T-SQL batch. So I would suggest using SET XACT_ABORT ON whenever you use transactions.

Next Steps

• Please first prior with Test environment before roll out in Production.
• More tips on error handling

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips