Managing Transactions in SQL Server Stored ProceduresBy Scott Mitchell
Last week's article, Maintaining Database Consistency with Transactions, looked at what, exactly, transactions are and how they can be used to guarantee consistency in a database when issuing commands that modify multiple tables. Specifically, transactions ensure that a set of modifying statements are atomic, namely that either all steps succeed or all steps fail. Transactions guarantee atomicity across query errors - such as trying to delete a record that cannot be deleted due to a foreign key constraint, or attempting to insert a string value into a numeric field in a database table - as well as catastrophic errors, such as power failures, hard drive crashes, and so on. In short, when wrapping multiple modifying commands within a transaction the database is guaranteed to be in one of two states: either the state before the batch of commands was issued, or the state after all commands have completed - in other words, there's no 'in-between' state.
The canonical transaction example is transferring money from one bank account to another. This process involves two steps: debiting money from one account and then crediting it to the other account. What is vital to avoid is having only the first step complete but, perhaps due to a power failure, the second not completing. When moving funds we don't want the bank to debit money from our account if they're not going to credit it back to another.
In addition to discussing the purpose of transactions, last week's article also examined how to wrap multiple modifying
statements within a transaction using ADO.NET. In particular we looked at using the
SqlConnection classes. A transaction is started with a call to the
BeginTransaction() method (which returns a
SqlTransaction object) and can be committed or rolled
back via the
In this week's article we'll continue our look at transactions, examining how to create, commit, and rollback transactions strictly through stored procedures. After examining the T-SQL syntax for working with transactions we'll discuss when one would opt to use transactions directly in stored procedures versus using them in the ADO.NET layer. Read on to learn more!
A Bit About Stored Procedures...
A database's stored procedures are akin to computer program's methods - like a method, stored procedures can accept a variable number of input parameters and can optionally return data. With a stored procedure you can write a number of T-SQL statements that are executed when the stored procedure is invoked, much like how a method is a collection of statements that are executed when the method is called.
Stored procedures offer a number of advantages over ad-hoc SQL statements, including:
- Adding another layer of encapsulation to the software architecture,
- Separating the data-specific details from the source code of the application,
- Security enhancements, as you can deny access to the database's underlying tables, instead only granting execute permissions for the stored procedures your web application needs to use, and
- Performance increases since SQL server can cache the execution plan on stored procedures whereas such plans need to be recomputed each time with ad-hoc queries.
Creating Transactions Within a Stored Procedure
Recall from Maintaining Database Consistency with Transactions that when using transactions you'll typically use the following sequence of steps:
- Indicate that you want to start the transaction. All commands from this point forward are part of the logical, atomic operation.
- Issue the discrete commands - the
DELETEs that make up your transaction.
- If any of these commands cause an error, rollback the transaction. Rolling back a transaction has the effect of undoing the effects of all previous statements in the transaction.
- If all steps succeed, commit the transaction. This persists the changes made throughout the transaction to the database.
BeginTransaction()method, with the transaction's calls wrapped inside of a
Try ... Catchblock. In the
Catchstatement - which would be reached if there were any SQL-related errors, you'd rollback the transaction by calling the
Rollback()method. If, however, no errors occurred you'd commit the transaction by calling the
Commit()method. The means for starting, committing, and rolling back a transaction inside of a stored procedure are a bit different, but the overall concept is the same.
First things first - we need to indicate that a transaction should begin. This is done, conveniently enough, with the
BEGIN TRANSACTION. Following that, you'll issue the set of statements that you want to be part of
the transaction, which will typically include
If you are using a version of Microsoft SQL Server prior to 2005, then after each and every modifying statement
you'll need to check to see if some error has occurred and, if so, you'll need to
rollback the transaction and raise an error. This can be done by checking the value of
@@ERROR - if it's non-zero
then the last statement caused an error. In the event of an error you'll first want to rollback the transaction using the
ROLLBACK and then raise an error using
RAISERROR will terminate the
stored procedure call and cause an exception to be raised in your .NET application.
If you are using Microsoft SQL Server 2005 or onwards, then you can use alternatively use the
This article looks at an example using the pre-SQL Server 2005 pattern. For a look at SQL Server 2005's
and how it can be used to create a more terse syntax for transactions, see:
TRY...CATCH in SQL Server 2005:
An Easier Approach to Rolling Back Transactions in the Face of an Error.
Lastly, at the end of the stored procedure, if there has been no error you'll want to commit the transaction, which can
be accomplished using the
The following snippet shows a stored procedure that uses a transaction to maintain data consistency. In this particular example, there are two related tables,
Employees, with the stored procedure deleting a
specific department. Each
Employees record has a foreign key constraint to the
Therefore, in order to delete a record from the
Departments table all associated records from the
table must be deleted first. Since we want to ensure that either all of the associated employees and the department are
deleted, or none of the records are deleted, we'll wrap these two
DELETE statements within a transaction.
RAISERROR method specifies the error message to return to the client along with the severity and state.
Refer to the technical
documentation for more information on
Transaction Management: Stored Procedures or ADO.NET?
This article, along with last week's look at using ADO.NET to manage transactions, provide two different ways for providing transactional support in your data-driven applications. So which technique should you use? I find myself using both techniques. For simple batch statements that are known at compile time I tend to use stored procedures. An excellent example would be the case in point above, when deleting from a table that has associated child records that I also want to delete.
There are times, however, when I need to issue a series of atomic statements but I don't know what statements will need to be executed, exactly, until runtime. Or, there may be a variable number of stored procedure calls that need to be made based on data supplied by the user. For example, imagine that I presented a list of all departments in the system using a CheckBoxList Web control, allowing the user to select the set of departments she wanted to delete.
Upon posting back, I'd enumerate the items in the CheckBoxList's
Items collection and issue a call to the
DeleteDepartment stored procedure we examined above. Now, deleting each department and its associated employees
is treated as a transaction (as we have
BEGIN TRANSACTION in the stored procedure), but I want all department
deletions to behave as an atomic statement. That is, say the user opted to delete five departments. If three departments
and their employees to be deleted only to have an error crop up when trying to delete the fourth department, I want to make
sure that the delete of the first three departments and employees are "undone." I can accomplish this by wrapping the
loop through the
Items collection and the subsequent calls to
DeleteDepartment within a transaction
itself. (This is an example of providing transactions at both the ADO.NET level and stored procedure level!)
That is, my code would look something like:
In a previous article we looked at maintaining transactions through ADO.NET; in this article we saw how transaction management can be done directly through T-SQL commands in stored procedures. Both approaches have their time and place - I typically manage transactions in the ADO.NET layer when the transactions involves iterating through programming constructs or other high-level logic needs to be applied when determining what entities to modify. However, when issuing a simple, straightforward request that modifies multiple tables - such as deleting a record from a "parent" table that requires that it's "children" also be subsequently deleted - I place the transaction logic at the stored procedure level. Of course, your mileage may vary.