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?

marc_s
  • 9,052
  • 6
  • 46
  • 52
TheHurt
  • 143
  • 3

1 Answers1

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)

Community
  • 1
gbn
  • 70,237
  • 8
  • 167
  • 244