detecting locked tables mysql (locked by LOCK TABLE)

Question

I would like to know whether there is an option to detect locked tables in mysql or not. I mean locked by LOCK TABLE table WRITE/READ command?

Why don't you unlock the table first ? – David V. Mar 23 '10 at 13:07 — David V., Mar 23 '10 at 13:07
@David V.: I guess because he needs to know it is locked first? Or maybe he want's to avoid 'touching' the table when/if it is locked? – lexu Mar 23 '10 at 13:11 — lexu, Mar 23 '10 at 13:11
O_O Ooooh my bad, I read that as "delete" (and deleting in the title) locked tables. – David V. Mar 23 '10 at 13:42 — David V., Mar 23 '10 at 13:42

confiq · Accepted Answer · 2014-09-15 09:32:51Z

You can't for non-named locks!

More info: http://forums.mysql.com/read.php?21,222363,223774#msg-223774

Not unless you use an advisory lock first. You might also want to look into using INSERT DELAYED instead.

Marc B · Answer 2 · 2010-04-05 18:41:01Z

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

borrel · Answer 3 · 2013-10-25 12:07:17Z

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 '%foo%' AND Database LIKE '[DBNAME]' AND In_use > 0;

to check any locked tables in a database.

Note: I can only get this query to work if I use `Database` instead of just Database – rinogo Feb 11 at 23:06 — rinogo, Feb 11 at 23:06

Giles Smith · Answer 4 · 2010-03-31 11:07:03Z

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!

this only works for named locks what about non named locks? – Marcin Apr 5 '10 at 12:32 — Marcin, Apr 5 '10 at 12:32
You can't for non named locks – Giles Smith Apr 6 '10 at 16:31 — Giles Smith, Apr 6 '10 at 16:31