Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(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/sproc layer and hit the DB direct from EF, does it?

share|improve this question
    
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

1 Answer 1

up vote 6 down vote accepted

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)

share|improve this answer
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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