3

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?

Igand
  • 1,161
  • 1
  • 15
  • 25
  • 1
    Does this answer your question? [Inner TransactionScope with different IsolationLevel, how can it be achieved?](https://stackoverflow.com/questions/3557435/inner-transactionscope-with-different-isolationlevel-how-can-it-be-achieved) – pinkfloydx33 May 09 '20 at 12:26
  • Not really. It's pretty much same question - so I apologize, I should have searched more, i'd expect such question to mention the unique part of the exception. But answer to "why its ok to change isolation level here but not there" is not there as well (as noted by OP in comment there) :( So no, it doesn't really answer it. – Igand May 10 '20 at 08:28
  • I was marking it as a duplicate question. The "does this answer" is part of stack overflows updates auto-comment text when marking dupe questions – pinkfloydx33 May 10 '20 at 08:36
  • I see. Now I noticed the kinda acceptable answer is in the comment bellow the accepted answer - It's not anything more, than I noted in the last paragraph of my question, but I guess no one will add anything more. Guess I shall vote close on my question and edit the answer there, so that it's more visible next time? – Igand May 10 '20 at 08:56
  • On second thought, my question is slightly different and more directed on "why is there the difference" - I'll do proper edits here. – Igand May 10 '20 at 09:02
  • I think it's subtlety answered over there. TransactionScope isnt just used for SQL. It can be used for other things (file system operations for example). Those might not support changing the isolation level like SQL sort of does. Perhaps you can look at dependent transactions and see if they support it. I was looking yesterday and that's how I ended up on the duplicate – pinkfloydx33 May 10 '20 at 09:05
  • I aggree. Due to the subtlety I decided to extract the to me most enlightening part, the comment, as an answer in here. I hope my post adds more information than clutter this way. – Igand May 10 '20 at 09:19

1 Answers1

0

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".

Igand
  • 1,161
  • 1
  • 15
  • 25