I am running into situations in my application where I need to use table lock hints or set the transaction isolation level to something other than the default Read Committed, in order to resolve deadlock issues. I am using a service oriented architecture, with each service call operating as an atomic operation, and Linq To Sql is serving as a lightweight DAL. Each service call calls my Business Layer and declares a new transaction like this:
using (var scope = new TransactionScope())
{
// Get datacontext
// do Business Logic stuff, including database operations via Linq To Sql
// Save transaction
scope.Complete();
}
The problem is sometimes I have complicated business logic that requires many database operations. Some reads, some writes, some reads for updating, etc, all within the same service call, and thus the same transaction.
I have read about the inability of Linq To Sql to add table lock hints to your linq query, with the suggested solution of using TransactionScope isolation levels instead. That's great and all, but in my situation, where each Transaction is for the purpose of an atomic service call, I don't see where this would work. For example, if I need to read one table without locking and dirty reads may be OK, and turn around and do another read for the purpose of updating, and do an update. I don't want to set Read Uncommitted for the entire transaction, only one particular read, so what do I do?
Is there not an extension I can implement that will allow me to add table lock hints, without using views or stored procedures, or using datacontext.ExecuteQuery("my raw sql string here")