detecting locked tables mysql (locked by LOCK TABLE)

標籤：des   blog   http   io   os   ar   for   sp   strong   

detecting locked tables mysql (locked by LOCK TABLE)

up vote15down votefavorite7	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? mysql locking share|improve this question edited Aug 26 ‘12 at 1:36pero 8,31832755 asked Mar 23 ‘10 at 12:55Marcin 2,18452553
	Why don‘t you unlock the table first ? –  David V. Mar 23 ‘10 at 13:07 6   @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 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 add a comment

5 Answersactiveoldestvotes

up vote1down voteaccepted	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. share|improve this answer edited Sep 15 at 9:32   answered Apr 5 ‘10 at 18:48confiq 905719
	add a comment

Did you find this question interesting? Try our newsletter

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

up vote29down vote	You can use SHOW OPEN TABLES to show each table‘s lock status. More details on the command‘s doc page are here. share|improve this answer answered Apr 5 ‘10 at 18:41Marc B 203k16128227
	add a comment

up vote14down vote	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. share|improve this answer edited Oct 25 ‘13 at 12:07borrel 578314 answered Aug 25 ‘12 at 7:10Aruman 39638
	3   Note: I can only get this query to work if I use `Database` instead of just Database –  rinogo Feb 11 at 23:06  add a comment

up vote3down vote

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! share|improve this answer answered Mar 31 ‘10 at 11:07Giles Smith 1087

this only works for named locks what about non named locks? –  Marcin Apr 5 ‘10 at 12:32 You can‘t for non named locks –  Giles Smith Apr 6 ‘10 at 16:31 add a comment

up vote0down vote	This article describes how to get information about locked MySQL resources. mysqladmin debugmight also be of some use. share|improve this answer edited Mar 23 ‘10 at 13:12   answered Mar 23 ‘10 at 13:06Tomislav Nakic-Alfirevic 5,76211735
	1   its fine but what about MyISAM being locked ? cheers –  Marcin Mar 23 ‘10 at 13:56 Sorry, can‘t help you there. –  Tomislav Nakic-Alfirevic Mar 23 ‘10 at 15:02

http://stackoverflow.com/questions/2499976/detecting-locked-tables-mysql-locked-by-lock-table

detecting locked tables mysql (locked by LOCK TABLE)