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?

[mslabko]

Hi @yj4189 , if you set indexers to "Update on Save" mode, then you should not do full reindex.
I suggest you change indexing mode to "update on schedule" + setup crons.

If you still have deadlock, please provide information from mysql command SHOW ENGINE INNODB STATUS

[aliasifmse]

Same error happening for me on 2.2.2

[Top-CMS]

Same error happening for me on 2.2.3

[dimple-vasoya]

We are facing deadlock issue during catalog_product_attribute reindexing.

It gives SQLSTATE[HY000]: General error: 2006 MySQL server has gone away error as MySQL database CPU is 100% utilized.

Magento version: 2.1.7
No of SKUs: 6 Lacs
No of Attributes: 110
No of stores: 4

Please suggest what can be done?

[mslabko]

@dimple-ambab , it can be another issue, not related to a deadlock. Please check after error happening status of section LATEST DETECTED DEADLOCK with command "SHOW ENGINE INNODB STATUS"
Also check all mysql error logs for reason of error

[MrDaar]

I've experienced similar issues...

[jakoblundsted]

This is still an issue on 2.2.4, but only when trying to save products while the indexing is running.

[mcyrulik]

Same here - We receive that error if a product is saving when the index is running.

[StrategicMindInc]

We have the same error with 2.2.5 and PHP 7.1. Below is the cron log.
[Zend_Db_Statement_Exception]
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO cron_schedule (job_code, status, created_at, scheduled_at) VALUES (?, ?, '2018-07-23 12:16:02', '2018-07-23 12:19:00')

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

cron:run [--group GROUP] [--bootstrap BOOTSTRAP]

[Zend_Db_Adapter_Exception]
SQLSTATE[HY000] [2002] No such file or directory

[PDOException]
SQLSTATE[HY000] [2002] No such file or directory

cron:run [--group GROUP] [--bootstrap BOOTSTRAP]

[StrategicMindInc]

In addition, the error message displayed in the browser is:
Service Temporarily Unavailable
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later

[magento-engcom-team]

Hi @w130pmpo.

Thank you for your report and collaboration!

The issue was fixed by Magento team.

The fix will be available with the upcoming 2.3.5 release.

[magento-engcom-team]

Hi @w130pmpo.

Thank you for your report and collaboration!

The issue was fixed by Magento team. The fix was delivered into magento/magento2:2.3-develop branch(es).
Related commit(s):

• 84f6a3826b33dc7e523d457a573bbac16d292695
• 0cf9026eb69e8ec5077764da62830313046996aa
• f8dd4f0c439ee67f60f49bcb394f744395c3fc3d
• 618b4b0c84db76f993c877238c310930b1159cca
• 6c35cf31bb43381ad2d780fbbebe8725b4effcc5
• cc70177848f9fefc254e679509f0655c8d446211
• 49d4367cc1b28d44daf8ee877152bff831c00df6

The fix will be available with the upcoming 2.3.5 release.

[fritzmg]

@magento-engcom-team links to the commits do not seem to be working

[magento-engcom-team]

Hi @w130pmpo.

Thank you for your report and collaboration!

The issue was fixed by Magento team.

The fix will be available with the upcoming 2.4.1 release.

[erikhansen]

@magento-engcom-team Can you provide working links to the commits in 2.3.5 that fix this issue, as the commits you posted in your April 24th comment no longer work?

[sunnetmedia]

I face a Similar issue on Magento 2.3.2 PHP 7.3 nginx, mariadb,

`root@ip-172-31-XXXXX:/var/www/html/XXXXXX# ### sudo php bin/magento cron:run

In Mysql.php line 589:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM cron_schedule WHERE (status = 'pending') AND (job_code in
('consumers_runner')) AND (created_at < '2020-11-14 02:35:41')

In Mysql.php line 110:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM cron_schedule WHERE (status = 'pending') AND (job_code in
('consumers_runner')) AND (created_at < '2020-11-14 02:35:41')

In Mysql.php line 91:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
cron:run [--group GROUP] [--bootstrap BOOTSTRAP]

^C`

### Solution
Problem is I start my exportProcessor before manual cron:run. reboot and restart cron:run again its sucess

#sudo php bin/magento queue:consumers:start exportProcessor
#sudo reboot
&
sudo php bin/magento cron:run
Ran jobs by schedule.

fine

[magento-engcom-team]

Hi @w130pmpo. Thank you for your report.
The issue has been fixed in #28007 by @driskell in 2.4-develop branch
Related commit(s):

• aa12503923ac994873724ecaa759e1eb14b318dc
• b0b72b0f8486ca119015e84b7786dfab13d2e475
• 315bd3af8c5ed3dd711abd40db9a163b0c953855
• 96029b55f7cd00b58967637aedac67ddd1767bad
• 7df183b6c6bc3f57f0c3d29d279c3c6d631cf455
• 68d679b7bd7d371c5266d4803f03f204893f7af2
• a90161907aedae371cb307759b685cc254efe2d8
• dda5c72c9303f9476ce7041743c87079cada87f6
• 4d4fa685c0b507b510ad899f5afe94bd6c836115
• e95d2fefefb62150e566f000a4a17f58f6716af5
• 916bc93138f7a81f65c2e8b5069eeb0f35fceb43
• edd77531f8847f7b13dbaca9aefa315ba4aa5e57
• e70027f838f766b816bd1f7063514c1a9caf7a20
• 92dc76438063e68f7523d5ab036a57d7faf7bc03
• d1b22ff825052d836b56f8ce33760d42b2241980
• a76482c4614a7660ab2d324ca4c086f66906c701

The fix will be available with the upcoming 2.4.3 release.

[avra911]

... the message keeps repeating The fix will be available with the upcoming 2.X.X release. over and over again since 2.1 up to 2.4.3. Can you guys change at least the words and use the proper probably?

This is particular interesting to address my concerns about the support.

Why this issue is closed when 2.4.3 was not released?

Where are the Adobe geniuses?

[mrtuvn]

I'm not definitely geniues but this fix already confirm delivered to 2.4-develop branch via commit #28007 by @driskell.
https://github.com/magento/magento2/search?q=MC-25132&type=commits
It's will available in 2.4.3 release official in October 2021
So this ticket should closed.
If you can reproduce the problem on a Magento 2.4-develop installation, I'd strongly suggest you open a new issue with detailed steps about how to reproduce it (even though that's probably hard to get right).
In the mean time create composer patch if you need deliver it sooner for apply fixes

[DuySexy]

temporary solution is truncate table cron_schedule , issue solved !

[groomershop-mt]

In our case the problem still occur after upgrading to Magento 2.4.3, so it's probably still not solved.

[Veltix]

Magento 2.4.3-p1 still this error exists...

[florianComp]

Magento 2.4.3 i have the same error when i import product with Php

[OvalMedia]

2.4.3-p1 same error. Happens with regular import/updates of ~4000+ products.

[hostep]

If you have an import that runs from within a PHP script, try to lock the indexer cronjobs before you start the import.
If those are locked, it means the import will run and block the indexers from running at the same time. When the import is finished, unlock the locks and the indexers will be able to run again.
Also, when the indexers are running while the import starts, doing this will prevent the import from starting up until the indexers stop running or when the lock timeout amount of seconds (300 seconds in the example below) expires and if that happens, throw an exception so it doesn't run.
The idea here is to prevent indexers and imports to run at the exact same time, which can lead to deadlocks.

Here's an example of a Helper class you can use for this, just call the executeAndLockIndexers with a callback method which will run your import code:

<?php

namespace Vendor\Import\Helper;

use Vendor\Import\Exception\LockException;
use Magento\Cron\Observer\ProcessCronQueueObserver;
use Magento\Framework\Lock\LockManagerInterfaceFactory;

class Lock
{
    private $lockManagerFactory;

    public function __construct(
        LockManagerInterfaceFactory $lockManagerFactory
    ) {
        $this->lockManagerFactory = $lockManagerFactory;
    }

    private function executeAndLockIndexers(callable $callback)
    {
        // try to get a lock of the index cronjob group & specific indexer cronjobs

        $lockManager = $this->lockManagerFactory->create();
        $lockTimeout = 300; // 5 minutes
        $cronIndexGroupId = 'index';

        $lockName1 = ProcessCronQueueObserver::LOCK_PREFIX . $cronIndexGroupId;
        $lockName2 = ProcessCronQueueObserver::LOCK_PREFIX . md5($cronIndexGroupId . '_' . 'indexer_reindex_all_invalid');
        $lockName3 = ProcessCronQueueObserver::LOCK_PREFIX . md5($cronIndexGroupId . '_' . 'indexer_update_all_views');

        // when we were able to create the lock, the cronjob will not start the indexer jobs (until the import is finished)
        // when the indexers are already running by the cronjob, we will wait the amount of seconds specified in the $lockTimeout variable before giving it up
        if ($lockManager->lock($lockName1, $lockTimeout)) {
            try {
                if ($lockManager->lock($lockName2, $lockTimeout)) {
                    try {
                        if ($lockManager->lock($lockName3, $lockTimeout)) {
                            try {
                                call_user_func_array($callback, []);
                            } finally {
                                $lockManager->unlock($lockName3);
                            }
                        } else {
                            throw new LockException("The $lockName3 was locked for more than $lockTimeout seconds, so giving up on this import");
                        }
                    } finally {
                        $lockManager->unlock($lockName2);
                    }
                } else {
                    throw new LockException("The $lockName2 was locked for more than $lockTimeout seconds, so giving up on this import");
                }
            } finally {
                $lockManager->unlock($lockName1);
            }
        } else {
            throw new LockException("The $lockName1 was locked for more than $lockTimeout seconds, so giving up on this import");
        }
    }
}

It might look a bit ugly, but we have something like this setup on multiple projects (both 2.3.x and 2.4.x Magento versions) and never see a deadlock anymore during imports.

As an additional hint: setup your lock provider to use the filesystem instead of the default database, because the database one can fail when you have a high load on your server and/or if your mysql server restarts. See the documentation about how to do this.

Hope this helps 🙂

[mahmoud-abdelmalek]

Magento 2.4.4 this error still exists

[ctadlock]

Still an issue in Magento 2.4.7-p5.

In case you havent figured it out out Magento is a joke. The lack of willingness to fix a now 8 years old issue with 150+ comments is one of many you will find when trying to us it.

[coresh]

The issue: still in Magento v2.4.8