How to View MySQL Query Locking Table

Most MySQL queries run properly without causing any issues in the server. But sometimes you may end up running a query that locks one or more MySQL tables and takes a long time to complete execution. When a table is locked it cannot be updated and this cause problems in other parts of your website or application. In such cases, you need to quickly identify the MySQL query locking table and probably terminate it. In this article, we will learn how to view MySQL query locking table.

How to View MySQL Query Locking Table

If your MySQL storage engine is MyISAM then you can use the following command to view the list of queries currently running in your database. Login to MySQL console and run the following query.

mysql> SHOW FULL PROCESSLIST;

The locking queries will be displayed with a status of LOCKED in the above query’s output.

If your MySQL server’s storage engine in InnoDB, you can use the following query to identify locking queries. Login to MySQL console and run the following query.

mysql> show engine innodb status;

Alternatively, you can also use free tool called Innotop which provides a simpler interface to find locking queries.

If none of the above approaches work for you, then try using the following query to list all the tables that are in use. This is useful if you have many tables and hundreds of database connections.

mysql> show open tables where In_Use > 0 ;

In this article, we have learnt several different ways to view MySQL query locking table. You can use any of them as per your requirement.

Also read:

How to Group By Date on Datetime Column in MySQL
How to Select By String Length in MySQL
How to Remove All Tables in MySQL
How to Skip Tables in MySQLdump
How to Reset MySQL Query Cache