how to fix Deadlock error occurred while reindexing all ?

[yagniksphp]

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?

[heaven7]

Hi,

Deadlocks error may occur for several reasons.

1. database table crash (try to copy all database to your local development enviropment and run reindex). If you can reindex on local then are problems with server. Try to repair and optimize tables.

2. not optimal database settings. (for example: try to increase innodb_buffer_pool_size)

I suggest reindexing with cron

[yagniksphp]

Thanks for reply,

I will try your suggestion for fix deadlock error occurred during reindexing manually in production mode.

And let you know if any issue or not work.

And as per your suggestion use cron for reindexing, if run reindexing in the background using cron that occur any issue in frontend create a new customer and place an order ?

and if running reindexing by cron,

is it cause any issue to insert new product and update product in backend or via import?

is it also displayed updated data on products, customer and orders on the frontend? 

[tigren_solution]

Hi, 

Here is the detailed solution to your problem. 

How To Fix Deadlock Error When Reindexing All In Magento 2?

If you have any problems when following the instruction, please let me know!

[cadencelabs]

Depending on the cause of the Deadlock, using the retry module could resolve the issue:

https://www.cadence-labs.com/2018/09/magento-2-deadlock-retry-module/

[vinayshah5]

Do this also relate with indexing issue when importing data?