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