September 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Nesting Transactions with a Single SQL Server Database

  • January 23, 2008
  • By Jani Järvinen
  • Send Email »
  • More Articles »

To run the sample application, you will need a connection to an SQL Server instance, such as a local SQL Server Express installation. In addition, you will need to create a test table called TEST with the following SQL statement:

CREATE TABLE [dbo].[Test] (
   [Name] [nvarchar](50) NULL)

Even though the table is trivial, it is sufficient to test the most important button of the application, the "Multi-Test" button. As the name of the button suggests, it tests multiple nested transactions. With the two check boxes below the button, you are able to indicate which transaction scopes (if any) should be marked complete.

Here is the code the button executes:

TransactionScope outerScope = new TransactionScope();
using (outerScope)
{
   string sql = "INSERT INTO [dbo].[test] ([name])
      VALUES ('AAAA')";
   RunSql(sql);
   TransactionScope innerScope = new TransactionScope();
   using (innerScope)
   {
      sql = "INSERT INTO [dbo].[test] ([name]) VALUES ('BBBB')";
      RunSql(sql);
      if (innerCheckBox.Checked) innerScope.Complete();
   }
   if (outerCheckBox.Checked) outerScope.Complete();
}

Here, two nested transaction scopes are created, and a simple INSERT statement is executed by both scopes. Depending on the check box states, those scopes are marked complete or simply disposed.

In addition to these functions, the sample application lets you do manual testing and transaction monitoring. With the buttons in the "Manual Testing" group, you are able to test transaction nesting interactively against the Northwind sample database. Beware, though, because not being able to complete and/or dispose a scope object can cause trouble like lingering transactions!

Reading SQL Server Transaction Data

The final thing the sample application is able to conduct is real-time monitoring of active transactions. To do this, the sample application can read SQL Server's Tempdb database, which contains system views that hold the data. For example, to get a list of currently active transactions, the following SQL statement can be used:

SELECT [transaction_id], [database_id],
   [database_transaction_state]
FROM [tempdb].[sys].[dm_tran_database_transactions]

This is exactly what the "Transaction Monitoring" part of the example application does. Note that you also could use, for example, Windows' Performance Monitor (perfmon.exe) to monitor active transactions. The proper counter is the Transactions counter under the given instance of SQL Server you wish to monitor.

Conclusion

In this article, you saw how nested transactions work when working with a single SQL Server database. As confirmed by the sample application, nested transactions with default options work in a simple, yet intuitive way. If the transaction is to succeed, all participating transactions must call Complete.

The transactional support can of course do much more than work with single databases. In fact, it supports even distributed transactions, intelligent escalation, config-file enabled tracing, and more. But, these are topics for another article.

Happy transactional programming!

Download the Code

You can download the code that accompanies this article here.

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP and has written dozens of magazine articles and published two books about software development. He is a group leader of a Finnish software development expert group at ITpro.fi. His frequently updated blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking his name at the top of the article.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel