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.