How to Fix Magento Deadlock Issues?

The deadlock issue is one of the biggest and most difficult issues to fix on Magento 2, which every Magento developer comes across sooner or later. Once you start to search for how to fix deadlock issues in Magento, you will have a hard time finding an ultimate guide.

So, my guess is —  it is probably not the first article you come across. But hopefully, you'll find the answer here.

In this article, you'll learn everything you need to know to understand and fix deadlock issues. Or at least significantly reduce them.

Why Do Magento Indexes Get Invalidated?

When you face the Magento deadlock issue, the first thing is to check Magento indexes and learn why they get invalidated. For that go to System > Tools > Index Management, make sure that you have set up index mode correctly and that your crons are running.

Magento index modes

Magento has two index modes:

• Update on Save — reindex is done during product save (it is not recommended to enable this mode on production since it can significantly increase product saving time).
• Update by Schedule  — reindex is done by cron jobs indexer_reindex_all_invalid and indexer_update_all_views.

Magento Index Management page with invalidated indexes

You can also see the IDLE(n) IN BACKLOG in the indexer management section which means that indexes for (n) products are invalid, and reindexing is required. However, after running reindex all indexes should be valid.

Magento Index Management page with valid indexes

Test Magento indexers

Let’s do a little testing:

Change index modes to Update by Schedule and run the reindex using the following command:

bin/magento indexer:reindex

Then open Index Management. All indexes should have the Update by Schedule mode and IDLE - 0, just as in the picture above.

After that go to any product and save it. No need to make any attribute value changes. Open Index Management in a new tab and compare it to the previous one. You may notice that some indexes changed from IDLE - 0 to IDLE - 1 (if you edited 5 products you will see IDLE - 5 and so on).

Monitor Magento indexers

Now that you know how to invalidate the index, we should move on to a real-life example.

You need to monitor the Index Management page to make sure that all indexes are valid (green). Magento executes reindex jobs every minute and if all indexes are valid it will take no more than a few seconds to finish.

As you may know, Magento keeps product attributes data in catalog_product_entyty_* tables. Those tables have MySQL triggers that make indexes invalid on data change. So if your indexes are invalid (red) every time you check them (e.g each 2 hours) that's because reindex process uses many resources and tables that get LOCKED during the process.

As you might have guessed, reindexing won't be so fast in this case. And that's when the Magento deadlocks issues, issues with product saving, start to appear.

Thus, the reason why your indexes get invalidated is — changing of the attribute values.

When Does the Magento Deadlock Issue Happen?

Assume you have a site with 3 store views and about 20k products, 200 categories. Full reindex will take about 40 minutes.

You have an extension that dynamically updates is_new product attribute by the condition — if the product created_at is less than 30 days ago then mark a product as new.

An extension has a cron job, that updates the attribute every hour. Data is updated in the following way:

1. Change is_new attribute value to 0 for all products
2. Find products that are created less than 30 days ago, and set the attribute value to 1

If a script runs every hour, then very few attribute values need to be changed. So, the number of new products will, most likely, stay the same for a few hours or even days.

The script will manipulate the data (change all attribute values to 0 then some to 1) and the final result will stay the same. But data was processed and indexes invalidated — need to avoid such situation.

So, after the script execution, we got 20K invalid indexes.

As we've mentioned above, a full reindex takes 40 minutes. If your index mode is Update by Schedule, Magento does a particular reindex (only for indexes that were invalidated) when cron jobs indexer_reindex_all_invalid and indexer_update_all_views are runnings. Particular reindex is faster than full reindex but also takes some time and creates an unnecessary overload on DB.

It is during such reindex process that the Magento deadlock issue appears.

How to Find What Causes Index Invalidation?

Since you already know that your indexes get invalidated because of the attribute values changing, you also need to know which values exactly. To figure that out try one of the following methods.

1. Install the Magento 2 Cron Schedule Extension that can help you monitor your cron jobs.

Example of an extension for cron management

2. Run full reindex bin/magento ind:reind to make all indexes valid (green).

3. Increase History lifetime in Stores > Configuration > Advanced > System > Cron (Scheduled Tasks) by 1440 min = 24 hours. It will allow you to find out what's going on on the website within 24 hours.

Cron (Scheduled Tasks) configuration

4) Keep an eye on the indexer_update_all_views job and indexes in the Cron Scheule Log section.

Cron schedule log in the admin panel

In our case the indexer_update_all_views job is executed every 5 minutes and, in most cases, the execution time is 1 sec — all indexes are valid and don't overload the database.

You may notice that after the red block, the execution of the indexer_update_all_views takes 6 minutes. It means that some indexes were invalidated by some actions. Usually, you can find a job that invalidates indexes in intervals between jobs executions — red block. 

Now you need to figure out what job triggers index invalidation. To do that you need to execute the cron job you suspect of causing the index invalidation. Don’t forget to run reindex before it.

So you just run the job and check indexes. If indexes get invalidated, you need to check if the extension works properly and if any data changes are required. You can also reschedule the cron job to do some actions at night time when nobody works with the admin panel and there is less traffic than during the day.

How to Fix the Deadlock Issue in Magento 2?

If Magento dead locks issue happens during product saving or some other action, you can find out the reason by following these steps:

1. Open phpMyAdmin and select your DB.

2. Open the Status tab and select the Process tab. Here you can see queries running currently and the time it take to execute them. 

Showing running SQL queries in phpMyAdmin

If you see some query that is executed for more than 1 minute, you need to pay attention to it. Perhaps the query is locked at the tables and that's why Magento can’t save a product.

To check it out, you can wait until the query is finished or kill the query and try to repeat an action you tried to perform (product save or some other action that failed due to the deadlock issue).

Check the Database Configuration

The last but not least thing to check when you experience deadlock issue in Magento is database configuration.

Magento recommends using the following DB configurations for better performance. So if you're experiencing the Magento deadlock issues, check it out.

You might also face the following issue: 

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was...

You can try to change the lock node within your app/etc/env.php file and change the prefix within the inner config node.

<?php
return [
    'lock' => [
        'provider' => 'db',
        'config' => [
            'prefix' => 'new-prefix-here'
         ]
    ]
];

Then just reset the indexers and try to reindex Magento again.

Fixing the Magento deadlock issues is not as hard as you might think as long as you know where to start. I hope this guide will help you to get a better understanding of the deadlock issue and the reasons why it appears.