Maintaining Database Consistency with TransactionsBy Scott Mitchell
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.
DELETE statements are the most
granular operations for modifying a database's underlying data, at times we want to treat multiple
DELETE statements as one atomic operation.
That is, in certain situations, rather than having each
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
UPDATEstatements - 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!
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 -
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:
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
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
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
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.
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
If you are using Microsoft SQL Server you can use the
System.Data.SqlClient.SqlTransactionclass to start a transaction. Begin by creating a connection to your database via the
SqlConnectionclass. Next, create a
SqlTransactioninstance by calling the
BeginTransaction()method of the
SqlConnectionclass like so:
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
SqlTransaction object. You can assign this
through the constructor or the
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:
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 |
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
DELETEstatements 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.