Nested Transaction issues within custom Windows Service

Question

I have a custom Windows Service I recently upgraded to use TransactionScope for nested transactions. It worked fine locally on my old dev machine (XP sp3) and on a test server (Server 2003). However, it fails on my new Windows 7 machine as well as on 2008 Server. It was targeting 2.0 framework; I tried targeting 3.5 instead, but it still fails.

The strange part is really in how it fails; no exception is thrown. The service itself merely times out. I added tracing code, and it fails when opening the connection for Database lookup #2 below. I also enabled tracing for System.Transactions; it literally cuts out partway while writing the block for the failed connection.

We ran a SQL trace, but only the first lookup shows up. I put in code traces, and it gets to the trace the line before the second lookup, but nothing after. I've had the same experience hitting two different SQL servers (both are SQL 2005 running on Server 2003). The connection string is utilizing a SQL account (not Windows integration). All connections are against the same database in this case, but given the nature of the code it is being escalated to MSDTC.

Here's the basic code structure:

TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{
 // Database lookup #1

 TransactionOptions options = new TransactionOptions();
 options.IsolationLevel = Transaction.Current != null ? Transaction.Current.IsolationLevel : System.Transactions.IsolationLevel.ReadCommitted;
 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
 {
  // Database lookup #2; fails on connection.Open()
  // Database save (never reached)
  scope.Complete();<br/>
 }
 scope.Complete();<br/>
}

My local firewall is disabled. The service normally runs using Network Service, but I also tried my user account (same results).

The short of it is that I use the same general technique widely in my web applications and haven't had any issues. I pulled out the code and ran it fine within a local Windows Form application. If anyone has any additional debugging ideas (or, even better, solutions) I'd love to hear them.

Answer 1

Surround your database lookups with a new transactionscope and use the suppress option. The select locks records in sql2005 even with the nolock hint.