Transaction Timeouts in C#

After nearly 14 years doing Java development, I took the plunge this year and started working in .Net. It has it pluses and minuses over the Java environment and suite of tools but overall I have enjoyed the change. Below is one example of my many experiences in learning the Microsoft way.

As part of a oncology treatment planning GUI application, I created a Windows service to periodically pull oncology records from a Sybase database, aggregate the data and place it into a SQL Server 2008 R2 database. Putting the plan data into a database separate from the treatment database helps us ensure patient safety both by restricting access to the original treatment system as well as guaranteeing no write backs to the treatment database.

The records are written to the SQL Server database in a transaction using the Microsoft Distributed Transaction Coordinator (MSDTC). The code that does this is written in C# and uses the TransactionScope class. The records are pulled over and written in a batch as a single transaction, the transactions are not a record-by-record basis. This was the source of my trouble.

Because the data pull process will run at least daily (as well as on demand), typically the amount of data moved will be small. However, to validate extreme conditions, we ran tests on large data sets to ensure the application could handle large loads. When we deployed the software to our customer’s facility for beta testing it worked fine until they wanted to a much larger set of test data. This meant a pull with considerably more data than is normal. When we tested the same exact scenario in our lab on the East Coast (U.S.) to their test Sybase database located over 3000 miles away it worked fine. However, once we ran it on the customer’s server we began encountering problems. It turned out, the customer was testing on much older hardware with less bandwidth - by an order of magnitude.

Of course the first thing I checked was the debug log (log4net was really helpful here) and noticed a stack trace with a transaction error message during the batch write to SQL Server:

System.InvalidOperationException: The transaction associated with the 

current connection has completed but has not been disposed. The transaction must be 

disposed before the connection can be used to execute SQL statements.

My first thought on seeing this error was that it was a data problem. I eyeballed the data in the database to see if anything stood out. It did not, and the same exact data worked just fine in our lab. I ran the test a few more times and noticed it did not fail at the same record but it did fail after processing around the same number of records. For example, if it failed at record ID 1474 on the first run it might fail at 1453 on the second run, 1500 on the third, and so on. From this evidence, it didn’t take long to figure out the error was being thrown at around one minute into the transactional write. From there it quickly became apparent that the transaction was timing out. I did a bit of research (thanks Google) to figure out where the transaction timeout was stored. The first place I checked was the SQL Server database settings, but it became quickly

apparent that the transaction timeout was controlled outside of the database. Next, I checked the Transaction Timeout setting in the Component Services:

I changed this to five minutes, restarted the MSDTC and my application. I retested, but no luck. The same transaction error occurred at the same time – one minute.

Back to Google to find another alternative. This time, I found a forum that talked about passing the TransactionScope a timeout parameter. I went back to the code and created a Timespan object of five minutes for the timeout (externally configurable of course).

using (TransactionScope tsc = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0,5, 0)))

{

     SQL Server inserts here...

     tsc.Complete();

}

After testing locally, I rolled it out to the customer’s beta test and it ran as expected, completing the transaction.