Is there anyway to show all the locks that are active in a mysql database?
Mysql – How to show thesql locks
MySQL
Related Topic
- Linux – How to bind MySQL server to more than one IP address
- Mysql – How to speed up a MySQL restore from a dump file
- Mysql – How to rename a MySQL database
- Mysql – show thesql theisam locks
- Mysql – MySQL engine for in-memory tables that support row locks
- Mysql – thesqldump throws: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)
Best Answer
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:
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.