I've just started experimenting with Linq to SQL and so far I'm very impressed.
But ...
(there's always a 'but')
... the convention where I work is to use the 'WITH (NOLOCK)' hint on all select statements and the SQL generated by Linq to SQL doesn't do this.
Is there a way to get the Linq to SQL generation code to use the NOLOCK hint in the SQL statements? I haven't been able to find any details on whether it is possible to 'interfere' with the SQL generation at all.
Herbie
-
Follow
Oops, something didn't work.
Sign In to be begin to follow this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.Getting "follow" information
Start following this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.Removing your "follow"
Setting up your "follow"
Did you know you can
sign up for email notifications?
-
Dr Herbie
Horses for courses
-
Dr Herbie
Horses for courses
As always, I find an answer within a few minutes of posting a question here:
A ReadUncommitted isolation level in a TransactionScope has the same effect as NOLOCK (apparently).
See this blog entry for snippet of code ...
Herbie
-
Glad I could help, Dr Herbie!Dr Herbie said:As always, I find an answer within a few minutes of posting a question here:
A ReadUncommitted isolation level in a TransactionScope has the same effect as NOLOCK (apparently).
See this blog entry for snippet of code ...
Herbie
-
Matthew van Eerde
AKA Maurits
> ... the convention where I work is to use the 'WITH (NOLOCK)' hint on all select statements
Why? You can get weird results if you SELECT while there's an INSERT or UPDATE or DELETE in effect. -
Matthew van Eerde
AKA Maurits
Reading up on it further, it looks like you're trying to avoid a situation where your SELECT blocks INSERT/UPDATE/DELETEs from other connections. Is that correct?
There are a couple of other options (assuming SQL Server 2005) which would prevent the SELECTs from getting data that was never committed while still minimizing contention problems:
1) You can use the SNAPSHOT isolation level. I don't see that in the IsolationLevel property doc but I do see it in the Table Hint doc, so this may not work yet from LINQ.
2) Set the database option READ_COMMITTED_SNAPSHOT to ON. This would allow you to use the default "Read committed" isolation level, but would affect every connection, even those that didn't use LINQ. -
Dr Herbie
Horses for courses
You're right, we want the NOLOCK hint to prevent blocking data writes -- this isn't a problem for our scenarios and helps a lot with performance.Matthew van Eerde said:Reading up on it further, it looks like you're trying to avoid a situation where your SELECT blocks INSERT/UPDATE/DELETEs from other connections. Is that correct?
There are a couple of other options (assuming SQL Server 2005) which would prevent the SELECTs from getting data that was never committed while still minimizing contention problems:
1) You can use the SNAPSHOT isolation level. I don't see that in the IsolationLevel property doc but I do see it in the Table Hint doc, so this may not work yet from LINQ.
2) Set the database option READ_COMMITTED_SNAPSHOT to ON. This would allow you to use the default "Read committed" isolation level, but would affect every connection, even those that didn't use LINQ.
We're running on SQL2000 for live data, so all options must be backward compatible. The isolation level changes would be the granularity of control that we are looking for.
We're currently not going with Linq to SQL and sticking to plain old datasets (PODS!) for now. We'll look at the entity framework stuff when it comes out of beta ...
Herbie
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.