Is it possible to step outside of the current TransactionScope and insert data

Question

Consider the following:

using (var outerScope = new TransactionScope())
{
    InsertDataInTableOne();
    InsertDataInTableTwo();
    InsertDataInTableThree();
    outerScope.Complete();
}

Now I want to have InsertDataInTableOne to be run outside of the outerScope transaction. This is a simplified representation, as the TransactionScope is created several calls up the chain, so I can't just put the call to InsertDataInTableOne outside of the TransactionScope creation.

I also know this might not be a good practice, and we're working on a decent fix. But we need this quick fix at this moment.

using (var outerScope = new TransactionScope())
{
    using (var innerScope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        InsertDataInTableOne();
        innerScope.Complete();
    }
    InsertDataInTableTwo();
    InsertDataInTableThree();
    outerScope.Complete();
}

That didn't work. I even tried with creating a TransactionScope with Suppress first, and then the RequiresNew.

So is it possible to insert data immediately in the database, effectively ignoring the fact that you are in a TransactionScope?

The connection is made outside of these methods (actually, when entering the service that is called).

What you have *should* work, assuming that a: you remember to complete the two transactions (your current code does not show this; there is no `innerScope.Complete();` or `outerScope.Complete();`), and b: the connections involved are created / opened *inside* their respective methods (so inside `InsertDataInTableOne` etc). Can you clarify what happens currently? And can you clarify where the connections are created / opened? — Marc Gravell, Feb 05 '13 at 14:38
Forgot the Complete methods :) The connections are not created inside the methods, so that might be it? We are using NHibernate, so maybe we can do something with the Session and/or SessionFactory? — Peter, Feb 05 '13 at 14:50
yeah, `TransactionScope` is fussy about when connections are created/opened. I can't advise on NHibernate, I'm afraid - I don't use it enough to give a qualified answer. — Marc Gravell, Feb 05 '13 at 14:52

score 0 · Accepted Answer · answered Feb 06 '13 at 15:31

Not sure if this will help anyone, but you never know. The problem lies with a custom company-framework. The implementation makes it hard to have nested transactions, I believe. We fixed it now by removing the outer transaction (which is several layers up) and creating two separate transactions in our block:

using (var scopeOne = new TransactionScope())
{
    InsertDataInTableOne();
    scopeOne.Complete();
}

using (var scopeTwo = new TransactionScope())
{
    InsertDataInTableTwo();
    InsertDataInTableThree();
    scopeTwo.Complete();
}

Again, this is a solution very specific to our code. Using RequiresNew should normally do the trick. So if it doesn't work, maybe look into your own code first ;)

Another reason why I'm not a huge fan of do-it-all-company-frameworks.

Is it possible to step outside of the current TransactionScope and insert data

1 Answers1