Get locked tables in mysql query.

Question

Hi,

Is there any way via mysql sql query to get the locked tables, i have an app running and there are bunch of tables getting locked in the app accessing processing (c# threading app) and mysql DB.

I need to see the locked tables based on sql query and analyze the code for that particular section.

Thanks.

Answer 1

Use:

SHOW OPEN TABLES

An check whether the column In_use is greater than 0. In that case, the table is locked.

Examples

• List of locked tables:

  show open tables WHERE In_use > 0

• Check whether the table tb_employees is locked or not:

  show open tables WHERE Table LIKE 'tb_employees' AND In_use > 0

From the official documentation:

In_use

The number of table locks or lock requests there are for the table. For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block waiting for the lock, but the lock request causes In_use to be 2. If the count is zero, the table is open but not currently being used. In_use is also increased by the HANDLER ... OPEN statement and decreased by HANDLER ... CLOSE.