I have asynchronous calls from Entity Framework to a stored procedure.
The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls.
The problem starts when the stored procedure is called 2-3 times asynchronous. Sometimes it returns wrong data.
Here is a flow of what happens (it's not accurate, it's just to demonstrate the problem):
- First thread enters the stored procedure.
- Second thread enters the stored procedure.
- First thread selects a table and inserts it to a temporary table.
- Second thread selects a table and inserts it to a temporary table.
- First thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 3) is also updated].
- Second thread updates the real table from the temporary table [the table that was selected in to the temporary table (section 4) is also updated].
The problem is that thread 2 doesn't use the new data that thread 1 updated. It uses the old data and therefore the update is not working.
What is the best solution for dealing with it? So that the code will be asynchronous as possible, and more important will work perfectly.
I thought maybe to lock the temp table, so the second thread will wait until the first thread finishes with that table. Or to use transactions. Also, I can call it synchronous but then it hurts the performance.