CODEWITHSUNDEEP
mysqllocking
Marcin
Marcin

Reputation: 5589

Detecting locked tables (locked by LOCK TABLE)

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.)

Upvotes: 81

Views: 320043

Answers (8)

Tomislav Nakic-Alfirevic
Tomislav Nakic-Alfirevic

Reputation: 10173

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

Upvotes: 0

confiq
confiq

Reputation: 2928

SHOW OPEN TABLES to show each table status and its lock.

For named locks see Show all current locks from get_lock

Upvotes: 37

Mark Amery
Mark Amery

Reputation: 154934

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.

Upvotes: 1

Hannes Fischer
Hannes Fischer

Reputation: 41

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 > 0

Upvotes: 3

Jose Manuel Abarca Rodr&#237;guez
Jose Manuel Abarca Rodr&#237;guez

Reputation: 10381

The simplest way is :

SHOW OPEN TABLES WHERE In_use > 0

You get the locked tables only of the current database.

Upvotes: 28

Arumand
Arumand

Reputation: 1894

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 > 0;

to check any locked tables in a database.

Upvotes: 87

Marc B
Marc B

Reputation: 360762

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

Upvotes: 47

Giles Smith
Giles Smith

Reputation: 166

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!

Upvotes: 4

Related Questions