Product update problems - throws Entity Framework exceptions

Questions
1

We’re experiencing problems with mass updating base products/variants in Litium. Different Entity Framework related exceptions are thrown when a large amount of products/variants is handled in one go.

Scenario: we have an import task that imports product data from an XML file and updates the corresponding base products/variants in Litium. The XML file can contain up to ~16000 products. The import task goes through each product in the file and compares the data to the corresponding base product/variant data in Litium. If anything have changed, a writable clone of the base product/variant is created, the affected fields/category connections/etc are changed and the task tries to update the base product/variant. This is when the problem starts occurring.

When the file contains a large amount of products, the calls to baseProductService.Update and variantService.Update starts throwing exceptions for random products/variants. This seems to happen randomly without any obvious pattern to it - different products are affected in different runs. We can re-run the same XML-file twice and get the problem for different products/variants.

All the different exceptions are Entity Framework related. Examples:

  • Violation of PRIMARY KEY constraint ‘PK_BaseProductCategoryLinkEntity’. Cannot insert duplicate key in object ‘Products.BaseProductCategoryLink’. The duplicate key value is (da79834f-1f4b-4377-ba1a-b9de39365c07, 27f5c7c3-f4b8-4c48-9471-9235d6144ab7).
  • Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
  • Violation of PRIMARY KEY constraint ‘PK_Products_VariantFieldData’. Cannot insert duplicate key in object ‘Products.VariantFieldData’. The duplicate key value is (7e39886b-e132-41a3-b467-e593acc96df9, _name, sv-SE, 0)

We do have proper validation in place in the code that should prevent this. If a field hasn’t changed, we don’t try to update it. If a category connection already exists, we don’t try and add another identical one.

We’re at a loss on how to solve this. Anyone else experienced the same kind of problem?

Litium version: 6.2.2

2

Feels like different threads are running and some of them are faster then other.
Do you batch update or one by one?

3

We’ve tried different approaches:

  • updating one by one through BaseProductService.Update and VariantService.Update

  • Batch update with a bunch of base product + variant pairs in each batch

  • Batch update with one base product + variant pair in each batch

None of those approaches completely solved the problem, but the error seems to happen less frequently with the last one.

What is the correct way of performing mass product updates in a thread safe way then?

4

I would suggest best way would be to do async programming so tasks wait for other task to finish but your issue could also be in code, check so u do not modify on a baseproduct/variant several times before saving (there was a similar case like that before where variant.Fields.AddOrUpdateValue was colliding with _variantService.Update(variant);). And use sync locks for the DB tables.

5

We are not creating any separate threads. The code resides in a regular method in a regular class, and is called through a button click on a regular web page. We’ve turned off all other tasks that manipulate products.

We have 1 call to MakeWritableClone and 1 call to create/update per product. And we only call AddOrUpdateField on those fields whose value is actually about to change.

How would you recommend that we go about using sync locks for all the product related tables? And wouldn’t that have a negative impact on the rest of the site?

6

It’s hard to get an understanding about what your code is doing without seeing anything, in general this is not a common problem that have been reported.

Do you have possible to share some code about the flow so we can see where/how the fetch/clone are done compared to the create/update/delete method calls to better understand what you are doing?

7

I have been working with Malin on this, and we have been able to replicate the issue in a clean Accelerator install.

When looping over a large number of variants, if we do the following, the issue occurs:

foreach(var variantId in variantIds)
{
    var variant = VariantService.Get(variantId);
    var writeableVariant = variant.MakeWriteableClone();

    // change some fields

    VariantService.Update(writeableVariant);

    // change some more fields

    VariantService.Update(writeableVariant);
}

It only fails in a handful of cases (around 20 when working with around 20k variants).

To my mind, this definitely a bug/issue with the system as it should be possible to run update against a writeable clone of a variant more than once. As I understand things, for whatever reasons, this is not possible to do in a reliable fashion.

But ok, this is a known limitation of the system. So we try to implement a workaround:

foreach(var variantId in variantIds)
{
    var variant = VariantService.Get(variantId);
    var writeableVariant = variant.MakeWriteableClone();

    // change some fields

    VariantService.Update(writeableVariant);

    variant = VariantService.Get(variantId);
    writeableVariant = variant.MakeWriteableClone();

     // change some more fields

    VariantService.Update(writeableVariant);
}

So now we get a new copy of the variant, make some changes and update it… and we still get the errors.

Litium.Data.DataException: An error occurred while updating the entries. See the inner exception for details. —> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. —> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ‘PK_Products_VariantFieldData’. Cannot insert duplicate key in object ‘Products.VariantFieldData’. The duplicate key value is (9f1633d1-90b8-489c-9c75-567ed989a5e2, _name, sv-SE, 0).

Again, this happens relatively infrequently (about 20/20k variants) but it does happen. Our current workaround is to store a list of the variants that throw this error and then run the update job again against this subset list.

I can package up my example code if needed.

8

@andymcpherson in general it should be possible to update the same variant multiple times as you are doing in the first example and if this not is the case it is as you say a bug.

If you can provide with the example code that will have this error we would be happy and it will be easier to backtrack and find up an solution of the problem.

9

@patric.forsgard How should I send the project files to you?

10

Thanks for the files, we will investigate and see what we can find.

11

We have now put out a new pre-release with a fix that should solve this issue. The pre-release have version number 6.2.3-patch-1812070747. Can you test and see if that is solving your issue in the project? I have tested with the provided script and created 200 000 base products and 600 000 variants without any issue.

Related bug https://docs.litium.com/support/bugs/bug_details?id=44453

1 Like
12

We haven’t encountered the problem in our dev environments since upgrading to the pre-release. It’s hard to say if the problem is 100% solved since it occurs at random and is hard to properly reproduce in a controlled fashion. But so far so good! Will test it out some more.

Is there an estimated release date for version 6.2.3?

1 Like