entity framework and dirty reads

Question

I have Entity Framework (.NET 4.0) going against SQL Server 2008. The database is (theoretically) getting updated during business hours -- delete, then insert, all through a transaction. Practically, it's not going to happen that often. But, I need to make sure I can always read data in the database. The application I'm writing will never do any types of writes to the data -- read-only.

If I do a dirty read, I can always access the data; the worst that happens is I get old data (which is acceptable). However, can I tell Entity Framework to always use dirty reads? Are there performance or data integrity issues I need to worry about if I set up EF this way? Or should I take a step back and see about rewriting the process that's doing the delete/insert process?

score 8 · Accepted Answer · edited May 23 '17 at 11:44

8

TransactionScope is your friend: Entity Framework with NOLOCK

edited May 23 '17 at 11:44

Community

1
1

answered Jan 11 '11 at 15:36

knight0323

738
2
6
12

score 5 · Answer 2 · answered Jan 11 '11 at 21:50

5

Don't use dirty reads. "The worst" isn't that you see old data. The worst is that you see uncommitted data. Stack Overflow uses snapshots rather than dirty reads to solve this problem. That's what I'd do, too.

answered Jan 11 '11 at 21:50

Craig Stuntz

125,891
12
252
273

Yes, but what if you're reading historical data? If you're certain the data being read isn't current, e.g. running reports, wouldn't `READ UNCOMMITED` be perfectly acceptable for the task? – Derreck Dean May 11 '18 at 18:31
If you are reading historical data then you also don't need dirty reads, because nothing is expected to be uncommitted. If you think you need dirty reads for some reason other than needing uncommitted data per se (such as locking or optimization) then you should fix rather than work around the actual problem. – Craig Stuntz May 12 '18 at 19:01
I was worried about the engine locking rows if multiple people were trying to access the same historical data. Is that something I should be worried about in this scenario? – Derreck Dean May 14 '18 at 17:44
Again, I think snapshot isolation will solve your concerns without dirty reads – Craig Stuntz May 14 '18 at 21:21
I've enabled RCSI on the system already, but I'm always concerned as downtime is verboten. Thank you for the input 7 years after the original post :) – Derreck Dean May 15 '18 at 13:32

score 1 · Answer 3 · answered Jan 17 '11 at 13:46

1

From the previous link, I found this, which also answers the question.

http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx

answered Jan 17 '11 at 13:46

bryanjonker

3,386
3
24
37

entity framework and dirty reads

3 Answers3