How to troubleshoot MySql deadlock

Preface

MySQL deadlock is a common problem, which means that two or more transactions hold the resources required by each other and are waiting for the other party to release, resulting in the inability to continue execution of all transactions. The following is a detailed analysis of the methods, prevention methods and solutions for MySQL deadlocks:

1. Deadlock check method

1. Check the deadlock log

MySQL will record deadlock information into the error log, which can be viewed in the following ways:

Method 1: Enable deadlock log output

SHOW ENGINE INNODB STATUS;

After executing the above command, the details of the latest deadlock will be displayed, including:

Transactions involved in deadlock
Resources locked by each transaction
Specific SQL statements that trigger deadlock

Method 2: Check the MySQL Error Log

Find deadlock-related records in MySQL error log file. The log file path is usuallyoflog_errorSpecified in the configuration item.

Example:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345678, ACTIVE 5 sec
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 25, OS thread handle 139824938854144, query id 42 localhost root updating
UPDATE orders SET status='completed' WHERE id=1

*** (2) TRANSACTION:
TRANSACTION 87654321, ACTIVE 3 sec
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 30, OS thread handle 139824938854145, query id 45 localhost root updating
UPDATE inventory SET stock=stock-1 WHERE product_id=1

2. Use performance analysis tools

MySQL Performance Schema: Byevents_waits_summary_by_instanceTable analysis waiting locks.
Third-party tools: such as those provided by Percona Toolkitpt-deadlock-logger, you can collect deadlock information regularly.

2. Common causes of deadlock

1. The order of different transactions operating resources is inconsistent

If two transactions access the same table and row, but the operation order is different, it is easy to cause deadlocks.

Example:

Transaction A: Lock the table firstorders, lock the table againinventory
Transaction B: Lock the table firstinventory, lock the table againorders

2. The lock range is too large

useUPDATEorDELETEThere is no preciseWHEREConditions, resulting in the expansion of the lock range.

3. The transaction holds the lock for too long

Long-term transactions may block other transactions, increasing the possibility of deadlocks.

4. Foreign keys and cascade operations

Tables associated with foreign keys may be implicitly locked when updated or deleted, resulting in deadlocks.

3. How to prevent deadlocks

1. Unify transaction operation order

Ensuring that multiple transactions access the same resource consistently can effectively reduce the probability of deadlock.

Transaction A and transaction B both access resources in the order of: orders → inventory

2. Reasonable design of SQL

Try to avoid full table scanning and optimizeWHEREConditions to make the lock range smaller.
Add indexes to tables that may occur concurrently to reduce the granularity of the lock.

Example:

UPDATE orders SET status='completed' WHERE id=1;

foridFields create indexes to reduce locking ranges.

3. Control transaction scope and lock time

Minimize transactions to a minimum logical unit to reduce lock time.
Avoid long-term operations (such as network calls, user interaction) in transactions.

4. Reduce concurrency

In high concurrency scenarios, rationally design distributed systems to reduce high-frequency operations on a single resource.
Use sharding technology or distributed databases.

5. Use the appropriate isolation level

If the business allows, consider removing the transaction isolation level fromREPEATABLE READReduced toREAD COMMITTED, reduce the probability of deadlock.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. Solution to deadlock

1. Regular monitoring and optimization

Regularly passedSHOW ENGINE INNODB STATUSCheck the deadlock log.
Use tools (such as pt-deadlock-logger) to analyze SQL with frequent deadlocks and optimize related queries and indexes.

2. Retry mechanism

Catch deadlock exceptions in the application and add retry logic.

Example:

int retries = 3;
while (retries &gt; 0) {
    try {
        // Perform database operations        break;
    } catch (DeadlockException e) {
        retries--;
        if (retries == 0) {
            throw e;
        }
    }
}

3. Manual separation lock conflict operation

Separate the part of a transaction that may cause deadlocks into a separate transaction.

Example:

Divide inventory updates and order status updates into two transactions to execute separately.

4. Rationally use the lock mechanism

In scenarios where data needs to be locked, useSELECT ... FOR UPDATEorLOCK IN SHARE MODEClear the scope of locking.
During large batch operations, it can be processed in batches to reduce lock time.

Example:

SELECT * FROM orders WHERE id=1 FOR UPDATE;

5. Optimistic lock

The conflict detection during data update is achieved through the version number or timestamp mechanism to avoid holding pessimistic locks.

Example:

Table structure increasesversionField, check whether the version number is consistent every time it is updated.

UPDATE orders SET status='completed', version=version+1 WHERE id=1 AND version=1;

V. Actual case analysis

Scenario: Order table and inventory table deadlock

Transaction A: Update order status → Update inventory
Transaction B: Update inventory → Update order status

Solution:

Adjust the order of operations

All transactions are accessed uniformly in the order table → inventory table.

Optimize SQL

Add indexes to orders and inventory tables to reduce the number of locked rows.

Separate transactions

Separate inventory updates into separate transactions, reducing the time the transaction holds locks.

Select isolation level reasonably

Set the transaction isolation level toREAD COMMITTED, avoid the locking operation of phantom reading.

6. Summary

When troubleshooting deadlocks, it is the key to analyze the root cause through logs and tools; preventing deadlocks requires reasonable design of transactions and SQL; solving deadlocks can be done by retrying, adjusting operation sequence, separating transactions and optimizing the scope of locks. Only by choosing the appropriate means according to the specific scenario can the deadlock problem be effectively avoided and solved.

This is the article about how to detect MySql deadlocks. For more information about MySql deadlocks, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!