LINQ-To-SQL NOLOCK (NOT ReadUncommitted)

Question

I searched for a while here and elsewhere and cannot find a good answer to the question why Linq-TO-SQL with NOLOCK is not possible.

Each time I try to apply the with (NOLOCK) hint to the Linq-To-SQL context (applies to a 1 sql statement), people often respond to force the transaction (TransactionScope) to set IsolationLevel to ReadUncommitted. Well, they rarely say that this leads to the fact that the connection for opening a transaction (which I also read somewhere, must be closed manually).

Using ReadUncommitted in my application as is, is really not so good. I am currently using contextual operators for the same connection to each other. How:

using( var ctx1 = new Context()) { ... some code here ... using( var ctx2 = new Context()) { ... some code here ... using( var ctx3 = new Context()) { ... some code here ... } ... some code here ... } ... some code here ... } 

With a total runtime of 1 sec and many users at the same time, changing the isolation level will cause the contexts to wait for each other to release the connection, because all connections in the connection pool are used.

Thus, one (for many reasons) to switch to "nolock" is to avoid deadlocks (we now have one client deadlock per day). The consequence of this is another kind of dead end and does not really solve my problem.

So I know what I can do:

• Avoid nested using the same connection
• Increase server connection pool size

But my problem is:

• This is not possible in the near future due to many lines of code re-factoring, and it will conflict with the architecture (without even thinking about whether it is good or bad).
• Although this, of course, will work, this is what I would call a “symptomatic treatment” - because I don’t know how much the application will grow, and if it is a reliable solution for the future (and then I could end up with an even worse situation when far more users are affected.

My thoughts:

• Could it be true that NoLock is impossible (for each operator without starting a transaction)?
• If 1 is true - is it true, no one else got this problem and solved it in the general linq to sql modification?
• If 2 is true - why is this not a problem for others?
  • Is there any other workaround I couldn't look at?
  • Is using the same connection (nested) many times so bad practice that no one has this problem?

Answer 1

1: LINQ-to-SQL really does not allow you to provide hints such as NOLOCK ; however, you can write your own TSQL and use ExecuteQuery<T> , etc.

2: to solve in an elegant way would be quite difficult, to be honest; and there is a good chance that you will use it improperly. For example, in a deadlock scenario, I would say that this is actually an UPDLOCK that you should use (during the first read) to make sure that the first read occupies the write lock; this prevents a second request receiving a read lock, so you usually get a lock instead of a deadlock.

3: using a connection is not necessarily a big problem (although note that new Context() will not generally have access to a shared connection in order to use a connection that you would use new Context(connection) ). If you see this problem, there are three possible solutions (if we exclude the use of ORM with hint support):

• using an explicit transaction (which should not be a TransactionScope - it could be a connection-level transaction) to indicate the isolation level
• write your own TSQL with tips
• use the isolation level at the connection level (noting the disclaimer that I added as a comment)

IIRC there is also a way to subclass the data context and override some transaction code to control the isolation level for the transactions that it creates internally.