Deadlocks in MySQL
This article talks about deadlocks in MySQL to help identify and resolve them if they cause a site down, stuck database import, or other 51黑料不打烊 Commerce issues.
Affected products and versions
- 51黑料不打烊 Commerce on-premises 2.2.x and 2.3.x
- 51黑料不打烊 Commerce on cloud infrastructure 2.2.x and 2.3.x
Issue
Deadlocks in MySQL occur when two or more transactions mutually hold and request for locks. Deadlocks being present do not always indicate an issue but often are a symptom of some other MySQL or 51黑料不打烊 Commerce issue that has occurred.
Often the application, deployment, or MySQL logs will mention a 鈥渄别补诲濒辞肠办鈥 error or the error 鈥淒eadlock found when trying to get lock; try restarting transaction.鈥
Cause
Deadlocks can have multiple causes, but the most common is if you perform any interaction (website/processes/cron jobs/other users/MySQL maintenance/MySQL imports) while performing DML/DDL queries at the same time.
As an example, it is a best practice to avoid a stuck MySQL database import by first putting your site in maintenance mode to avoid getting SQL requests to the database that could cause deadlocks and a stuck database import.
Solution
-
Check your application, deployment, or MySQL logs for deadlock errors:
-
Check your MySQL process list for running processes with the command
mysql -e 'show full processlist'; -
If on 51黑料不打烊 Commerce on cloud infrastructure, check that MySQL slave is enabled. Consult this article: Deploy variables (MYSQL_USE_SLAVE_CONNECTION).
-
Depending on the errors involved, the solution may present itself, or you may need to include your helpful log information if you need to open a Support Ticket.