0

I would like to run one particular Linq2Sql query in snapshot isolation mode without affecting the rest of my application.

However whenever I use code like

using (var t = new TransactionScope(TransactionScopeOption.Required, 
       new TransactionOptions { IsolationLevel =  IsolationLevel.Snapshot }))
{
    ...              
}

the Database is still using snapshot isolation after the transactionScope is finished.

I could add a dummy to reset the isolation mode like the following

using (var t = new TransactionScope(TransactionScopeOption.Required, 
       new TransactionOptions { IsolationLevel =  IsolationLevel.ReadCommitted }))
{
    ...              
}

But this does not solve the problem that any queries started by other threads in between times would also be running in snapshot isolation.

It looks like, if I want to introduce a TransactionScope anywhere in my program, I have to use it everywhere.

Is that the case, or am I missing something?

sgmoore
  • 15,694
  • 5
  • 43
  • 67
  • That's quite hard to believe to be honest. How do you know that snaphost isolation is still used outside the scope? – Evk Mar 17 '18 at 20:41
  • @Evk The Sql command from [here](https://stackoverflow.com/questions/19102726/get-current-net-transactionscope-isolationlevel) returns snapshot. Also I tested using a `select * from table with (readpast)` which fails because I am now longer in ReadCommitted IsolationLevel – sgmoore Mar 17 '18 at 21:18
  • Was hard to believe but you are right. You can find more info about this in question I marked as duplicate. – Evk Mar 17 '18 at 21:35

0 Answers0