CODEWITHSUNDEEP
c#sql-servertransactionsintegration-testingtransactionscope
Zout
Zout

Reputation: 924

C# Rollback Outer TransactionScope Regardless of What Happens In Nested TransactionScopes

First of all, I have read this similar question: Nested/Child TransactionScope Rollback but the answer didn't provide a solution and our questions are slightly different.

Basically, I have a database integration test which uses a transaction scope (In actuality the scope is managed in setup/teardown in an abstract class)

[Test]
public void MyTest()
{
    using(var outerScope = new TransactionScope())
    {
        Assert.True(_myService.MyMethod());
        var values = _myService.AnotherMethod();
    }
}

And MyService.MyMethod also uses a TransactionScope 

public bool MyMethod()
{
    using(var innerScope = new TransactionScope())
    using(var con = conFact.GetOpenConnection())
    {
        var cmd = con.CreateCommand();
        //set up and execute command
        if (isCheck) scope.Complete();
        return isCheck;
    }
}

So in theory, MyMethod only commits its changes if isCheck is true, but regardless of whether that transaction commits or not, when the method is tested, it will be rolled back.

It works as expected unless isCheck is false, in which case I get the following exception: System.Transactions.TransactionException : The operation is not valid for the state of the transaction.

I think what happened here was that since innerScope used TransactionScopeOption.Required, it joined the transaction used in outerScope. Once innerScope gets disposed when isCheck is false, outerScope is also disposed (This is what I don't want to happen!) so when I try to get another connection after MyMethod has been called, the outerScope is already disposed.

Alternatively, if I specify TransactionOption.RequiresNew, I get this exception: System.Data.SqlClient.SqlException : Timeout expired.

I have tried using a SqlTransaction with a specified savepoint, and different combinations of TransactionOption to no avail.

Upvotes: 1

Views: 743

Answers (1)

usr
usr

Reputation: 171178

There is no such thing as nested transactions. You can nest scopes but all that the nested scopes do is attach the the already running transaction. You cannot treat an inner scope independently from the other scope (except of course with RequiresNew which simply creates an independent transaction).

The functionality that you want does not exist in System.Transactions.

Savepoints are the only way to create something that looks like nested transactions. But then again SQL Server is prone to kill your entire transaction for arbitrary reasons. It is unpredictable what errors roll back the statement and what errors roll back the transaction. (Yes, this makes no sense.)

Upvotes: 2

Related Questions