TransactionScope question - how can I keep the DTC from getting involved in this?

Question

(I know the circumstances surrounding the DTC and promoting a transaction can be a bit mysterious to those of us not in the know, but let me show you how my company is doing things, and if you can tell me why the DTC is getting involved, and if possible, what I can do to avoid it, I'd be grateful.)

I have code running on an ASP.Net webserver. We have one database, SQL 2008.

Our data access code looks something like this-- We have a data access layer that uses a wrapper object for SQLConnections and SQLCommands. Typical use looks like this:

void method1()
{
    objDataObject = new DataAccessLayer();
    objDataObject.Connection = SomeConnectionMethod();
    SqlCommand objCommand = DataAccessUtils.CreateCommand(SomeStoredProc);

//create some SqlParameters, add them to the objCommand, etc....

objDataObject.BeginTransaction(IsolationLevel.ReadCommitted);
objDataObject.ExecuteNonQuery(objCommand);
objDataObject.CommitTransaction();
objDataObject.CloseConnection();
}

So indeed, a very thin wrapper around SqlClient, SqlConnection etc. I want to run several stored procs in a transaction, and the wrapper class doesn't allow me access to the SqlTransaction so I can't pass it from one component to the next. This led me to use a TransactionScope:

using (TransactionScope tx1 = new TransactionScope(TransactionScope.RequiresNew))
{
  method1();
  method2();
  method3();
  tx1.Complete();
}

When I do this, the DTC gets involved, and unfortunately our webservers don't have "allow remote clients" enabled in the MSDTC settings-- getting IT to allow that will be a fight.

I'd love to avoid DTC becoming involved but can I do it? Can I leave out the transactional calls in methods1-3() and just let the TransactionScope figure it all out?

Answer 1

What does SomeConnectionMethod() do? If it doesn't return the same connection in method1(), method2() and method3(), then you have a distributed transaction. So DTC will be involved to coordinate this transaction.

So you have to return the same connection in SomeConnectionMethod() to prevent DTC to involve in your transaction.

Answer 2

Why it doesn't work:

I think even if you would share the same connection (as suggested by larryq), it wouldn't work without getting DTC involved.

You want a Transaction scope to wrap those methods:

• method1
  • Begin Tran
  • Commit Tran
• method2
  • Begin Tran
  • Commit Tran
• method3
  • Begin Tran
  • Commit Tran

In each method you begin and commit. If something goes wrong in method3 you want to rollback everything, including the already committed transactions from method1&2. To do that you need a DTC transaction that is spanning all 3 methods.

Correct me if I am wrong, but I think you can use TransactionScope and avoid the use of DTC only if you do not begin and commit transactions inside the methods.

How could it work:

Do you really need to use transactions with begin and commit? Can't you do something like:

using (var scope = new TransactionScope(TransactionScopeOption.Required)) {
   objDataObject.ExecuteNonQuery(objCommand);
}

The Scope Required instead of RequiredNew uses an existing transaction if one already exists.

Please feel free to correct me if I got something wrong.