ADO.NET Distributed transactions, loose ends.

There is still a lot of information related to using ADO.NET with System.Transactions Distributed Transactions, but in the interest of keeping the blog moving I am going to roll up most of the issues into this final post. Feel free to post questions if anything does not make too much sense.

Random ramblings:

The first connection _may_ be special. To make usage of promotable transactions transparent we have modified the api to artificially hide this.

If the first connection that we enlist into a LightweightTransaction is to a Sql Server 2005 we create a promotable transaction that is really nothing but a local transaction. For us to be able to promote this at a later time we need to make sure that we keep a reference. We have essentially hijacked this connection to talk to the server about the distributed transaction and bad things would happen if we tried to unenlist this connection. We used to be able to enlist and unenlist connections from distributed transactions at will (not that I would recommend this), and we could still do this if we wished for any connection other than these “special” connections. To avoid confusion and simplify the model we have decided to dis-allow unenlistment for all connections when using System.Transactions distributed transactions. Let me know if this is blocking an important scenario that we have not been able to think about.

Distributed transactions and threading:

The first question I got on TransactionScope was that since this sets a static Transaction.Current property would we run into trouble when using multiple threads? No, there is (or rather should not be) any problems with this scenario. Transaction.Current lives in thread local storage (TLS) so it is scoped per thread. If you want to enlist all the threads in the same distributed transaction it is slightly more complicated, the important concept to remember here is that commit may not be thread safe. The solution is to rely on the Clone command of the transaction, a cloned transaction can be rolled back, but it cannot commit. Pass a clone of your main transaction to each thread and manually enlist.

The TransactionOptions object for System.Transaction.Create(TransactionOptions)

I am not a big fan of this name, to me it seems like I am going to be able to set “traditional” transaction options like required, requiresnew etc. Instead this object allows you to set timeout and isolation level for a transaction. I have to point out that setting the isolation level on the transaction does not guarantee that your transaction will run in this mode, it is more of a suggestion to the resource manager.

TransactionOptions transactionoptions1 = new TransactionOptions();

transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;

    ICommittableTransaction icommittabletransaction1 = Transaction.Create(transactionoptions1); // LightweightCommittableTransaction

Standard Disclaimer, this post is provided “AS IS” and confers no rights.

Rambling out