Directly finding and terminating specific SQL statements that cause deadlocks is an effective way to deal with deadlocks, especially in high concurrency environments. The following steps and examples show how to resolve deadlock problems by identifying, analyzing, and terminating long-running SQL statements.
1. Identify the SQL statement that causes deadlock
1. Use SHOW ENGINE INNODB STATUS
First, bySHOW ENGINE INNODB STATUSThe command obtains the current InnoDB engine status information, including deadlock detection information.
SHOW ENGINE INNODB STATUS;
Find the outputLATEST DETECTED DEADLOCKPartially, the specific transaction information that causes deadlock will be displayed, including the tables, rows, locks and transaction IDs involved.
2. Use the INFORMATION_SCHEMA table to get detailed information
Can queryINFORMATION_SCHEMATable to obtain the currently underway transaction and connection information. For example, use the following SQL statement to get transaction information in an activity:
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
3. Query the process list
useSHOW PROCESSLISTThe command can see all current connections and executions SQL statements:
SHOW PROCESSLIST;
The output will include theID、USER、HOST、DB、COMMAND、TIME、STATEandINFOfield, whereINFOThe field displays the SQL statement being executed.
2. Terminate the transaction that causes deadlock
Once the specific transaction and SQL statement are confirmed, the next step is to terminate the transaction.
1. Use the KILL command to terminate the process
according toSHOW ENGINE INNODB STATUSandSHOW PROCESSLISTGetID, can be usedKILLCommand terminates the corresponding connection. Here is an example:
-- fromSHOW PROCESSLISTGet specific processes from the resultsID KILL 12345;
3. Examples of practical operation steps
The following is a complete example of operations from identifying deadlocks to terminating deadlock transactions.
1. Obtain deadlock information
useSHOW ENGINE INNODB STATUS：
SHOW ENGINE INNODB STATUS;
Assume that the output shows:
------------------------ LATEST DETECTED DEADLOCK ------------------------ ... *** (1) TRANSACTION: TRANSACTION 123456789, ACTIVE 5 sec ... mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s) MySQL thread id 4321, OS thread handle 140735453062912, query id 5678 localhost user UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1 ... *** (2) TRANSACTION: TRANSACTION 987654321, ACTIVE 5 sec ... mysql tables in use 1, locked 1 6 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 1 MySQL thread id 8765, OS thread handle 140735453709824, query id 1234 localhost user UPDATE employees SET salary = salary * 1.2 WHERE department_id = 2 ...
2. Confirm the thread ID that caused the deadlock
AssumptionTRANSACTION 123456789is the transaction that causes deadlock, the MySQL thread ID is4321。
3. Get a detailed process list
useSHOW PROCESSLIST：
SHOW PROCESSLIST;
Assume that the result contains the following information:
+--------+------+-----------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------+---------+---------+------+-------+------------------+ | 4321 | user | localhost | mydb | Query | 5 | Locked| UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1 | | 8765 | user | localhost | mydb | Query | 5 | Locked| UPDATE employees SET salary = salary * 1.2 WHERE department_id = 2 | +--------+------+-----------+---------+---------+------+-------+------------------+
4. Terminate a specific transaction
useKILLThe command terminates the thread ID is4321The process:
KILL 4321;
After executing the above command, MySQL will terminate the thread ID as4321The corresponding transactions of the process will be rolled back, thereby unlocking the deadlock state.
4. Preventive measures
Of course, proactively termination of transactions is only an emergency measure to solve the deadlock, and more importantly, preventive measures:
- Optimize the application: Avoid long-running transactions.
- Control concurrency: Restricting a large number of interdependent transactions executed simultaneously.
- Use index reasonably: Make sure that the SELECT statement uses the appropriate index to reduce the range of the lock.
- Appropriate locking particle size: Choose the appropriate lock granularity according to the business scenario.
- Fixed resource access order: Ensure that all transactions access resources in the same order.
5. Summary
Through the above method, you can find out the specific transactions that cause deadlock and useKILLThe command terminates. This method can quickly solve the deadlock problem, but it is not a long-term solution. To fundamentally solve the deadlock problem, we still need to work hard on application design and database optimization.
This is the end of this article about mysql quick way to solve deadlocks. For more related content on mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!