Why isolation level is ignored when using TransactionScopeOption.Suppress in .net

Question

I am trying to execute a query with an isolation level considered uncommitted as part of an existing transaction using LINQ TO SQL. If I use the option to suppress this transaction from the parent transaction, it seems that I am losing the ability to specify the isolation level. Using this code in LINQPad:

void Main() { var db = this; db.ExecuteQuery<string>(@"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); using (var trans = new TransactionScope(TransactionScopeOption.Required)) { // updates or inserts here using (new TransactionScope(TransactionScopeOption.Suppress, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) { db.ExecuteQuery<string>(@"SELECT CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions where session_id = @@SPID ").Dump("Inside the transaction"); } // updates or inserts here } } 

I get SERIALIZABLE result. Is there a way to run a query inside a transaction and change the isolation level to read uncommitted?

Answer 1

What you want to use is TransactionScopeOption.RequiresNew . The Supress option makes your host block work without an external transaction, as well as without creating a new one. What is he doing.

RequiresNew forces the creation of a new root transaction scope.

See the pivot table in this article for how the various options behave.

More about Suppress :

Suppressing is useful when you want to save executed operations in a section of code, and do not want to abort an external transaction if operations are not performed. For example, if you want to perform registration or audit operations, or when you want to publish events for subscribers, regardless of whether it completes or interrupts your external transaction.