MySQL Deadlock Check: Query for InnoDB Deadlocks

To investigate deadlock in a table, you can follow these steps:

1. Execute the following command to check the current deadlock situation:
2. Display the InnoDB storage engine status.
3. In the results, locate the section for “InnoDB” and look for the phrase “LATEST DETECTED DEADLOCK”.
4. Detailed information about the deadlock will be listed below, including the transaction ID, locks being waited for and held, and the deadlock chain.
5. Based on the information in the deadlock chain, the involved tables can be identified. Each transaction will display the locked resources it holds and is waiting for, allowing for the relevant tables to be found.
6. You can use the following query statement to find the specific operation of a deadlock transaction based on its transaction ID.
7. Retrieve all data from the table “innodb_trx” in the information schema where the transaction ID is equal to ‘deadlock_transaction_id’.
8. Replace “deadlock_transaction_id” with the actual transaction ID.

By following the above steps, you can query the tables and transaction information related to deadlocks.