0

I have a web application where users can post offers for products.

Sometimes - less than 1 in 10 cases - saving an offer leads to multiple entries in the database with identical data (usually 2 entries, but 3 or even 4 identical entries already occurred).

The offer class is a simple entity class with some properties in it. It is also connected to two further entity classes OfferImage and OfferCategory, which store the associated images and the categories in which the offer should appear.

The code to save an item to the database is the following (part of a repository class):

public class OfferRepository {
    ...
    public async Task InsertAsync(Offer offer)
    {
        Context.Offer.Add(offer);
        await Context.SaveChangesAsync();
    }
    ...
}

It is called within a service class:

public class OfferService 
{
    ...
    public async Task CheckinAsync(Offer offer)
    {
        await repository.InsertAsync(offer);
    }
    ...
}

That method of this service class is called by an mvc controller:

public async Task<IActionResult> Create(CreateOfferViewModel createOfferViewModel)
{
    if (ModelState.IsValid)
    {
        ...
        //conversion of the view model object to an Offer object
        ...
        await offerService.CheckinAsync(offer);
    }
    ...
}

As you can see, the structure is relatively simple. However, this error occurs regularly.

The lifecycle of the service classes is managed with dependency injection in startup.cs. OfferRepository and OfferService are both added scoped (services.AddScoped) The context class is added like this:

services.AddDbContext<Context>(options =>
{
    options.UseSqlServer(
        Configuration.GetConnectionString("DataConnection"),
        sqlServerOptionsAction: sqlServerOptions =>
            {
                sqlServerOptions.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(3), errorNumbersToAdd: null);
            }
    );
});

To further narrow down the problem, I ran a profiler and got a recording of the INSERT statements (in chronological order):

  1. SPID 59 - 2019-09-24 16:05:19.670 - INSERT INTO Offer ...
  2. SPID 57 - 2019-09-24 16:05:19.673 - INSERT INTO Offer ...
  3. SPID 59 - 2019-09-24 16:05:19.710 - INSERT INTO OfferImage ... / INSERT INTO OfferCategory ...
  4. SPID 57 - 2019-09-24 16:05:19.760 - INSERT INTO OfferImage ... / INSERT INTO OfferCategory ...

What makes me suspicious is that there are two different process IDs that execute the INSERTs. Since the DbContext is scoped by default, there should only be one process ID under which all statemens are executed - or am I wrong? If I am not mistaken, this would mean that two requests are executed in parallel, which in turn raises further questions about how this can happen.

As you can see, I am a little confused and hope for help from someone who can explain this or has observed and solved something similar.

(SQL Server is version 13/2016, EF Core is version 2.2)

Thank you very much!

8
  • 1
    quick question, are you sure that the endpoint is not getting called twice from the client side? Commented Sep 25, 2019 at 15:19
  • Thanks for the hint. Was also one of my first thoughts, so I tested it. But multiple clicking on the "Save" button on the website does not lead to multiple entries. And multiple requests with e.g. Fiddler can be ruled out for our users and the cases where that happens. During testing I have even been able to observe such behaviour myself, and I am very sure that I have only fired a single request. Commented Sep 25, 2019 at 15:24
  • 2
    You can also try using Postman to just send one request over to your endpoint. Another question, is there anything unique in Offer that you can do to ensure it gets added once? Then you can use that to do an AddOrUpdate instead.. Commented Sep 25, 2019 at 15:27
  • 1
    How are you managing the lifecycle of your Context (and service and repo)? Are you using dependency injection? If so, what lifecycle are you using (transient, scoped etc). If the same context is used across different requests this could be a potential cause. Commented Sep 25, 2019 at 15:31
  • Thank you for pointing that out. I will try to reproduce the problem with Postman and investigate further. I also changed the Insert() to an Update() (which can also be used since Offer has an auto-key attached to it). But unfortunately the problem was not solved. But even if it would have been solved, I'd like to know what's happening. Commented Sep 25, 2019 at 15:32

1 Answer 1

1

Every call to your API can use a different thread id. If PID really is a different process, then you have 2 different instances of your API running at once.

If you really don't want duplicates, then add a constraint to your database to prevent duplicates (product name or something). This means that SaveChanges will throw a DbUpdateException which you will need to catch and determine if it's a duplicate exception (which you can send an error back to the user via a HTTP response code, maybe 409 conflict), or something else (which is probably a 5xx error).

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the info and the tip with the constraint! I should do that anyway, I guess. However, this feels more like a workaround for a problem that definitely should not happen. At least, I want to understand what is happening.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.