This blog is part of the series The SQL Server TSQL Classes: Stored Procedure

The links to previous post are given below.

http://www.sqlservergeeks.com/sql-server-stored-procedures-the-tsql-classes-stored-procedures-a-primer/

http://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching/

http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/777/the-tsql-classes-stored-procedures-parameter-sniffing

http://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-parameter-sniffing-resolution/

http://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.

The procedure uspAddCurrency inserts a value in Adventureworks2008R2.Sales.Currency table. Let’s now force a primary key violation error and analyze the output.

1_The_TSQL_Classes_Stored_Procedures_Transaction_Handling

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().

Let’s now force a primary key violation and analyze the output.

2_The_TSQL_Classes_Stored_Procedures_Transaction_Handling

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.

3_The_TSQL_Classes_Stored_Procedures_Transaction_Handling

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 .

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook