Magento2: 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

1. 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

[ghost]

Hi @w130pmpo , thank you for your report.
We've acknowledged the issue and added to our backlog.

[gulshan-streammarket]

Is this issue fixed?

[MarcusWolschon]

Is this issue fixed?

Not in 2.2.6

[anupwankhede]

Is this issue fixed?

Not in 2.2.6

Hi @marcelo-monteiro , do you mean its fixed in 2.2.6 OR it still exists in 2.2.6 ?

[ulftietze]

Unfortunately it's not fixed in 2.2.6 :/

[fritzmg]

Same problem here too. While we also run custom import scripts, the error occurs with the cron_schedule table. And that's what I don't understand: why would this table get into a deadlock? May be if a cron:run crashes unexpectedly? What could be the reasons?

[shikhamis11]

@engcom-backlog-nazar any update on this issue ???

[Sherbieny]

Same issue still happening on 2.2.6 installation, getting logs with SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM cron_schedule WHERE (status = 'success') AND (job_code in...

all indexers are set to update on schedule, and we have products import script running every 5 minutes with a locking mechanism to prevent overlapping calls
any updates ?

[denzyk]

Are there any updates on this issue? It would be very good to have at least a working workaround until the official solution is ready, the issue is almost 2 years old :scream:

[fbkaasa]

I can confirm it also happens on 2.2.6 installs. 2 year old bug that is preventing our clients from selling products. When can we expect an update on this error?

[ghost]

@Dharmeshvaja91 why you unpinned this issue ?

[denzyk]

Hey @Dharmeshvaja91, is there any good reason for unpinning this issue? It could get more attention otherwise.

[ghost]

@amol2jcommerce Why ?

[shikhamis11]

This is the most important one this must be fixed. I have a temporary solution for this where I tried restarting transaction if this issue comes

[denzyk]

Hey @amol2jcommerce, is there any good reason for unpinning this issue? It could get more attention otherwise.

[elvinristi]

pinned back as Dharmesh replied "its by mistake"

[ghost]

@priti2jcommerce why you unpinned this issue ? please do not touch pinned issues.

[priti2jcommerce]

Hi @engcom-backlog-nazar ,

sorry we are not aware about this.

[denzyk]

@ronak2ram guys, please, stop unpinning the goddamn issue :|

[ronak2ram]

sorry we are not aware about this.

[hostep]

Guys can we please stop with this pinning thingy, it's annoying every single user on the Magento 2 repo, because the issue is put right in our face on the issues list. My guess is then that every Magento contributer can very simply remove it from the list by pressing the 'X' in the box since we have more elevated privileges (I'm going to try this in a minute myself), and most M2 contributers don't see this discussion here because they don't click through.
Either Magento should remove extended privileges for contributers so we can't unpin an issue, or we should simply not be using this pinning feature in my opinion.

Edit: yep, just simply clicking the 'X' in the issue list unpins it globally. We really shouldn't be using that.

Thanks! :)

[Ctucker9233]

I have the same issue on 2.2.7 @engcom-backlog-nazar. I think we could all use some update info on this issue.

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM mgit_cron_schedule WHERE (status = 'pending') AND (job_code in ('currency_rates_update', 'visitor_clean', 'catalog_index_refresh_price', 'catalog_product_flat_indexer_store_cleanup', 'catalog_product_outdated_price_values_cleanup', 'catalog_product_frontend_actions_flush', 'catalog_product_attribute_value_synchronize', 'backend_clean_cache', 'system_backup', 'sales_clean_quotes', 'sales_clean_orders', 'aggregate_sales_report_order_data', 'aggregate_sales_report_invoiced_data', 'aggregate_sales_report_refunded_data', 'aggregate_sales_report_bestsellers_data', 'sales_grid_order_async_insert', 'sales_grid_order_invoice_async_insert', 'sales_grid_order_shipment_async_insert', 'sales_grid_order_creditmemo_async_insert', 'sales_send_order_emails', 'sales_send_order_invoice_emails', 'sales_send_order_shipment_emails', 'sales_send_order_creditmemo_emails', 'security_clean_admin_expired_sessions', 'security_clean_password_reset_request_event_records', 'paypal_fetch_settlement_reports', 'outdated_authentication_failures_cleanup', 'expired_tokens_cleanup', 'newsletter_send_all', 'analytics_subscribe', 'analytics_update', 'analytics_collect_data', 'catalogrule_apply_all', 'magento_newrelicreporting_cron', 'catalog_product_alert', 'aggregate_sales_report_coupons_data', 'persistent_clear_expired', 'captcha_delete_old_attempts', 'captcha_delete_expired_images', 'get_amazon_capture_updates', 'get_amazon_authorization_updates', 'amazon_payments_process_queued_refunds', 'aggregate_sales_report_shipment_data', 'sitemap_generate', 'aggregate_sales_report_tax_data', 'temando_process_platform_events')) AND (created_at < '2019-01-24 21:42:02')

[fbkaasa]

Any updates on this bug?

[zipnfc]

We have experienced this today running 2.2.6 on 7.1 PHP. We truncated the cron_schedule table as suggested above and seems to have stopped the errors for now....

[Magento\Framework\DB\Adapter\DeadlockException]
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM cron_schedule WHERE (status = 'error') AND (job_code in ('indexer_reindex_all_invalid', 'indexer_update_all_views', 'indexer_clean_all_changelogs')) AND (created_at < '2019-02-17 20:25:03')

[Zend_Db_Statement_Exception]
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM cron_schedule WHERE (status = 'error') AND (job_code in ('indexer_reindex_all_invalid', 'indexer_update_all_views', 'indexer_clean_all_changelogs')) AND (created_at < '2019-02-17 20:25:03')

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

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

[hmphu]

The issue is happening for me in Magento 2.3.0 CE and RDS MySQL 5.7

[zipnfc]

We have changed our cron from * to 5 minutes as saw this suggestion somewhere and we have not had the deadlock since - might be a fluke but seems OK. Apart from this waiting an extra few minutes to process can anyone see an issue with this? Or is this more normal than * on a production system?

[hmphu]

Thanks @zipnfc I also updated my cron to */5 a few minutes ago. Im waiting and check for deadlock

[Ctucker9233]

@magento-engcom-team What's going on here?

[seansan]

This issueis still occuring in M2.3.1 @magento-engcom-team

[seansan]

To be clear issue #22438 is about CRON. Not a user action as the OP.

Many comments and references in issue #22438 are also about CRON

But i can imagine that deadlocks in general are dont-wanna-have

I do suggest fixing the CRON issue fast because it is triggered 24/7 --- also this seems a rather standard business flow where cron actions are executed. It should not lead to errors or warnings

[lracicot]

I have the same issue when trying to import products via a custom API endpoint. It occurs when there are multiple imports at the same time.

We need to import multiple datasets at the same time because there are too many products to keep in sync with other systems, and the import is very slow (12 products/seconds on average).

[magento-engcom-team]

Hi @w130pmpo.

Thank you for your report and collaboration!

The related internal Jira ticket MAGETWO-94684 was closed as non-reproducible in 2.3.x.
It means that Magento team either unable to reproduce this issue using provided _Steps to Reproduce_ from the _Description section_ on clean or the issue has been already fixed in the scope of other tasks.

But if you still run into this problem please update or provide additional information/steps/preconditions in the _Description section_ and reopen this issue.

[onlinebizsoft]

I have some sites in 2.2.6 and look like same issue :(

[fritzmg]

I have some sites in 2.2.6 and look like same issue :(

You need to update to Magento 2.3 and check if the same issue still occurs there. Then report back in this ticket.

[Ctucker9233]

@magento-engcom-team This was reported on 2.3.1 and was closed as a duplicate of this issue #22438. Please reinvestigate.

[lracicot]

Here's what I've been able to found in my case for the moment:

In Magento 2.3.1, if you have an import that takes multiple minutes (for example, you have added an API endpoint that uses the import module), and you have an indexer running at the same time because of a cron job, it is possible to reproduce the error.

The error is NOT systematic. It's a race condition. Pausing the indexer cron solves the problem temporarily.

[southerncomputer]

Yeah I just coded in a giant file-lock when i'm importing products to avoid (indexing) each other causing issues. Not elegant but it is stable.

Otherwise a slow import process will always conflict with indexing since it tries to run every minute from cron..

[lracicot]

@southerncomputer would you to share your code, if it’s legally possible? It might be a nice temporary workaround for us.

[fritzmg]

Yeah, we encounter exactly the same thing. We temporarily fixed it by calling Magento's cron not as often and only after our imports are run - which is just a workaround obviously. We intend to fix it by tying our importer to the regular Magento cron - the theory being, that Magento will execute all cron tasks sequentially when ever Magento's cron is run. This way this problem will hopefully be avoided.

[southerncomputer]

@lracicot I just look for a file_exists in the these two files, and my importer process touches the files. Ghetto but it works for us.

vendor/magento/module-indexer/Cron/UpdateMview.php: if ( file_exists('/home/scw/public_html/.indeximporting') )
vendor/magento/module-indexer/Cron/ReindexAllInvalid.php: if ( file_exists('/home/scw/public_html/.indeximporting') )

We import about 2500 new items a day during off-peak hours, then let the indexers continue afterwards to index the new attributes/sets/items into magento and elasticsuite..

Deadlocks are not fun to work around!

[asprada]

Same deadlock issues in my project. We have around 100k products and we use scheduled import for stocks and prices updates daily. The imports was cancelled caused deadlock occured by reindex process run by cron scheduler doing operations on the same tables during imports.

The deadlocks on the cron_schedule table was there as well.

The only way to deal with that issues in my case was to change the transaction isolation level (transaction_isolation) on mysql database to READ-COMMITTED. From the moment of change the deadlock issues are gone and didn't notice any harmful consequences for now.

[fritzmg]

The issue, specifically the deadlock for

DELETE FROM `cron_schedule ` WHERE (status = 'missed') AND (job_code in (...)) AND (created_at < '...')

still occurs in Magento 2.3.2.

[seansan]

This is the problem I think. 3 cron Jobs for m2 per installation manual.
Where 1 cleans? And the other updates running jobs and locks the table.

On Mon, 26 Aug 2019 at 10:19, Fritz Michael Gschwantner <
[email protected]> wrote:

The issue, specifically the deadlock for

DELETE FROM cron_schedule WHERE (status = 'missed') AND (job_code in (...)) AND (created_at < '...')

still occurs in Magento 2.3.2.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/8933?email_source=notifications&email_token=AAE7I23OCFT773OBVADJ7ULQGOG23A5CNFSM4DEEGGT2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5DU6BA#issuecomment-524766980,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAE7I27BRFTGBT4R5IDUBKDQGOG23ANCNFSM4DEEGGTQ
.

[fritzmg]

@seansan no, that can't be it, because we have only configured the cron:run command and no other cronjobs.

I think the problem is the following:

• When running cron:run, new entries are generated for cron_schedule, according to the cron configuration of each cron group.
• When running cron:run, Magento 2 starts a separate process for each cron group.
• When a cron group is finished, cron_schedule is updated.
• While everything _within_ a cron group is executed sequentially, each _cron group_ is executed in parallel.
• This means it is perfectly possible that more than 1 process wants to either delete, update or create entries in cron_schedule at the same time.

[southerncomputer]

The only way to deal with that issues in my case was to change the transaction isolation level (transaction_isolation) on mysql database to READ-COMMITTED. From the moment of change the deadlock issues are gone and didn't notice any harmful consequences for now.

This should be in the documentation. No Other sql server ships with any isolation mode as high as MySql!

[vpashovski]

@mslabko I have same issue when Product Import and Indexes (Run by Schedule) are running simultaneously.
Here is part of the result from SHOW ENGINE INNODB STATUS command:

*** (1) TRANSACTION:
TRANSACTION 13465676479, ACTIVE 9 sec starting index read
mysql tables in use 13, locked 13
LOCK WAIT 75 lock struct(s), heap size 8400, 113 row lock(s), undo log entries 392
MySQL thread id 1297378, OS thread handle 139861071734528, query id 581434663 localhost {{database}} closing tables
INSERT  INTO `catalog_product_entity` (`updated_at`,`attribute_set_id`,`entity_id`) VALUES ('2019-10-25 11:03:30', '26', '88544'), ('2019-10-25 11:03:30', '26', '76920'), ('2019-10-25 11:03:30', '26', '76712'), ('2019-10-25 11:03:30', '26', '50673'), ('2019-10-25 11:03:30', '26', '88437'), ('2019-10-25 11:03:30', '26', '100339'), ('2019-10-25 11:03:30', '26', '88438'), ('2019-10-25 11:03:30', '26', '88439'), ('2019-10-25 11:03:30', '26', '97704'), ('2019-10-25 11:03:30', '26', '50675'), ('2019-10-25 11:03:30', '26', '50676'), ('2019-10-25 11:03:30', '26', '50677'), ('2019-10-25 11:03:30', '26', '50678'), ('2019-10-25 11:03:30', '26', '50679'), ('2019-10-25 11:03:30', '26', '61685'), ('2019-10-25 11:03:30', '26', '86107'), ('2019-10-25 11:03:30', '26', '97207'), ('2019-10-25 11:03:30', '26', '65273'), ('2019-10-25 11:03:30', '26', '76713'), ('2019-10-25 11:03:30', '26', '71129'), ('2019-10-25
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 230728 page no 53 n bits 312 index PRIMARY of table `{{database}}`.`catalog_product_entity` trx id 13465676479 lock_mode X locks rec but not gap waiting
Record lock, heap no 207 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 421339515734448, ACTIVE 13 sec fetching rows
mysql tables in use 21, locked 21
3329 lock struct(s), heap size 565456, 621893 row lock(s)
MySQL thread id 1297473, OS thread handle 139861055493888, query id 581360716 localhost {{database}} Sending data
INSERT INTO `catalog_product_index_price_temp` SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `pw`.`website_id`, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS `tax_class_id`, IFNULL((IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)), 0) AS `price`, IFNULL((LEAST(IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value), IF(IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) IS NOT NULL AND (IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date) AND (IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 230728 page no 53 n bits 312 index PRIMARY of table `{{database}}`.`catalog_product_entity` trx id 421339515734448 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

[engcom-Charlie]

Hello @w130pmpo @Ctucker9233 !
@southerncomputer found a solution (watch here https://github.com/magento/magento2/issues/8933#issuecomment-530787010 ) .
The problem was in the configuration of mysql server.
Could we close this issue as resolved?

[fritzmg]

@engcom-Charlie do you agree that changing the transaction isolation level to READ-COMMITTED is the proper solution to this problem? If yes, this needs to be publicly communicated and integrated into the Magento 2 documentation as well.

[engcom-Charlie]

@fritzmg
Yes, i am agree.
Please refer to the Magento docs repo for it.

[m2-assistant[bot]]

Hi @engcom-Charlie. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[Kipperlenny]

After 2.5 years...

[Ctucker9233]

@southerncomputer @engcom-Charlie How and where does this isolation level get changed? I'm not running my own server, i'm hosted.

[Kipperlenny]

@southerncomputer @engcom-Charlie How and where does this isolation level get changed? I'm not running my own server, i'm hosted.

and this is not needed by plenty, shopware or any other shop system...

seems like a "we don't know how to fix it" workaround

[Adel-Magebinary]

guys,

try this.

Change your mysql configration usually located at

/etc/mysql/mysql-server.conf.d/mysqld.cnf
or
/etc/mysql/percona-server.conf.d/mysqld.cnf

Add to the final line with the followings:

innodb_autoinc_lock_mode = 2
transaction-isolation = READ-COMMITTED
innodb_optimize_fulltext_only = 1
innodb_lock_wait_timeout = 99
innodb_deadlock_detect = 0

Hope it helps you all.

[engcom-Charlie]

@Ctucker9233 answer here https://github.com/magento/magento2/issues/8933#issuecomment-552334354

[fritzmg]

@engcom-Charlie is this solution still the Magento development's team official stance on this issue? I suspect that some hosters will not allow this to be changed.

[engcom-Charlie]

Hello @fritzmg
We are still waiting for response from Magento Devdocs team https://github.com/magento/devdocs/issues/5956#issue-520034543 .

[Ctucker9233]

@engcom-Charlie I can't make that change because I am hosted. I suspect a majority of magento users are hosted. You can't seriously expect that all users of magento will have access to their own server!

[southerncomputer]

You could set transaction level per transaction!
This is done in the symfony library for database automatically. I'm sure it could be ported to zend DB framework.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

see how they do it here:
vendor/symfony/http-foundation/Session/Storage/Handler/PdoSessionHandler.php: $this->pdo->exec('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');

something like this perhaps:

    public function beginTransaction()
    {
        if ($this->_isRolledBack) {
            // phpcs:ignore Magento2.Exceptions.DirectThrow.FoundDirectThrow
            throw new \Exception(AdapterInterface::ERROR_ROLLBACK_INCOMPLETE_MESSAGE);
        }
        if ($this->_transactionLevel === 0) {
            $this->logger->startTimer();
                 $this->_connection->query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

            parent::beginTransaction();
            $this->logger->logStats(LoggerInterface::TYPE_TRANSACTION, 'BEGIN');
        }
        ++$this->_transactionLevel;
        return $this;
    }

[vpashovski]

Also if you want you can use plugin for Magento\Framework\DB\Adapter\AdapterInterface::beforeTransaction(). Something like this:

<type name="Magento\Framework\DB\Adapter\AdapterInterface">
   <plugin name="plugin_name" type="{Vendor}\{Module}\Plugin\Magento\Framework\DB\Adapter\AdapterInterfacePlugin" />
</type>

class AdapterInterfacePlugin
{
    public function beforeBeginTransaction(\Magento\Framework\DB\Adapter\AdapterInterface $subject)
    {
        $subject->getConnection()->query('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED');

        return null;
    }
}

Note that I'm using SESSION otherwise deadlocks still occurs :)

[engcom-Charlie]

@southerncomputer feel free to create a PR for it.

[southerncomputer]

@engcom-Charlie Maybe someone like @vpashovski would be better to make a pull since he could make it conditional to the database configuration to enable or disable the functionality!

[engcom-Charlie]

@southerncomputer yes, sure.
@vpashovski could you do it?

[vpashovski]

@engcom-Charlie Maybe someone like @vpashovski would be better to make a pull since he could make it conditional to the database configuration to enable or disable the functionality!

@southerncomputer Can you please explain in more details what you mean to make it conditional to the database configuration?

Also @mslabko opened internal issue and they are working on it. So we can wait for their solution.

[southerncomputer]

@vpashovski It should be configurable as other database options are, to prevent an unacceptable database isolation mode from being hard coded in, say if it is not necessary (database is already set to read-committed or code requires alternate session transaction isolation level) ..

[fritzmg]

@shrielenee has rejected the request to change the documentation. And I agree that this needs to be fixed within Magento itself.

[sdzhepa]

Based on multiple feedbacks and complaints I think we have to Confirm this issue and re-considering/investigate one more time.

IMO:
It would be very helpful to have more detailed and clear steps to reproduce on the latest Magento codebase.
Detailed instruction on how to reproduce will prevent situation that issue will be closed again as "cannot reproduce"

[magento-engcom-team]

@sdzhepa Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• [ ] Add "Reproduced on " label(s) to this ticket based on verification result

Once all required information is added, please add label "Issue: Confirmed" again.
Thanks!

[magento-engcom-team]

:white_check_mark: Confirmed by @sdzhepa
Thank you for verifying the issue. Based on the provided information internal tickets MC-22212 were created

Issue Available: @sdzhepa, _You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself._

[matt-gribben]

Can confirm this is still an issue, in my case I'm running a Magento 2.3.2 with multiple incoming API connections building quotes and creating orders and the deadlock issue happens quite frequently. This is something that needs resolving especially considering the move towards headless systems and PWA

[smadasam]

So, what is a workaround once you are in this state? My reindexing cron job doesn't run anymore. Given it is is always locked, it seems like the lock is leaked/stale. Can I just manually delete the lock somehow?

[carolineboivin]

We've been experiencing several issues on a project with indexers getting repeatedly getting stuck. (M2.3.3)
After thorough investigations we were able to isolate a pattern that might cause the issue.
When an index gets stuck, it seems to be when the server's system cron calling "php/sessionclean" runs while there is an index running (on schedule).

What seems to happen is that the sessionclean deletes php sessions and when this happens, the php session used by the indexation cron job gets deleted, causing the next "SHOW LOCK()" call from the same indexation process, specifying a sessionid that does not exist anymore, and causing the slow query that runs until timeout.

Just found out about that one, thus the fix haven't been tested yet.
Would be great if some of you could check if that explanation could be valid in your contexts too.

The funny thing is that knowing what I was looking for, I was finally able to find the solution... _in a twitter post_ (believe it or not) : https://twitter.com/willemwigman/status/1177162364308860928 (image taken from the post)

Will be trying this one out and update this when it will be tested.

[kamzata]

Any news on this? Here's my magento.cron.log file:

Magento CE 2.3.4

In Mysql.php line 585:

  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 ('indexer_reindex_all_invalid
  ', 'indexer_update_all_views', 'indexer_clean_all_changelogs')) AND (create
  d_at < '2020-04-20 03:10:08')


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 ('indexer_reindex_all_invalid
  ', 'indexer_update_all_views', 'indexer_clean_all_changelogs')) AND (create
  d_at < '2020-04-20 03:10:08')


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]

[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 magento/magento2#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.