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