List all mysql tables with locks
Posted on November 23, 2021
To list all tables with locks, run the following SQL query:
show open tables where `In_use` > 1
- Database – The database containing the table.
- Table – The table name.
- 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 is 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client blocks 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.
- Name_locked – Whether the table name is locked. Name locking is used for operations such as dropping or renaming tables.