Why inner TransactionScope's IsolationLevel cannot be different, while SQL transactions can be?

user_4547594 0
Viewed 356

Changing isolation level within database transaction is ok, including case when you join one transaction to already running one. You just change the way you handle locks from now on. Using Sql Server, this runs without problems:

begin transaction 
set transaction isolation level serializable;
select * from FooTable;

set transaction isolation level read committed;
select * from FooTable;

begin transaction
set transaction isolation level serializable;
select * from FooTable;
--transaction_isolation_level can be observed as 4 (serializable)

But, when using .NET TransactionScope to create transaction in said Sql Server like this(C#, xUnit):

[Theory]
[AutoFixtureMagicToGetParameterInstances]
void ZmenaIzolacniUrovneVedeKVyjimce(IFooDao sut, Foo foo)
{
    var tranOpts = new TransactionOptions()
    {
        IsolationLevel = IsolationLevel.Serializable,
        Timeout = TimeSpan.FromSeconds(60)
    };
    var tranOpts2 = new TransactionOptions()
    {
        IsolationLevel = IsolationLevel.ReadCommitted,
        Timeout = TimeSpan.FromSeconds(60)
    };
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, tranOpts))
    {
        sut.SelectFoos();
        using (var transactionScope2 = new TransactionScope(TransactionScopeOption.Required, tranOpts2))
        {
            sut.SelectFoos();
        }
    }
}

leads to exception:

System.ArgumentException : The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope.
 Parameter name: transactionOptions.IsolationLevel

Why designer of TransactionScope deemed it necessary to immediately throw an exception?

I'd expect behavior would be the same at least as long only database resources are involved. Is there something about TransactionScope I'm missing or is it just because reasonable behavior cannot be guaranteed across all possible enlisted resources?

transactionscope transactions c#
1 Answers

As stated in comments here Inner TransactionScope with different IsolationLevel, how can it be achieved?

TransactionScope is not limited to use with SQL Server, it can allow distributed transactions across processes/systems. So it is stricter than what SQL Server allows, likely to simplify the complexity of ensuring consistency across the systems than support distributed transactions. – AaronLS

So answer basically seems to boil down to "TransactionScope may have way more on it's plate than just database transaction and so it forbids complexities like changing isolation levels".

user_45475940
Related