detecting locked tables mysql (locked by LOCK TABLE)

Question

Hi guys,

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

cheers,
Marcin

Answer 1

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

Answer 2

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!

Answer 3

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

Answer 4

You can't for non-named locks !!!! More info: http://forums.mysql.com/read.php?21,222363,223774#msg-223774