Database Tip: Managing Transactions with a Stack

Although a lot of database work in many web applications involves single table queries and changes, in many cases you make multiple changes to a database. In these cases, you should consider using transactions. A transaction defines a unit of work to be performed, and for the transaction to complete, each part of it has to complete successfully. If everything works, the transaction is committed to the database. If not, all the changes are rolled back. You also have the option to nest transactions, in case you have a very complicated unit of work that is broken into “sub-units” of work.

In some of my own code, I’ve created a method to manage these nested transactions by using a Stack collection. A stack is a basic computer science concept that helps implement a last-in, first-out structure. When you start a transaction, you push the transaction onto the stack. When you commit the transaction, you pop it off the stack. If you have to roll back a transaction, you need to roll back each nested transaction in reverse order. The stack is perfect for this arrangement.

Here are the functions I added to my database class:

public void BeginTransaction()
{
   SqlTransaction currentTrans = currentConnection.BeginTransaction();
   transactions.Push(currentTrans);
}

public void RollbackTransaction()
{
   SqlTransaction currentTrans;
   while (transactions.Count > 0)
   {
      currentTrans = (SqlTransaction)transactions.Pop();
      currentTrans.Rollback();
   }
}

public void CommitTransaction()
{
   SqlTransaction currentTrans = (SqlTransaction)transactions.Peek();
   currentTrans.Commit();
   transactions.Pop();
}

public SqlTransaction CurrentTransaction
{
   get
   {
      if (transactions.Count > 0)
         return (SqlTransaction)transactions.Peek();
      else
         return null;
   }
}

I have the transactions variable declared as:

private Stack transactions = new Stack();

Each time I run a function within the Database class, I look to see whether the CurrentTransaction is null; if not, I set the Transaction property of each SqlCommand object equal to it. This enlists the command in the transaction. When I’m done with all my work, I call CommitTransaction, which commits the current transaction to the database. If any errors occur, I can call RollbackTransaction, which rolls back all pending transactions. A failure in one part of the transaction means the entire thing needs to be undone in this model.

In regards to using the Stack object, the Peek method returns the top object without removing it from the stack; doing this lets you use the object without removing it from the stack, because a transaction may have three or four actions before it’s complete.

About the Author

Eric Smith is the owner of Northstar Computer Systems, a web-hosting company based in Indianapolis, Indiana. He is also a MCT and MCSD who has been developing with .NET since 2001. In addition, he has written or contributed to 12 books covering .NET, ASP, and Visual Basic. Send him your questions and feedback via e-mail at questions@techniquescentral.com.