Use different Isolation Level within a handler

1

I have a query I need to execute within a handler that should run with a different isolation level than the endpoint is using.

We are on MSMQ with SQL persistence.

This is a query against a vendor database, that has an issue where it will do a table lock and block all other SQL queries in progress, even if your just reading data, when using Read Committed. Being a vendor database I’m not able to really dig into it and solve that issue for them.

So, a while back we switched queries against this view to Read Uncomitted and vastly reduced the blocking against the vendor database. Near accurate results are all that is required in this particular case.

Therefore when translating from our monolith in to Nservice Bus this chunk of code needs to come over.

  List<Data.u_OpenCustomerBackOrders> backOrders = null;
  var tranOptions = new System.Transactions.TransactionOptions()
       {
        IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted,
        Timeout = new TimeSpan(0, 1, 30)
       };
using (new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, tranOptions))
      {
      var db = new Data.InfinityEntities();
      using (db)
           {
             backOrders = (from bo in db.u_OpenCustomerBackOrders
                                     where bo.InvoiceID == message.infinityInvoiceId
                                     select bo).ToList();
           }
     }

Unfortunately, this results in the following error:

The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope.

I know I can change the entire endpoint to use read uncommitted as documented here:

However, that is not how I want the entire endpoint to behave for obvious reasons.

Is there a way around this? Can I run a single query in a different isolation level than what the endpoint is using?

Any help is appreciated.

-BW

2

First of all, you can use a nested transaction with a different isolation level as long as it is a new transaction, thus not participating in the outer transaction. That probably is no issue as you already want to use READ UNCOMMITTED meaning that you are not interested in inconsistency at all.

var tranOptions = new TransactionOptions()
{
  IsolationLevel = IsolationLevel.ReadUncommitted,
  Timeout = new TimeSpan(0, 1, 30)
};
using (new TransactionScope(TransactionScopeOption.RequiresNew, tranOptions)) // <-- RequiresNew instead of Required
{
  var db = new Data.InfinityEntities();
  using (db)
  {
    backOrders = (from bo in db.u_OpenCustomerBackOrders
                  where bo.InvoiceID == message.infinityInvoiceId
                  select bo).ToList();
  }
}

Second, you are doing a query. Without knowing what happens here, if this result is only used on a screen then you shouldn’t use messaging here. Instead, the UI should go directly to the database and run this query.

If this result is not used in a query for a UI but to modify data, then I advise really not to use READ UNCOMMITTED to actually make changes to business data. If you are using that pattern in your application then that eventually will result in data corruption in the future.

3

Thank you,

RequiresNew is what I was looking for.

Yes I know the ramifications of using this pattern, and thus why I don’t want any other part of the endpoint running with read uncommitted.

One goal of ours is to obsolete the system we are talking to here using sort of a strangler pattern.

Their database/system has a number of issues i’ll not go into here, but suffice it to say, if this view is not read from using read uncommitted it can have locking issues show up across all of the clients the vendor supplies to use this database. Everywhere writes are performed it is done in a read committed pattern.

Once obsoleted, this endpoint and this query will also be obsolete and will go away.

Thank you for the reply