Menu
We're evaluating EF4 and my DBA says we must use the NOLOCK hint in all our SELECT statements. So I'm looking into how to make this happen when using EF4.
I've read the different ideas on how to make this happen in EF4, but all seem like a work around and not sanctioned by Microsoft or EF4. What is the "official Microsoft" response to someone who wants their SELECT statement(s) to include the NOLOCK hint when using LINQ-to-SQL / LINQ-to-Entities and EF4?
By the way, the absolute best information I have found was right here and I encourage everyone interested in this topic to read this thread.
Thanks.
644
The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
With (nolock) is preferred when you really must use nolock. nolock is not deprecated yet, but is listed as deprecated in sql 2016. Yes, you should change them. Not using the WITH () syntax for hints is deprecated and some hints do not work without it.
NOLOCK tells the server you are happy to read uncommitted data, allowing the user to retrieve the data without being affected by locks set by other queries; it doesn't mean "this query must not lock tables". Without seeing your tables, data, and the query there's limited help anyone can give.
As John said, using the NOLOCK will not guarantee that a deadlock does not occur. It is a strong suggestion to the query optimizer - but may not be discarded if the optimizer feels it unnecessary. Rather than use the nolock, it would be better to tune the queries. The answer's simple.
NOLOCK = "READ UNCOMMITTED" = dirty reads
I'd assume MS knows why they chose the default isolation level as "READ COMMITTED"
NOLOCK, in fact any hint, should be used very judiciously: not by default.
Your DBA is a muppet. See this (SO): What can happen as a result of using (nolock) on every SELECT in SQL Sever?. If you happen to work at a bank, or any institution where I may have an account please let me know so I can close it.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
