Nested/Child TransactionScope Rollback

Question

I am trying to nest TransactionScopes (.net 4.0) as you would nest Transactions in SQL Server, however it looks like they operate differently. I want my child transactions to be able to rollback if they fail, but allow the parent transaction to decide whether to commit/rollback the whole operation. The problem is when the first complete occurs, the transaction is rolled back. I realize that complete is different to commit.

A greatly simplified example of what I am trying to do:

static void Main(string[] args) {     using(var scope = new TransactionScope()) // Trn A     {         // Insert Data A          DoWork(true);         DoWork(false);          // Rollback or Commit     } }  // This class is a few layers down static void DoWork(bool fail) {     using(var scope = new TransactionScope()) // Trn B     {         // Update Data A          if(!fail)         {             scope.Complete();         }     } } 

I can't use the Suppress or RequiresNew options as Trn B relies on data inserted by Trn A. If I do use those options, Trn B is blocked by Trn A.

Any ideas how I would get it to work, or if it is even possible using the System.Transactions namespace?

Thanks

Answer 1

You're probably not going to like this answer, but...

Voting inside a nested scope

Although a nested scope can join the ambient transaction of the root scope, calling Complete in the nested scope has no affect on the root scope. Only if all the scopes from the root scope down to the last nested scope vote to commit the transaction, will the transaction be committed.

(From Implementing an Implicit Transaction using Transaction Scope)

The TransactionScope class unfortunately doesn't provide any mechanism (that I know of) for segregating units of work. It's all or nothing. You can prevent any transaction from occurring on a specific unit of work by using TransactionScopeOption.Suppress, but that is probably not what you want, as you would then lose atomicity for whatever is inside that scope.

There is only one "ambient" transaction when you use a TransactionScope. Once a TransactionScope is disposed or gets collected without Complete being executed, the whole ambient transaction gets rolled back; that's it, game over.

In fact, SQL Server doesn't support true nested transactions at all, although it is possible (though somewhat unintuitive) to achieve the same end result with appropriate use of SAVE TRAN statements. Re-implementing this logic as a Stored Procedure (or several of them) might be your best option if you require this particular behaviour.