detecting locked tables mysql (locked by LOCK TABLE)

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?

share |improve this question
 
   
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

5 Answers

up vote 1down 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
 

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
 

Use SHOW OPEN TABLEShttp://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
 
3 
Note: I can only get this query to work if I use `Database` instead of just Database –  rinogo Feb 11 at 23:06 

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

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

share |improve this answer
 
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

转载于:https://www.cnblogs.com/seasonzone/p/4055068.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值