ASP.NET Web API C# Concurrent Requests Causing Duplicates in Database

Question

I have a WebApi Async controller method that calls another async method that first does a null check to see if a record exists, and if it doesn't add it to database. Problem is if I have say 3 requests come in at the same time all the null checks happen at once in various threads (i'm assuming) and I will get 2 duplicate entries. For example:

 public async void DoSomething()
{
    var record = {query that returns record or null}
    if (record == null)
    {
        AddNewRecordToDatabase();
    }   
}

... This seems like a very common thing and maybe I'm missing something, but how do I prevent this from happening? I have to purposely try to get it to create duplicates of course, but it is a requirement to not allow it to do so.

Thanks in advance,

Lee

Answer 1

I prevent this from happening with async calls by calling a stored procedure instead. The stored procedure then makes the check, via a "On duplicate key detection" or a similar query for MSSQL db.

That way, it's merely the order of the async calls that gets to determine which is a create, and which is not.

Answer 2

I would solve this by putting unique constraints in the data layer. Assuming your data source is sql, you can put a unique constraint across the columns you are querying by with "query that returns record or null" and it will prevent these duplicates. The problem with using a lock or a mutex, is that it doesn't scale across multiple instances of the service. You should be able to deploy many instances of your service (to different machines), have any of those instances handle requests, and still have consistent behavior. A mutex or lock isn't going to protect you from this concurrency issue in this situation.