1213 Deadlock found when trying to get lock

[w130pmpo]

Preconditions

1. Magento 2.1.3
2. Php 7.0.13-1 and Apache2

Steps to reproduce

1. Importing Product (Saving to Magento) from 3rd party API from CLI Script
2. Run above process in parallel since too many products exist
3. Index - Update by Schedule. (Meaning no index works when importing product)

Expected result

1. Product Should be successfully saved to the system

Actual result

1. Following problem happen oftenly,

• PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock

2. If I try to edit product related value on Admin page while importing product, that error happen too.

[southerncomputer]

The indexer runs on a schedule by cron! you need to disable that or they need a lock to prevent concurrent access!

If you don't mind modifying the database code you can modify aoe's magenta 1 plugin to function the same. I have and it does not solve the longer issues, but can be a lifesaver due to the little short issues!

You should really batch all of your product imports when the store is in maintenance mode and then reindex before taking it out of maintenance mode at midnight!

At least until magento can develop a decent locking system to work around the cron indexer!
https://github.com/AOEpeople/Aoe_DbRetry/blob/master/app/code/community/Aoe/DbRetry/Resource/Db/Pdo/Mysql/Adapter.php

[w130pmpo]

Thanks for reply.

• I forgot to mention that we for now even disabled cron job too. So, no index work when importing product.

• Let me tell you my scenario here
  -- There are lots of products on 3rd party system to import.
  -- Average 30k products exist on that system. On that system, 3k of products deleted and 3k of new products created every week.
  -- To manage a price and stock of existing product and newly added product, we need to run this process at least 1-2 per week.
  -- Importing Products to Magento system is slow. Especially creating new product is much slower than updating.
  -- Because of reason above (many products and slow), we have to run this process most time of week. So we cannot even turn our website in maintenance mode. That's why we need to run this script in parallel to get it done faster.

Thank you

[southerncomputer]

Sounds like you need to use a dedicated import script. I can import 1M products in about 6 hours time using dedicated script that does direct access to database, but as you have figured out- you cannot manually muck with the categories/products during the same time! IMO the best route would be to offload the product attributes/categories to a nosql based solution so you can side-load the products. I've only seen this done with magento 1 using smile-sa (GitHub) mongodb driver!

But if you use smile-sa's elastic suite - the indexing goes much faster! check it out it is free!

While reindexing the catalog stays online since it reindexes to a new catalog and does a switcheroo when done!

[w130pmpo]

Hello,

Could you please specify "nosql based solution" ?

Thank you

[southerncomputer]

store all product attributes in mongodb which is far better than EAV-style datastore for tons of products. Better yet - imo - would be to store the attributes in elasticsearch itself!

[w130pmpo]

Thanks for reply. I have another question regarding indexing.

• Currently Indexer has been set as "UPDATE BY SCHEDULE"

• Cron job currently disabled

• Create Product using php script on CLI and through Product Model (Product->save())

=> expected result, newly added product should not be in catalog_product_flat table
=> actual result, newly added product is in catalog_product_flat_table (look system reindex after product save)

[southerncomputer]

I'm not sure, are you sure that cron.php is not being called by crond?!

I assumed reindexing was to move from database (flat, or eav) to solr/elasticsearch models on schedule. I would have thought the flat table was directly written to.

I've been using elasticsearch since v1 - so i'm not sure how the flat tables get indexed if not real time?! mysql doesn't need any work to add text based indexing!

[ooples]

I'm having this same issue on magento 2.1.6 and I'm using elasticsearch but my issue seems to be when magento is running a reindex

[Nix-id]

Confirm this bug.
v.2.1.8

[Kipperlenny]

and with v.2.21

[farhadfrz]

Confirm
Magento 2.1.10
In our case, when reindex is running, no order can be placed (lock on sales_order table)

[yj4189]

Magento Version: 2.1.9|
Mode: Production
Indexing Mode: Update on save

Total Products: 18k
Total Customers: 54k
Total Orders: 18k

my client site is marketplace site so, on this site, there are lots of products, customers, and orders adding or updating.

But when I tried to reindexing all than after some time exception.log file generated including the following error in this file and server was stuck and then need to restart the server and then after the site is working as normal.

"PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction"

so please suggest me how can I fix it and what is the best way to reindexing manually or using cron?