CODEWITHSUNDEEP
c#linq-to-sqltransactionsnolock
Nick Niebling
Nick Niebling

Reputation: 317

LINQ-To-SQL NOLOCK (NOT ReadUncommitted)

I've been searching for some time now in here and other places and can't find a good answer to why Linq-TO-SQL with NOLOCK is not possible..

Every time I search for how to apply the with(NOLOCK) hint to a Linq-To-SQL context (applied to 1 sql statement) people often answer to force a transaction (TransactionScope) with IsolationLevel set to ReadUncommitted. Well - they rarely tell this causes the connection to open an transaction (that I've also read somewhere must be ensured closed manually).

Using ReadUncommitted in my application as is, is really not that good. Right now I've got using context statements for the same connection within each other. Like:

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 execution time of 1 sec and many users on the same time, changing the isolation level will cause the contexts to wait for each other to release a connection because all the connections in the connection pool is being used.

So one (of many reasons) for changing to "nolock" is to avoid deadlocks (right now we have 1 customer deadlock per day). The consequence of above is just another kind of deadlock and really doesn't solve my issue.

So what I know I could do is:

  1. Avoid nested usage of same connection
  2. Increase the connection pool size at the server

But my problem is:

  1. This is not possible within near future because of many lines of code re-factoring and it will conflict with the architecture (without even starting to comment whether this is good or bad)
  2. Even though this of course will work, this is what I would call "symptomatic treatment" - as I don't know how much the application will grow and if this is a reliable solution for the future (and then I might end up with a even worse situation with a lot more users being affected)

My thoughts are:

  1. Can it really be true that NoLock is not possible (for each statement without starting transactions)?
  2. If 1 is true - can it really be true no one other got this problem and solved it in a generic linq to sql modification?
  3. If 2 is true - why is this not a issue for others?
    1. Is there another workaround I havn't looked at maybe?
    2. Is the using of the same connection (nested) many times so bad practice that no-one has this issue?

Upvotes: 4

Views: 3581

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063013

1: LINQ-to-SQL does indeed not allow you to indicate hints like NOLOCK; it is possible to write your own TSQL, though, and use ExecuteQuery<T> etc

2: to solve in an elegant way would be pretty complicated, frankly; and there's a strong chance that you would be using it inappropriately. For example, in the "deadlock" scenario, I would wager that actually it is UPDLOCK that you should be using (during the first read), to ensure that the first read takes a write lock; this prevents a second later query getting a read lock, so you generally get blocking instead of deadlock

3: using the connection isn't necessarily a big problem (although note that new Context() won't generally share a connection; to share a connection you would use new Context(connection)). If seeing this issue, there are three likely solutions (if we exclude "use an ORM with hint support"):

  • using an explicit transaction (which doesn't have to be TransactionScope - it can be a connection level transaction) to specify the isolation level
  • write your own TSQL with hints
  • use a connection-level isolation level (noting the caveat I added as a comment)

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

Upvotes: 2

Related Questions