Find the locked tables in MySQL Database

Syntax for identified locked tables in MySQL

SHOW OPEN TABLES  [{FROM | IN} db_name]
 [LIKE 'pattern' | WHERE expr]

Check all locked tables in MySQL Database

show open tables;

mysql> show open tables;
+--------------------+----------------------------+--------+-------------+
| Database           | Table                      | In_use | Name_locked |
+--------------------+----------------------------+--------+-------------+
| mysql              | check_constraints          |      0 |           0 |
| sakila             | customer                   |      1 |           0 |
| mysql              | column_type_elements       |      2 |           0 |
+--------------------+----------------------------+--------+-------------+

Note:
IN_USE column indicates of the table is locked. 0 means table is open, currently no one used, 1 means table is locked, its incremented if some one else lock then its value 2.
NAME_LOCKED column indicates whether the table name is locked, is used for drop ping or renaming tables request.

Check locked tables for particular database

mysql> show open tables in world;
+----------+-----------------+--------+-------------+
| Database | Table           | In_use | Name_locked |
+----------+-----------------+--------+-------------+
| world    | countrylanguage |      0 |           0 |
+----------+-----------------+--------+-------------+
1 row in set (0.00 sec)

Note: WORLD is database in MySQL

Check the locked tables having in_use value greater than 1

show open tables where in_use > 1;

mysql> show open tables where in_use > 1;
Empty set (0.00 sec)

Check the locked tables with its name.

show open tables like 'country';

mysql> show open tables like 'country';
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| sakila   | country |      0 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)


mysql> show open tables like 'country%';
+----------+-----------------+--------+-------------+
| Database | Table           | In_use | Name_locked |
+----------+-----------------+--------+-------------+
| sakila   | country         |      0 |           0 |
| world    | countrylanguage |      0 |           0 |
+----------+-----------------+--------+-------------+
2 rows in set (0.00 sec)