How to check specific table is locked in Mysql?

Question

I am executing below query to find the whether my temp table is locked.

show open tables where in_use > 0 and table = 'temp'

But it throwing below error :

Syntax error near 'table = 'temp'

I have searched but unable to get the correct syntax. Can anybody help.

Answer 1

Approach1: Simply execute command show processlist; or show full processlist; command and check if any query/table is showing locked in processlist.

Note: check from super user to check all running process on server.

Approach2: execute show engine innodb status; and you can check locking here...

Approach3: In mysql 5.6 onwards you can check it in information_schema db also.

Note: If you are using any gui tools like mysqladministrator then you can check all running process in this and easy findout if any table is locked....

Update:

As you want to get this by sql query, so you can use below query-

For all locked tables-

SHOW OPEN TABLES WHERE in_use>0;

To check specific table-

SHOW OPEN TABLES WHERE in_use>0 AND `Table`='your_table_name';