Is using CRUD stored procedures against a view with NOLOCK bad?

Question

(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?

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 '11 at 14:23

score 7 · Accepted Answer · edited May 23 '17 at 11:33

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)

edited May 23 '17 at 11:33

Community

1

answered Jun 28 '11 at 06:19

gbn

69,809
8
163
243

3

When will people learn that NOLOCK is not the turbo button for database systems?!? – SQLChicken Jun 28 '11 at 13:06
1

"But we've always done it that way!" -- Makes me want to go all ITG. – Adrien Jun 29 '11 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 '11 at 18:17

Is using CRUD stored procedures against a view with NOLOCK bad?

1 Answers1