Sunday, April 02, 2006

System.Transactions - Nesting Scopes, Enlist Connection in Transaction

In a previous post I decribed how the Lightweight Transaction Manager (LTM) of System.Transactions has the bad habit of "promoting" to full-blown MS DTC transactions even when using a single SQL Server 2005 database. You can use the .Suppress option to exclude some code from being part of a transaction and thus stay LTM even when using multiple open connections against the SS2K5 database.

To circumvent this limitation (feature), I have implemented a data session object used to pass an open SqlConnection object around my biz logic and data access logic. Last week, when implementing a unit test for some logic involving a method in the call stack using the .Suppress option, I noted that changes made to the data was not rolled back when the error I was debugging, occurred.

Some experimentation with nested transaction scopes lead me to conclude that I needed to open the common, shared connection within a TransactionScopeOption.Required using block before passing it around to get the correct behavior (rollback on error, etc). It seemed that opening the connection in a .Suppress block would suppress transactions even in nested 'required' scopes.

I posted my findings at and got an "expected behavior" from Alazel Acheson of the ADO.NET team. The explanation is that the connection will enlist in a transaction only when opened, thus when opened in a suppress block, the connection will never use a transaction. To ensure that a connection gets enlisted in a transaction, you need to actually open the connection within each using TransactionScope block.

The need to stay LTM is a very common requirement in the System.Transactions community, and Acheson has implemented a DbConnectionScope class that simplifies the process of using a common connection in nested transaction scopes. The DbConnectionScope class has been further refined to include nesting options to provide detailed connection reuse control. Good work, Alazel!

I recommend anyone who has implemented their own LTM data session object to switch to the DbConnectionScope. I strongly advice never to roll your own transaction management mechanism, the chances are that you will not be able to make it 100% bulletproof.


Anonymous said...

That's interesting but seems a bit messy or a lot just to code this simple code.

Yea I know this is like the only work around based on posts I've found but what if you need to add a parameter to your command?

You wouldn't want to do this in the InnerMethod as the params are specific to each Query call.

If you're calling 2 queries or procs inside a transaction scope, half the time you're gonna need to to add parameters to the command object between these 2 lines in the InnerMethod:

SqlCommand cmd = conn.CreateCommand();
//add parames here but for what query call!! bad

Maybe I'm missing it but you should be able to add these params to your SqlCommand object inside the OuterMethod.

Kjell-Sverre Jerijærvi said...

I don't see how SqlCommand params relates to having a shared, opened SqlConnection used across all the SqlCommand objects.

You can use as many different SqlCommand objects as you like, just remember to attach the shared SqlCommand/DbConnectionScope to the commands to stay LTM.