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

No comments

What Triggers This Error

After the developer tried to re-indexing, the exception.log file was generated including the following deadlock error in this file. Meanwhile, the server was stuck and needed restarting. Then, the site can run as usual.

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

Magento Version: 2
Mode: Production
Indexing Mode: Update on save

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

So, how to fix this error, and what is the best way to re-index manually?

Solution For Magento Deadlock Error When Reindexing All

There are 3 simple steps to handle this Magento 2 deadlock error:

  • Step 1: Open your database on local then run SQL:
DROP TABLE `catalog_category_product_index`;
DROP TABLE `catalog_category_product_index_tmp`;


CREATE TABLE `catalog_category_product_index` (
  `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Category ID',
  `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) DEFAULT NULL COMMENT 'Position',
  `is_parent` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `visibility` smallint(5) UNSIGNED NOT NULL COMMENT 'Visibility'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Index';
ALTER TABLE `catalog_category_product_index`
  ADD PRIMARY KEY (`category_id`,`product_id`,`store_id`),
  ADD KEY `CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY` (`product_id`,`store_id`,`category_id`,`visibility`),
  ADD KEY `CAT_CTGR_PRD_IDX_STORE_ID_CTGR_ID_VISIBILITY_IS_PARENT_POSITION` (`store_id`,`category_id`,`visibility`,`is_parent`,`position`);
CREATE TABLE `catalog_category_product_index_tmp` (
  `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Category ID',
  `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) NOT NULL DEFAULT '0' COMMENT 'Position',
  `is_parent` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `visibility` smallint(5) UNSIGNED NOT NULL COMMENT 'Visibility'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Indexer Temp Table';
ALTER TABLE `catalog_category_product_index_tmp`
  ADD KEY `CAT_CTGR_PRD_IDX_TMP_PRD_ID_CTGR_ID_STORE_ID` (`product_id`,`category_id`,`store_id`);
  • Step 2: Try to re-index on local
  • Step 3: If you can re-index on the local then re-index on the server

Wrapping Up

We have shown you 3 steps to fix the deadlock error that occurs when re-indexing in Magento. If you have any problems when following the instructions, please leave a comment below. And don’t forget to subscribe to Tigren’s blog to access hundreds of helpful Magento tutorials such as How To Fix Magento 2 Invalid Form Key. Please Refresh The Page Error and How To Fix One Or More Indexers Are Invalid Error In Magento 2?.

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

Related Posts

Leave a Reply