Transactions in SQL Server Part I – An Introduction

A basic requirements in most SQL Server based applications is implementing a database operation (DDL or DML) in a single unit of work. The DDL requirement is more from a DBA’s perspective rather than the application, since any application would perform only INSERTs, UPDATEs and DELETEs on the existing data, and a DBA would be performing DDL statements on the existing database as part of schema updates. The default behavior of SQL Server is to implement implicit transactions, meaning every T-SQL Statement is committed as soon as it is executed. This works fine as long as there is only one query being executed. But this is seldom the reality in large applications.

Enterprise applications normally entail executing large transactions, ie. a large number of T-SQL statements to be executed as a single unit of work. This means, either all the statements run successfully or they all need to be rolled back. This is also one of the ACID properties of transactions – Atomic. This ensures that the state of the database is stable even in case of failure, and any subsequent operation can be performed gracefully. The implementation of these transactions becomes important in any multi-tier application where the user interface makes a call to the database in response to user actions. In the first part of this article, I will discuss the basics of SQL Server Transactions from a beginner developer’s perspective. Below is an example of how to define an explicit transaction:

BEGIN TRANSACTION;
BEGIN TRY
--  T-SQL Statements
END TRY
BEGIN CATCH
-- error catching SQL
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

The first statement in this example is BEGIN TRANSACTION. This statement marks the beginning of an explicit transaction. Beginning with SQL Server 2005 onwards, a new block of T-SQL statements called BEGIN TRY… END TRY and BEGIN CATCH… END CATCH was introduced. These blocks make it easy for error handling. The main block, BEGIN TR… END TRY contains the statements to be executed. BEGIN CATCH… END CATCH is the error handling portion of the construct.

The BEGIN TRANSACTION statement marks the beginning of an explicit transaction. This statement should be the first statement in any block of code (Stored Procedure, Function). The BEGIN TRY… END TRY defines a block of code where the actual T-SQL Statements go. If all the statements within this block run successfully, then control is transferred to the first statement after the BEGIN CATCH… END CATCH block. If, however, any of the statements results in an error, then control is transferred to the BEGIN CATCH… END CATCH block.

Since we have defined an explicit transaction, we need to either roll it back, or commit it. The Rollback would occur in the BEGIN CATCH… END CATCH block. The Commit should be immediately after this block. The statement that rolls back a transaction is ROLLBACK TRANSACTION, and the statement that commits is COMMIT TRANSACTION.

In the example code above, I have checked for the value of the system variable @@TRANCOUNT before taking a decision of rolling back or committing a transaction. This variable indicates whether there are any transactions open in the current session, and if yes, we need to do something about them. Therefore, in the BEGIN CATCH..END CATCH block, if the value of the @@TRANCOUNT variable is greater than 0, it means we need to roll back the transaction because since control was transferred to this block when errors were encountered in the BEGIN TRY..END TRY block of statements.

Similarly, after the BEGIN CATCH..END CATCH block, we need to check the @@TRANCOUNT variable to determine if there are any transactions open, and these will need to be committed them when no errors were encountered in the BEGIN TRY block.

This is a very basic demonstration of the use of SQL Server Transactions which should be implemented in stored procedures and functions that are the basic blocks of T-SQL code, and form the backbone of any transaction based application. In the next article, I will discuss more advanced topics related to using and implementing transactions.