None of the answers can show all the locks that are currently held.

Do this e.g. in mysql in a terminal.

start transaction;
update someTable set name="foobar" where ID=1234;
-- but no rollback or commit - just let it sit there

Clearly the transaction above holds a lock, because the transaction is still active. But no query is going on right now and nobody is waiting for a lock anywhere (yet at least).

INFORMATION_SCHEMA.INNODB_LOCKS is empty, which makes sense given the documentation, because there is only one transaction and currently nobody waiting for any locks. Also INNODB_LOCKS is deprecated anyway.

SHOW ENGINE INNODB STATUS is useless: someTable is not mentioned at all

SHOW FULL PROCESSLIST is empty, because the culprit is not actually running a query right now.

You can use INFORMATION_SCHEMA.INNODB_TRX, performance_schema.events_statements_history and performance_schema.threads to extract the queries that any active transactions have executed in the past as outlined in my other answer, but I haven’t come across any way to see that someTable is locked in the above scenario.

The suggestions in the other answers so far won’t help at least.

Disclaimer: I don’t have innotop installed and I didn’t bother. Perhaps that could work.

Leave a Reply

Your email address will not be published. Required fields are marked *