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.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Organizations are increasingly gravitating toward mobile-first application development as they assess the need to revamp their application portfolios to support touch computing and mobility. Consumerization has brought higher expectations for application usability along with the mobile devices themselves. Enterprises are increasingly shifting their new application acquisitions and development efforts toward mobile platforms. With this backdrop, it is natural to expect application platform vendors to invest in …

  • Live Event Date: July 8, 2015 @ 2:00 p.m. ET / 11:00 a.m. PT With business driving the increasing adoption of agile methodologies such as Continuous Integration and Continuous Deployment, the need for agile database tooling has become even more important. In many companies, the database development lifecycle has become the bottleneck in an otherwise agile delivery process. As such, database professionals are under increasing pressure to shorten delivery timelines even further, only increasing the stakes …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date