To read the article online, visit http://www.4GuysFromRolla.com/articles/072705-1.aspx

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:
  1. Indicate that you want to start the transaction. All commands from this point forward are part of the logical, atomic operation.
  2. Issue the discrete commands - the INSERT, UPDATE, and DELETEs that make up your transaction.
  3. 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.
  4. 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 myTrans SqlTransaction 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.

Happy Programming!

  • By Scott Mitchell

  • Article Information
    Article Title: ASP.NET.Maintaining Database Consistency with Transactions
    Article Author: Scott Mitchell
    Published Date: July 27, 2005
    Article URL: http://www.4GuysFromRolla.com/articles/072705-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers