CODEWITHSUNDEEP
c#sqltransactions
Ilia G
Ilia G

Reputation: 10211

Trouble understanding TransactionScope

Given following code structure

func1()
{
    using (TransactionScope1)
    using (connection1)
    {
        "insert into table A"

        func2()

        "select from table B"
    }
}

func2()
{
    using (TransactionScope2)
    using (connection2)
    {
        foreach (x in y)
        {
            "select from table A"
            "insert into table B"
        }
    }
}

If TransactionScope2 was rolled back, then "select from table B" fails with The operation is not valid for the state of the transaction. As I understand TransactionScope2 is joining with the first one and rolls them both back. So I changed it to create it with TransactionScopeOption.RequiresNew, which in turn results in timeout on "select from table A".

The idea is to let second transaction read data from first one, but commit/rollback independently. My guess is IsolationLevel needs to change somehow, but I don't really understand the options for it.

EDIT: Perhaps it is easier to understand the problem if we name the func1 and 2. Basically func2 is a DAL functionality, and and func1 is a unit test for it. func1 creates some sample data, gets func2 to perform some actions on it, checks the result then rolls the entire thing back regardless of wherever func2 was successful or not.

EDIT2: after reading up some more I think the following code should work, but for some reason I still get timeouts on select from table A

func1()
{
    using (var txn = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
    using (connection1)
    {
        "insert into table A"
        func2()
        "select from table B"
    }
}

func2()
{
    using (var txn = new TransactionScope(TransactionScopeOption.RequiresNew))
    using (connection2)
    {
        foreach (x in y)
        {
            "select from table A"
            "insert into table B"
        }
    }
}

Upvotes: 2

Views: 472

Answers (2)

Henk Holterman
Henk Holterman

Reputation: 273169

You have a nested TransactionScope.

By default they are linked, without further action (RequiresNew) from you, the outer scope will fail (RollBack) when the inner one does.

This way they will be independent:

func2()
{
    using (TransactionScope2 = 
         new TransactionScope(TransactionScopeOption.RequiresNew)) 
    using (connection2)
    {
       ...
    }
}

Upvotes: 2

muratgu
muratgu

Reputation: 7311

Try IsolationLevel.ReadUncommitted.

Upvotes: 0

Related Questions