Detecting locked tables locked by LOCK TABLE

Question

Is there a way to detect locked tables in MySQL  I mean tables locked by the LOCK TABLE table WRITE READ command    Note that readers interested in detecting named locks acquired with GET LOCK should read Show all current locks from get lock instead

User · Answer

The following answer was written by Eric Leschinki in 2014/15 at https://stackoverflow.com/a/26743484/1709587 (now deleted):

Mini walkthrough on how to detect locked tables:

This may prevent the database from enforcing atomicity in the affected tables and rows. The locks were designed to make sure things stay consistent and this procedure will prevent that process from taking place as designed.

Create your table, insert some rows
create table penguins(spam int, ham int);
insert into penguins(spam, ham) values (3, 4);
show open tables:
show open tables like "penguins"
prints:
your_database penguins    0   0
Penguins isn't locked, lets lock it:
LOCK TABLES penguins READ;
Check if it's locked:
show open tables like "penguins"
Prints:
your_database, penguins 1, 0
Aha! It is locked! Lets unlock it:
unlock tables
Now it is unlocked:
show open tables like "penguins"
Prints:
your_database penguins    0   0
show all current locks
show open tables where in_use <> 0
It would be much more helpful if the MySQL developers put this information in a regular table (so I can do a select my_items from my_table where my_clauses), rather than this stripped down 'show table' syntax from system variables.

User · Answer

You can create your own lock with GET LOCK lockName timeOut   If you do a GET LOCK lockName  0  with a 0 time out before you lock the tables and then follow that with a RELEASE LOCK lockName  then all other threads performing a GET LOCK   will get a value of 0 which will tell them that the lock is being held by another thread   However this won t work if you don t have all threads calling GET LOCK   before locking tables  The documentation for locking tables is here  Hope that helps

User · Answer

This article describes how to get information about locked MySQL resources  mysqladmin debug might also be of some use

User · Answer

You could also get all relevant details from performance schema   SELECT OBJECT SCHEMA  OBJECT NAME  GROUP CONCAT DISTINCT EXTERNAL LOCK  FROM performance schema table handles  WHERE EXTERNAL LOCK IS NOT NULL  GROUP BY OBJECT SCHEMA  OBJECT NAME   This works similar as   show open tables WHERE In use  gt  0

User · Answer

You can use SHOW OPEN TABLES to show each table s lock status  More details on the command s doc page are here

User · Answer

The simplest way is    SHOW OPEN TABLES WHERE In use  gt  0   You get the locked tables only of the current database

User · Answer

Use SHOW OPEN TABLES  http   dev mysql com doc refman 5 1 en show-open-tables html  You can do something like this  SHOW OPEN TABLES WHERE  Table  LIKE    TABLE NAME    AND  Database  LIKE   DBNAME   AND In use  gt  0    to check any locked tables in a database

User · Answer

SHOW OPEN TABLES to show each table status and its lock   For named locks look Show all current locks from get lock

[mysql] Detecting locked tables (locked by LOCK TABLE)

Mini walkthrough on how to detect locked tables:

show all current locks

Examples related to mysql

Examples related to locking