By: Bhavesh Patel | 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
