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.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
