Is there anyway to show all the locks that are active in a mysql database?
See Marko's link for InnoDB tables and the caveats.
For MyISAM, there isn't a dead easy "this is the offending query" solution. You should always start with a processlist. But be sure to include the full keyword so that the printed queries aren't truncated:
SHOW FULL PROCESSLIST;
This will show you a list of all current processes, their SQL query and state. Now usually if a single query is causing many others to lock then it should be easy to identify. The affected queries will have a status of
Locked and the offending query will be sitting out by itself, possibly waiting for something intensive, like a temporary table.
If it's not obvious then you will have to use your powers of SQL deduction to determine which piece of offending SQL may be the cause of your woes.
If you use InnoDB and need to check running queries I recommend
show engine innodb status;
as mentioned in Marko's link. This will give you the locking query, how many rows/tables are locked by it etc. Look under TRANSACTIONS.
The problem with using
SHOW PROCESSLIST is that you won't see the locks unless other queries are queueing up.
SHOW OPEN TABLES:
show open tables where In_Use > 0 ;
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
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.
You can use below script:
SELECT pl.id ,pl.user ,pl.state ,it.trx_id ,it.trx_mysql_thread_id ,it.trx_query AS query ,it.trx_id AS blocking_trx_id ,it.trx_mysql_thread_id AS blocking_thread ,it.trx_query AS blocking_query FROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it ON pl.id = it.trx_mysql_thread_id INNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id = ilw.requesting_trx_id AND it.trx_id = ilw.blocking_trx_id
show engine innodb status; is the way to do this with innodb, but there's an extra step:
SET GLOBAL innodb_status_output_locks=ON; first, per https://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html