If you have a Magento 2.x installation, you may from time-to-time encounter a MySQL “Deadlock.” A Deadlock occurs when 2 or more MySQL connections run transactions that prevent one-another from completing. When this happens, MySQL picks one or more connections and kills the query with a “Deadlock encountered” error. If you are frequently encountering Deadlocks in your application under low-traffic, you may want to revise your application design. That said, Deadlocks are just a fact of life, and even a well designed application will encounter them on occasion.
This module provides an update to the core Magento 2 Pdo/Mysql driver which will “retry” Deadlocked transactions using the exponential back-off strategy (each retry is “delayed” by an increasing number of seconds). This module draws inspiration from the Magento 1 module by Aoe: https://github.com/AOEpeople/Aoe_DbRetry
Our module can be found at the associated github: https://github.com/cadencelabs/magento2-deadlockRetry
You’ll want to install it with:
# cd to your magento root first mkdir -p app/code/Cadence/DeadlockRetry && git clone https://github.com/cadencelabs/magento2-deadlockRetry app/code/Cadence/DeadlockRetry
In Magento 2, the Pdo/Mysql class is instantiated directly (not through the ObjectManager class) this means that in order to override that class, we have to make an update to the repo’s composer.json file to instruct it to load our version of the Pdo/Mysql class. IF YOU ARE USING Magento 2.1 MAKE SURE YOU LOAD THE 2.1 BRANCH OF OUR REPO
Make this edit to your composer.json [IMPORTANT – Our module won’t work without this]
"files": [ "app/etc/NonComposerComponentRegistration.php", + "app/code/Cadence/DeadlockRetry/Framework/DB/Adapter/Pdo/Mysql.php" ], "exclude-from-classmap": ["**/dev/**", "**/update/**", "**/Test/**"] },
Then run the below command to update your composer’s autoloader:
# cd to your Magento 2 root composer dump-autoload
Once the autoloader is updated, you’ll want to upgrade your Magento 2 database:
# cd to your Magento 2 root php bin/magento setup:upgrade
The module is now installed!
Test the Module
We’ve included a Magento 2 command line script you can use to verify Deadlock transactions will be retried. This script uses the suggestion in this blog article to simulate a Deadlock: https://www.xaprb.com/blog/2006/08/08/how-to-deliberately-cause-a-deadlock-in-mysql/
Run the below command:
bin/magento cadence:deadlockRetry:simulate Starting a transaction to force a deadlock... # You can now check var/log/system.log (Magento 2.2.x) or var/log/debug.log (Magento 2.1.x)
Once you start the simulation script, you’ll see Deadlocks get retried, and logged to your var/log/system.log (Magento 2.2.x) or var/log/debug.log (Magento 2.1.x). Note that this kind of Deadlock retry depends on the setting:
By default, this variable is set to 50 seconds – so you’ll have to wait 50 seconds to see a Deadlock recorded in your system.log.
Use at your own risk!!
This module is experimental, provided as-is, and should be used at your own risk. Depending on the cause of Deadlocks in your application, retrying them could actually exacerbate resource issues – so keep a close eye on things after you install this!
After the extension is installed, you should see Deadlocks getting retried in your production environment (and as mentioned, a log of those attempts will be made).
Need help with Magento 2 Bugs?
We’ve had a lot of experience fixing problems with Magento 2. If you need help, head over to the Cadence Labs contact page, or email us at firstname.lastname@example.org. We offer affordable rates for our Magento development services.