SELECT * FROM INFORMATIONSCHEMA.INNODBLOCK_WAITS w
INNER JOIN INFORMATIONSCHEMA.INNODBLOCKS l1 ON w.REQUESTINGTRXID = l1.LOCKTRXID
INNER JOIN INFORMATIONSCHEMA.INNODBLOCKS l2 ON w.BLOCKINGTRXID = l2.LOCKTRXID;
SHOW ENGINE INNODB STATUS\G
Look for the `TRANSACTIONS` section.
SELECT trxid, trxstate, trxstarted, trxrequestedlockid, trxwaitstarted, trxweight, trxmysqlthreadid, trx_query
FROM informationschema.innodbtrx
ORDER BY trx_started;
KILL THREAD_ID;
Replace `THREADID` with the `trxmysqlthreadid` from the query in step 3.
SET GLOBAL slowquerylog = 'ON';
SET GLOBAL slowquerylog_file = '/path/to/your/log/file.log';
SET GLOBAL longquerytime = 2;
Adjust the `longquerytime` value as needed.
EXPLAIN SELECT * FROM yourtable WHERE yourconditions;
Replace the `SELECT` statement with the query causing lock contention.
These are immediate actions you can take to identify and potentially resolve lock contention issues in your MySQL database.
Let Dr. Droid create custom investigation plans for your infrastructure.
Book Demo
