Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Transaction Remains Open After Stored Procedure Error


By:   |   Last Updated: 2017-05-02   |   Comments   |   Related Tips: More > 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

Error Raised

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.

Result of Transaction state

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.

Error Raised

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.

Transaction Already rollbacked

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


Last Updated: 2017-05-02


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools