0

I am trying to find working and optimal solution for the following situation:

  1. I have WebService in the cloud written on c#;
  2. Endpoint of this service can be called in a random time with random frequency;
  3. During handling of request we call method where we have read \ write operations on the table in our DB (I have simplified flow):
    • we read data from table
    • apply some validation rules
    • and according to specific rules update some fields and write data back to the table
    • commit transaction

The real problem is when we have 2 or 3 simultaneous request for one user - we easily get to the situation when we read invalid data from the table, because previous operation didn't save results (commit transaction) yet.

  • I've tried to setup isolation level, but it is reason for deadlock.
  • Usage of Upsert operation on DB level requires moving logic to stored procedure - it is not desirable.

So the question: what is the best way to synchronize work with DB correctly? Thank you for any suggestions.

Tools: Entity Framework 6.0, Azure SQL, Azure

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Michael Trusov
  • 43
  • 1
  • 4
  • That's not asynchronous, that's the standard way all databases work. If you don't want conflicting changes, use transactions (not nice) or optimistic concurrency (preferred). Optimistic concurrency is nothing new, it was introduce in the 1990s with disconnected recordsets. Check [Handling Concurrency with the Entity Framework 6 in an ASP.NET MVC 5 Application](https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application) – Panagiotis Kanavos Apr 05 '17 at 10:47
  • You can run into problems if you read an aggregate value, eg an account balance, change it then try to save it back. An `UPDATE SET Balance=Balance + 5 WHERE...` doesn't have any concurrency problems, it just increments the balance. If you read the value though, change it then try to write it back, you can get into conflicts. The solution here is to **NOT** use an ORM, or at least create an operation that generates an increment operation – Panagiotis Kanavos Apr 05 '17 at 10:51
  • Thanks, I'll try to investigate 'optimistic concurrency' approach. – Michael Trusov Apr 05 '17 at 11:11

0 Answers0