How to deal with async calls to a stored procedure that updates data

Question

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):

1. First thread enters the stored procedure.
2. Second thread enters the stored procedure.
3. First thread selects a table and inserts it to a temporary table.
4. Second thread selects a table and inserts it to a temporary table.
5. 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].
6. 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.

My question:

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.

Thanks.

Answer 1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a queue system to process the asynchronous calls so that you know for a fact the data is already updated before calling the second or to use some other mutex/singleton to deal with it in the database.

While I personally don't like doing it this way, you can use sp_lock: http://msdn.microsoft.com/en-us/library/ms187749.aspx

There are a few drawbacks, namely you're locking an entire object and forcefully making something synchronous.

Other ways involve query hints such as holdlock with xlock. These will force the other calls to wait but you may introduce deadlocks into your environment. At that point, forcing serialization may be what you want as an isolation level.

Since the calls rely on the previous calls being completed, it would make more sense to implement a background queue thread or class to deal with all of the calls in order as using sp_lock will cause concurrency issues later. You could also try to redo the design so that the previous call doesn't have to complete to get the correct information. Since we are scant on details I do not know if that's possible or not.