Maintaining Database Consistency with Transactions
By Scott Mitchell
Introduction
While databases can efficiently hold large amounts of information that can be queried, all that data and all that querying
power is useless if the data is incorrect or nonsensical. Databases provide a plethora of techniques for ensuring the integrity and
consistency: primary key and unique constraints can be employed to ensure entity integrity; foreign key constraints aid in
ensuring relational integrity, and transactions help ensure that the database's data remains consistent.
While INSERT, UPDATE, and DELETE statements are the most
granular operations for modifying a database's underlying data, at times we want to treat multiple
INSERT, UPDATE, and/or DELETE statements as one atomic operation.
That is, in certain situations, rather than having each INSERT, UPDATE, and DELETE statement
stand on its own, we want the set of statements to be, together, an indivisible unit. When issuing this set of
statements we want either the entire set of statements to succeed, or all to fail - there should be no 'in-between' state.
The canonical transactional example is transferring money from one account to another. A money transfer account at a bank
requires two steps; if we want to transfer $500 from our checking account to our savings account, the following steps must be
processed:
First, $500 must be deducted from our checking account,
Next, $500 must be added to the savings account
In terms of SQL syntax, this would involve two UPDATE statements - one subtracting $500 from the balance of
the checking account and the other incrementing the savings account balance by $500. It is vital, however, that these two
steps are treated as one atomic unit. What we want to avoid is to have step 1 complete, subtracting $500 from our checking
account, but before step 2 can run, crediting our savings account, imagine that the database server crashes. (Well, this
scenario is something the bank might not get too upset over!) It is important that either both of these steps complete
in total or neither complete.
Database transactions are what ensure atomicity, one of the key features of any database system. Microsoft SQL Server, as
well as any professional grade database product, has support for transactions. In this article we'll examine how to wrap
multiple SQL statements within an atomic database transaction using the SqlTransaction class in the
System.Data.SqlClient namespace. Read on to learn more!
Transaction Examples
Before we look at the .NET code necessary for establishing a transaction, let's first take a moment to discuss some common
scenarios where transactions might be needed. I already mentioned the canonical example - moving money from one account to
another - and that typifies scenarios where transactions are needed. If you are performing multiple modifying statements -
INSERT, UPDATE, or DELETEs to one or more tables - and these actions, together, form
a logical, atomic unit, then you'll likely want to utilize transactions.
A common example is when you have a parent/child relationship between two (or more) tables in the database. When deleting
a row from the parent table you'll need to delete the associated child rows as well (or reassign them to a new parent).
Therefore, in code where you delete a parent record you might have two SQL statements, like:
-- DELETE child records
DELETE FROM ChildTable
WHERE ParentID = IDofParentBeingDeleted
-- DELETE parent record
DELETE FROM ParentTable
WHERE ParentID = IDofParentBeingDeleted
Clearly these two DELETE statements are a logical, atomic operation. That is, you don't want the operation to
be interrupted somehow after just the first DELETE has completed, but before the second one has.
Another common transaction example exists when you have logically related tables that, when inserting or updating one,
requires an insert or update in the other. For example, imagine an online auto insurance site where you can get free,
online quotes. To get your quote you might be prompted to not only enter some insurance-related information - the year, make
and model of your car, your age and marital status, and so on - but also some information as how you heard about the site -
radio ad, TV ad, from a friend, and so on. Upon hitting 'submit,' the site might make two inserts - one into a table that
has a record for each insurance request, as well as another record into some customer reporting table that tracks how visitors
are finding the site.
Not only do transactions protect against unexpected, catastrophic failures from interrupting a sequence of steps, but they
also protect against unexpected SQL-related errors. For example, imagine that you had five UPDATEs you wanted
to logically group as a single, atomic operation, but, for whatever reason, the data that was attempting to be updated in
the fifth and final UPDATE contained an illegal value and therefore caused a error. Without wrapping these
five UPDATEs in a transaction the fifth one would error out and not update the database, but the four previous
UPDATEs would be. This is now a maintenance nightmare since the database is now in an logically inconsistent
state.
The Common Steps of a Transaction
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 INSERT, UPDATE, and 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.
Transactions are atomic so if there is any catastrophic failure - a loss of power, the database server crashing, etc. -
when the database restarts the 'partially completed' transaction will be automatically rolled back, maintaining the consistency
of the system.
When working with transactions through .NET you'll start the transaction and then issue a number of statements using that
transaction object. A Try ... Catch block can be used to catch any exceptions raised by errors in issuing SQL
statements that make up the transaction. In such a case you can rollback the transaction. If no errors occur, you can
commit the transaction.
Working With Transactions - the SqlTransaction Class
If you are using Microsoft SQL Server you can use the System.Data.SqlClient.SqlTransaction class to start a
transaction. Begin by creating a connection to your database via the SqlConnection class. Next, create
a SqlTransaction instance by calling the BeginTransaction() method of the SqlConnection
class like so:
'Create a connection
Dim myConnection As New SqlConnection(myConnString)
myConnection.Open()
'Start the transaction
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()
Next, create the SqlCommand object that you'll issue your SQL statements through. When creating this object you
need to specify that it should use the myTransSqlTransaction object. You can assign this
through the constructor or the SqlCommand's Transaction property.
... Continued from above ...
Try
'Specify the first statement to run...
Dim sql as String = "INSERT INTO ..."
'Create the SqlCommand object, specifying the transaction through
'the constructor (along with the SQL string and SqlConnection)
'Alternatively, could set properties of myCommand
'to specify the Connection, CommandText, and Transaction...
Dim myCommand as New SqlCommand(sql, myConnection, myTrans)
Note that we have the SqlCommand object and all command issued to the database within a Try ... Catch
block. At this point you can go ahead and issue all of the related commands to the database:
... Continued from above ...
myCommand.ExecuteNonQuery()
'Issue another INSERT
myCommand.CommandText = "INSERT INTO ..."
myCommand.ExecuteNonQuery()
... Lather, rinse, repeat as needed! ...
'If we reach here, all command succeeded, so commit the transaction
myTrans.Commit
Catch ex as Exception
'Something went wrong, so rollback the transaction
myTrans.Rollback()
Throw 'Bubble up the exception
Finally
myConnection.Close() 'Finally, close the connection
End Try
That's all there is to it! In the Lather, rinse, repeat as needed! you can issue all of the related SQL statements
to your database that comprise the transaction. Note that if anything goes awry, the transaction is rolled back in the
Catch section. If, however, all statements complete without error, the transaction is committed. Regardless
of whether or not an exception is raised, the code in the Finally block is executed, which closes the connection.
Maintaining Transactions in T-SQL
You can also manage transactions directly in T-SQL syntax. That is, rather than using the SqlTransaction class
in your source code, you can move the transaction syntax to those stored procedures that require them (i.e., those that
modify data in multiple statements). Refer to Managing Transactions
in SQL Server Stored Procedures for more information.
Conclusion
In this article we examined the concept of database transactions and how to wrap SQL statements in an atomic transaction
from .NET. When creating ASP.NET data-driven applications it is important to maintain your data's
consistency. If you have scenarios where there are multiple INSERT, UPDATE, or DELETE
statements that make up one logical, atomic operation, it is imperative that you wrap these statements within a transaction.
If you fail to do this, you'll later regret when one of the related statements raises an error or there's some unexpected,
catastrophic failure that interrupts one of these atomic operations.
