This blog is part of the series The SQL Server TSQL Classes: Stored Procedure
The links to previous post are given below.
https://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-error-handling/
In last class I talked about error handling in stored procedure. Continuing on from where I left, in this class I’ll discuss transaction handling and Error handling.
Transaction handling is important so as to make sure that in case of any exception/error in a procedure there aren’t any open transactions resulting in blocking or deadlock situation and thus decreasing the system performance.
Open transaction in a procedure can be handled using @@Trancount global variable or by XACT_STATE().
@@Trancount returns number of begin/open transaction in a connection.
Let’s see how it can be used to handle open transactions.
IF Object_ID('uspAddCurrency') IS NOT NULL
DROP PROC uspAddCurrency
GO
CREATE PROCEDURE [dbo].[uspAddCurrency]
@CurrencyCode nChar(3),
@Name nvarchar(50)
AS
BEGIN TRY
-- Insert a new record into currency table
BEGIN TRAN
INSERT INTO [Sales].[Currency]
([CurrencyCode]
,[Name]
,[ModifiedDate])
VALUES
(
@CurrencyCode,
@Name,
GetDate()
)
COMMIT TRAN
END TRY
BEGIN CATCH
-- return the error
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
-- rollback any open transaction
If @@TRANCOUNT>0
BEGIN
Print 'Rolling back open transaction'
ROLLBACK TRAN
END
END CATCH
The procedure uspAddCurrency inserts a value in Adventureworks2008R2.Sales.Currency table. Let’s now force a primary key violation error and analyze the output.
The execution of procedure results in a primary key violation error and thus the commit transaction statement within Try isn’t executed and the control is passed to CATCH block. Inside catch block @@trancount detects one open transaction and thus does a rollback.
Another, way to do this is XACT_STATE() function. It tells whether there is an active committable or active uncommittable transaction. Let’s re-write the above procedure using XACT_STATE().
IF Object_ID('uspAddCurrency') IS NOT NULL
DROP PROC uspAddCurrency
GO
CREATE PROCEDURE [dbo].[uspAddCurrency]
@CurrencyCode nChar(3),
@Name nvarchar(50)
AS
BEGIN TRY
-- Insert a new record into currency table
BEGIN TRAN
INSERT INTO [Sales].[Currency]
([CurrencyCode]
,[Name]
,[ModifiedDate])
VALUES
(
@CurrencyCode,
@Name,
GetDate()
)
COMMIT TRAN
END TRY
BEGIN CATCH
-- return the error
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
-- Check transaction status
If (XACT_STATE())=1
BEGIN
SELECT [MSG]='It''s a Commitable Transaction',[XACT_STATE]=XACT_STATE()
COMMIT TRAN
END
If (XACT_STATE())=-1
BEGIN
SELECT [MSG]='It''s an UnCommitable Transaction',[XACT_STATE]=XACT_STATE()
ROLLBACK TRAN
END
END CATCH
Let’s now force a primary key violation and analyze the output.
The primary key violation error results in a committable open transaction and it is handled accordingly by the procedure. In order to change the open transaction to uncommittable state we need to set XACT_ABORT to ON. XACT_ABORT, when set to ON rollbacks the transaction instead of statement if a query results in an error. Let’s make this change in above procedure and analyze the output.
As explained above, the XACT_ABORT renders the transaction to be uncommitable which is trapped by XACT_STATE=-1 condition.
Setting XACT_STATE to ON is considered to be good practice as it rollbacks entire transaction and avoids a situation where in an open transaction result in blocking or deadlock.
Difference between @@TranCount and XACT_STATE()
- Trancount can’t be used to detect uncommitable transactions.
- XACT_STATE can be used to either commit or rollback a transaction depending on a situation where as trancount can only be used to rollback transaction.
- XACT_STATE can’t be used to determine nested transactions.
Summary
In this class we learnt how to control transactions with in a stored procedure using @@TRANCOUNT and XACT_STATE function and to use XACT_ABORT to avoid open transactions .
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Hi!
Transaction handling should be done always by the client application, which controls the behavior.
If you prefer trx handling in an sp, you should at least ensure the rollback is only for the sp code, which means you should rollback to a safepoint. A general ROLLBACK rolls back the complete open transaction which may involve various procedures and statements.
kr/Werner
This stored procedure will rollback fine as long as it is not within a transaction (for instance started by the client).
If there is already a transaction open, rolling back would generate a mismatch between of transaction count before and after procedure execution… and I don’t know how to handle this!
Whether transaction should be handled by the client application is extremely debatable.
In most cases, I’d rather not because it may generate long running transactions and this is usually a bad idea. I would rather keep transactions to as short as possible and return a status from the procedure indicating success or failure…
I suppose it depends the sort of applications written and the perspective (client side programmers might prefer handling transactions client side while DBAs would rather keep it shorter).