Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there anyway to show all the locks that are active in a mysql database?

share|improve this question
1  
You can query INNODB_LOCK_WAITS and INNODB_LOCKS tables. – user267707 Jan 29 '15 at 17:15
up vote 22 down vote accepted

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.

share|improve this answer

Using this command

SHOW PROCESSLIST

will show all process currently running, including process that has acquired lock on tables.

share|improve this answer

AFAIK there's still no native way in MYSQL, but I use innotop. It's free and has plenty of other functionality as well.

Also see this link for more information on using the innotop tool.

share|improve this answer

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.

share|improve this answer
show open tables where In_Use > 0 ;
share|improve this answer

Reference taken from this post.

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.