4

(I am crossposting this from SO on the suggestion of a commenter)

Our DBAs have created a pattern where our database layer is exposed to EF via views and CRUD stored procedures. The CRUD works against the view. All the views have the NOLOCK hint. From what I understand, NOLOCK is a dirty read, and that makes me nervous. Our databases are not high volume, but it seems like blanket NOLOCK is not very scalable while maintaining data integrity. I get that the decoupling is a good idea, but the problem there is we don't. Our externally exposed objects look just like our views which map 1 to 1 with our tables.

"If we want to change the underlying data model, we can." ... but we don't. I won't touch on what a PITA this all is from a VS/EF tooling viewpoint.

Is NOLOCK used in this situation bad? Since our database looks exactly like our class library, I think it makes sense to just get rid of the whole view/stored procedure layer and hit the DB direct from EF, does it?

Improve this question
1
  • You can read about the NOLOCK hint in this question. It's not always needed, you should use it only upon need.
    – Marian
    Jun 28, 2011 at 14:23

1 Answer 1

Reset to default
7

Using NOLOCK blindly is idiotic. There is no best practice at all for this. This could be construed as utter arrogance: that someone knows better than MS who chose READ COMMITTED as the default

From SO: "Using NOLOCK Hint in EF4?". As well as me calling the DBA a muppet, a guy from the MS EF team answers too. And SQL - when should you use “with (nolock)”

In addition, NOLOCK is ignored for updates, inserts, deletes.

Using 1:1 views is idiotic too. It adds no value. From programmers.se "What popular “best practices” are not always best, and why?".

Using a view to hide table changes ad interim is OK, but having the extra layer based on dogma is pointless. And I bet you don't have WITH SCHEMABINDING so the view can differ from the table anyway (SO)

Improve this answer
3
  • 3
    When will people learn that NOLOCK is not the turbo button for database systems?!?
    – SQLChicken
    Jun 28, 2011 at 13:06
  • 1
    "But we've always done it that way!" -- Makes me want to go all ITG.
    – Adrien
    Jun 29, 2011 at 22:10
  • Most of my processes use NOLOCK pretty much everywhere, but I think it's OK in our environment - DW with monthly ETL loads, and never any contention since there's no activity during the load process.
    – JNK
    Jul 12, 2011 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.