0

If I do a write to a (ms)SQL database and saves the changes using SaveChangesAsync(), is there a possibility that a future read on the database could read unsaved changes?

Factors could include, whether a different DbContext, thread or process is used to access the database.

Cardin
  • 5,148
  • 5
  • 36
  • 37

2 Answers2

3

Short Answer, NO (Tim P is mis-informed).

Calling DbContext.SaveChangesAsync will automagically create a Transaction for the duration of the saving.

This means that if any other thread tries to access the table, one of a number of things can occur.

Generally it means that the other Database call will block on the other thread, whilst the transaction is uncommited/not-rolledback.

Aron
  • 15,464
  • 3
  • 31
  • 64
  • So if only `SaveChanges` and `SaveChangesAsync` is used (i.e. not setting up custom `Transaction` or `IsolationLevel`), then no matter what kind of multi-thread/process you use, the EF calls are guaranteed to be safe? Would using `ExecuteSqlCommand` change things up a bit? – Cardin Sep 22 '15 at 03:01
  • @Cardin I am not sure about using ExecuteSqlCommand. However, it is typical to loop on ExecuteSqlCommand, which would be an issue. – Aron Sep 22 '15 at 03:05
  • Another question, if table A has a FK constraint that makes it dependent on table B, would the `Transaction` be smart enough to know to block write access on table A, if table B is still being populated elsewhere? – Cardin Sep 22 '15 at 04:18
  • Please read up on database transactions. It is unclear what you are asking, it sounds like a simple enough question, but until you develop the necessary jargon, I can't understand the question. By then you should have the answer. – Aron Sep 22 '15 at 04:20
1

Short answer: Yes.

It depends on several factors, such as how much you are saving to the database. If you're saving 1,000 rows/objects, and it's a slow database server, it's possible that this time window is wide enough that another thread is reading while (for example) row #879 has yet to be saved. And this has nothing to do with it being asynchronous. This is the normal concurrency problem in dealing with multi-user relational database systems.

Tim P.
  • 2,903
  • 24
  • 26
  • I see.... I managed to avoid concurrency issue by putting a lock{} on methods that touch the same tables for synchronous code. I don't think I need to make my writes asynchronous yet (probably not the bottleneck) but was hypothesizing. Thanks! – Cardin Sep 22 '15 at 02:38
  • @Cardin NOOOOOO you do NOT use `lock` on your DbContext. Databases have their own concurrency model which is far more effective than .net. – Aron Sep 22 '15 at 02:40
  • Last I checked `DbContext.SaveChanges` wraps the databases calls in a Transaction automagically. So that should be impossible. – Aron Sep 22 '15 at 02:41
  • Transactions will help if you block the calling thread with an "await". However, if you kick off the process with Async (without an await) then you do run a risk of it not finishing the SaveChanges() before your next line of code executes. – Tim P. Sep 22 '15 at 02:41