MySQL 检测锁定表（由 LOCK TABLE 锁定）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2499976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Detecting locked tables (locked by LOCK TABLE)
提问by Marcin
Is there a way to detect locked tables in MySQL? I mean tables locked by the LOCK TABLE table WRITE/READcommand.
有没有办法检测 MySQL 中的锁定表？我的意思是由LOCK TABLE table WRITE/READ命令锁定的表。
(Note that readers interested in detecting namedlocks acquired with GET_LOCKshould read Show all current locks from get_lockinstead.)
（请注意，对检测获取的命名锁感兴趣的读者GET_LOCK应该改为从 get_lock读取Show all current locks。）
采纳答案by confiq
SHOW OPEN TABLESto show each table status and its lock.
SHOW OPEN TABLES显示每个表的状态和它的锁。
For named locks look Show all current locks from get_lock
对于命名锁，查看从 get_lock 显示所有当前锁
回答by Aruman
Use SHOW OPEN TABLES:
http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html
使用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.
检查数据库中的任何锁定表。
回答by Marc B
回答by Jose Manuel Abarca Rodríguez
The simplest way is :
最简单的方法是：
SHOW OPEN TABLES WHERE In_use > 0
You get the locked tables only of the current database.
您只能获得当前数据库的锁定表。
回答by Giles Smith
You can create your own lock with GET_LOCK(lockName,timeOut)
您可以使用 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.
如果GET_LOCK(lockName, 0)在锁定表之前执行0 超时，然后执行0 超时，则RELEASE_LOCK(lockName)执行 a 的所有其他线程GET_LOCK()将获得值 0，这将告诉他们该锁正由另一个线程持有。
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
但是，如果GET_LOCK()在锁定表之前没有调用所有线程，这将不起作用。锁定表的文档在这里
Hope that helps!
希望有帮助！
回答by Hannes Fischer
You could also get all relevant details from performance_schema:
您还可以从 performance_schema 中获取所有相关详细信息：
SELECT
OBJECT_SCHEMA
,OBJECT_NAME
,GROUP_CONCAT(DISTINCT EXTERNAL_LOCK)
FROM performance_schema.table_handles
WHERE EXTERNAL_LOCK IS NOT NULL
GROUP BY
OBJECT_SCHEMA
,OBJECT_NAME
This works similar as
这类似于
show open tables WHERE In_use > 0
回答by Tomislav Nakic-Alfirevic
回答by Mark Amery
The following answer was written by Eric Leschinkiin 2014/15 at https://stackoverflow.com/a/26743484/1709587(now deleted):
以下答案由Eric Leschinki于 2014/15 年在https://stackoverflow.com/a/26743484/1709587（现已删除）撰写：
Mini walkthrough on how to detect locked tables:
This may prevent the database from enforcing atomicity in the affected tables and rows. The locks were designed to make sure things stay consistent and this procedure will prevent that process from taking place as designed.
Create your table, insert some rows
create table penguins(spam int, ham int); insert into penguins(spam, ham) values (3, 4);show open tables:
show open tables like "penguins"prints:
your_database penguins 0 0Penguins isn't locked, lets lock it:
LOCK TABLES penguins READ;Check if it's locked:
show open tables like "penguins"Prints:
your_database, penguins 1, 0Aha! It is locked! Lets unlock it:
unlock tablesNow it is unlocked:
show open tables like "penguins"Prints:
your_database penguins 0 0show all current locks
show open tables where in_use <> 0It would be much more helpful if the MySQL developers put this information in a regular table (so I can do a
select my_items from my_table where my_clauses), rather than this stripped down 'show table' syntax from system variables.
关于如何检测锁定表的迷你演练：
这可能会阻止数据库在受影响的表和行中强制执行原子性。锁定旨在确保事情保持一致，并且此程序将阻止该过程按设计进行。
创建您的表格，插入一些行
create table penguins(spam int, ham int); insert into penguins(spam, ham) values (3, 4);显示打开的表：
show open tables like "penguins"印刷：
your_database penguins 0 0企鹅没有被锁定，让我们锁定它：
LOCK TABLES penguins READ;检查它是否被锁定：
show open tables like "penguins"印刷：
your_database, penguins 1, 0啊哈！它被锁定了！让我们解锁它：
unlock tables现在它被解锁了：
show open tables like "penguins"印刷：
your_database penguins 0 0显示所有当前锁
show open tables where in_use <> 0如果 MySQL 开发人员将此信息放在常规表中（这样我可以做一个
select my_items from my_table where my_clauses），而不是从系统变量中剥离出“显示表”语法，那将会更有帮助。
