Magento2: Deadlock Errors during product save

[deepanshu27193]

Preconditions

1. Magneto 2.0.2
2. PHP 5.6, MySQL 5.6

My Scene

1. To Save/Update large amount(45K) products in Magento from amazon on daily basis.
2. I have developed a custom script to save products in Magento
3. Product is saving properly if i save them one by one.

Steps to reproduce

1. saved 10 products at a time by using pcntl_fork(php function).
2. another scenario is NodeJS async call to Magento API's {each api call will save one product} and i limit the calls to 5 at a time. but still same error.
3. i also change indexing on "Update by Schedule". and done indexing manually by command at last.

Expected result

1. product should be save

Actual result

Some of the products are saved successfully but some failed in ratio of 70%-30% respectively.
Log Errors-

1. Asymmetric Transaction rollback.
2. Deadlock Errors.
3. Serialization failure.
   These are the general message of errors.
   one of my friend also suggest me to change innoDB lock or some serialization properties of MySQL,
   but as i thought these changes will make some other effect on magento application.

Description

i am saving custom product in magento 2.0.x. it is working fine when i am saving them one by one.
but as i have to save 45k products and to make my process fast, i saved multiple product at same time. at this time magento is throwing exception of deadlock.

Exception line :- /vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php235
Exception :-
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO catalog_product_entity (attribute_set_id, type_id, sku, created_at, updated_at) VALUES (?, ?, ?, '2016-06-29 12:29:57', '2016-06-29 12:29:57')

[veloraven]

@deepanshu27193 thank you for your report.
Could you please specify exact version?
Also, please provide description according to the template: with actual result, expected result and exact steps, as it's not clear what do you mean as 'saved multiple product at same time'.

[andimov]

According to contributor guide, tickets without response for two weeks should be closed.
Please feel free to reopen if it's needed.

[deepanshu27193]

Ver. - Magneto 2.0.2
As i have used pcntl_fork and saved 10 products at a time, a kind of multi threading scenario which gave me the above error.
Now i am using NodeJS for saving product by Rest API's call, but the error is same.
Or can you provide me a solution from which i can save multiple products so that the process can speed up.

[deepanshu27193]

any update ?

[pboisvert]

@deepanshu27193 please reopen the issue if you are still encountering the problem

[deepanshu27193]

@pboisvert i cannot able to reopen this ticket. no option here at my side.

CC - @andimov

[andimov]

@deepanshu27193
Please, format this issue according to the Issue reporting guidelines with the actual result, expected result and exact steps.

[deepanshu27193]

Preconditions

1. Magneto 2.0.2
2. PHP 5.6, MySQL 5.6

My Scene

1. To Save/Update large amount(45K) products in Magento from amazon on daily basis.
2. I have developed a custom script to save products in Magento
3. Product is saving properly if i save them one by one.

My Custom Steps to make fast

1. saved 10 products at a time by using pcntl_fork(php function).
2. another scenario is NodeJS async call to Magento API's {each api call will save one product} and i limit the calls to 5 at a time. but still same error.
3. i also change indexing on "Update by Schedule". and done indexing manually by command at last.

Expected result

1. product should be save

Actual result

Some of the products are saved successfully but some failed in ratio of 70%-30% respectively.
Log Errors-

1. Asymmetric Transaction rollback.
2. Deadlock Errors.
3. Serialization failure.
   These are the general message of errors.
   one of my friend also suggest me to change innoDB lock or some serialization properties of MySQL,
   but as i thought these changes will make some other effect on magento application.

[rbncha]

I have same issue when saving only one instance of (configurable) product process. It happens randomly. Sometimes the product is saved without any error, sometimes this error pops.

I realized this error occurs on imported db from Magento 1.9.24 (not fresh installed).

Env:
PHP 7.0.1
Mysql 5.6.23
Apache 2.4
Magento 2.1.1

[joshdifabio]

We're also seeing this issue when saving multiple configurable products in parallel. Do you have any update on this Magento?

[w130pmpo]

Any updates here? We are having same issue

[ooples]

I'm using 2.1.6 and I'm having this same issue

[maheshb4u]

We are also facing the same issue. Please let us know any update/solution on this to fix the issue.
Magento 1.9 programmatically creating multiple products at a time getting below error.
"Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction"

[mihalyYaxa]

Same error when trying to insert multiple products at the same time MCE 1.9.2 when using this command in php $mgproduct->addImageToMediaGallery($Image, $visibility, true, false);

[_attributeCode:protected] => media_gallery
[_part:protected] => backend
[message:protected] => SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO `catalog_product_entity_media_gallery_value` (`value_id`, `store_id`, `label`, `position`, `disabled`) VALUES (?, ?, ?, ?, ?)
[string:Exception:private] => 
[code:protected] => 0
[file:protected] => app/code/core/Mage/Core/Model/Config.php
[line:protected] => 1348

[careys7]

I have the same issue using the REST API on any Magento version in multiple environments.

It returns an error "Unable to save product" via the API, nothing is logged in the exception logs, and database shows the deadlocks described above.

POST requests cause a lot more deadlocks (approx 10x) than PUT.

Any level of concurrency > 1 against the API causes deadlocks.

[okorshenko]

Hi @deepanshu27193 please let us know if the issue exists in latest 2.2 release. 2.0 is end of life

[magento-engcom-team]

@deepanshu27193, we are closing this issue due to inactivity. If you'd like to update it, please reopen the issue.

[peleq-smile]

This issue is still available, when Magento team will stop closing issue for "inactivity" ?!
We have no choice to fix by ourselves some troubles because you don't give us the solutions... :-/
It really sucks.

[Pheonix7284]

We are seeing this in 2.1.10 as well.... This even happens in the built in product import function via the admin on occasion.

[mslabko]

Hi @deepanshu27193 ,
We have fixed deadlocks on product save in many scenarios. Could you please try to reproduce it with your cases?
Please note, if you use EE edition, then fixes will be available in one of next releases.

CC: @peleq-smile, @careys7, @maheshb4u , @w130pmpo

[harishps]

We are seeing this issue in Magento 2.3.3, while updating the configurable product

[LiamKarlMitchell]

Still an issue in 2.3.2, working around it by retrying the save if deadlock happened up to 5 attempts...
Suspect its due to reindexing right when saving.

[dcruz11]

Hi everyone, any solution to fix this bug?

[Jedi88]

Also looking for an update here?

[dcruz11]

Nobody got fix this issue?

[LiamKarlMitchell]

Via rest api in an importer I just looped over it 3 times if it failed to save to try again which seemed to work.
It did not happen continuously just every now and again...

[dcruz11]

Any way to temporary stop crons by code, before the custom updates are done. And Reactivate crons after that?