SubSonic2.2 SharedDbConnectionScope and TransactionScope Transaction Confusion

Question

ARGH!!!

There seems to be a little confusion surrounding the SharedDbConnectionScope and TransactionScope objects to enable wrapping your SubSonic queries within a transaction.

The docs suggest specifying the using SharedDbConnectionScope wrapped around the using TransactionScope...

using(SharedDbConnectionScope scope = new SharedDbConnectionScope())
{
  using(TransactionScope ts = new TransactionScope())
  {
    // do something
    ts.Complete();
  }
}

Then other question here such as Subsonic: Using SharedDbConnectionScope together with TransactionScope seems to be broken suggest the docs are wrong and the two objects should be the other way around...

using(TransactionScope ts = new TransactionScope())
{
  using(SharedDbConnectionScope scope = new SharedDbConnectionScope())
  {
    // do something
    ts.Complete();
  }
}

But looking into the source code I am even more confused.

In the SqlQuery.cs code file it has a number of ExecuteTransaction overloads. For example...

public static void ExecuteTransaction(List<SqlQuery> queries)
{
  using(SharedDbConnectionScope scope = new SharedDbConnectionScope())
  {
    using(TransactionScope ts = new TransactionScope())
    {
      foreach(SqlQuery q in queries)
        q.Execute();
    }
  }
}

Umm... Interesting... Matches the docs but... Where's the ts.Complete() call?

How is that supposed to commit the transaction? As far as I can see it will always rollback. And it is the same for all the ExecuteTransaction overloads!

But here is the real kicker...

In the TransactionWithDtcOffTests.cs code has some nice tests except they have set up the SharedDbConnectionScope and TransactionScope around the other way!

using(TransactionScope ts = new TransactionScope())
{
  using(SharedDbConnectionScope connScope = new SharedDbConnectionScope())
  {
    // <snip />
  }
}

I haven't had the opportunity to run the tests for SubSonic 2.2 but I assume someone has and they passed..

Finally...

Can someone give me the definitive answer to how Transactions in SubSonic2.2 should be set up? Are the docs indeed wrong? Does the source for the ExecuteTransaction overloads and tests be aligned to whichever way is actually correct?

Answer 1

The SharedConnectionScope (SCS) block must be inside a TransactionScope (TS). The purpose of the SCS is to prevent escalating the transaction to the MSDTC if possible, so having the TS using block inside of a SCS using block makes little sense to me. In any case, every TS block must have a Complete() call for the transaction to be committed.

Answer 2

Personally i found that when using SQL 2005, SCS must be inside TS and when using SQL 2000 (with MSDTC), SCS must wrap TS. I hope this helps...