MySQL 从 get_lock 显示所有当前锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11034504/
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
Show all current locks from get_lock
提问by braunbaer
Is there any way to select / show all current locks that have been taken out using the GET_LOCKfunction?
有什么方法可以选择/显示使用该GET_LOCK功能取出的所有当前锁？
Note that GET_LOCKlocks are different from table locks, like those acquired with LOCK TABLES- readers who want to know how to see thoselocks should read Detecting locked tables (locked by LOCK TABLE)
请注意，GET_LOCK锁不同于表锁，就像那些获得的锁一样LOCK TABLES- 想要知道如何查看这些锁的读者应该阅读检测锁定的表（由 LOCK TABLE 锁定）
回答by Marc Alff
Starting with MySQL 5.7, the performance schema exposes all metadata locks, including locks related to the GET_LOCK()function.
从 MySQL 5.7 开始，性能模式公开所有元数据锁，包括与GET_LOCK()函数相关的锁。
See http://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
见http://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
回答by Mark Amery
From MySQL 5.7 onwards, this is possible, but requires first enabling the mdlinstrument in the performance_schema.setup_instrumentstable. You can do this temporarily (until the server is next restarted) by running:
从 MySQL 5.7 开始，这是可能的，但需要首先启用表中的mdl仪器performance_schema.setup_instruments。您可以通过运行临时执行此操作（直到服务器下次重新启动）：
UPDATE performance_schema.setup_instruments
SET enabled = 'YES'
WHERE name = 'wait/lock/metadata/sql/mdl';
Or permanently, by adding the following incantation to the [mysqld]section of your my.cnffile (or whatever config files MySQL reads from on your installation):
或者永久地，通过将以下咒语添加到[mysqld]您的my.cnf文件部分（或 MySQL 在您的安装中读取的任何配置文件）：
[mysqld]
performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'
(Naturally, MySQL will need to be restarted to make the config change take effect if you take the latter approach.)
（当然，如果您采用后一种方法，则需要重新启动 MySQL 以使配置更改生效。）
Locks you take out afterthe mdlinstrument has been enabled can be seen by running a SELECTagainst the performance_schema.metadata_lockstable. As noted in the docs, GET_LOCKlocks have an OBJECT_TYPEof 'USER LEVEL LOCK', so we can filter our query down to them with a WHEREclause:
通过对桌子运行 a 可以看到您在仪器启用后取出的锁。如文档中所述，锁有一个of ，因此我们可以使用子句将查询过滤到它们：mdlSELECTperformance_schema.metadata_locksGET_LOCKOBJECT_TYPE'USER LEVEL LOCK'WHERE
mysql> SELECT GET_LOCK('foobarbaz', -1);
+---------------------------+
| GET_LOCK('foobarbaz', -1) |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM performance_schema.metadata_locks
-> WHERE OBJECT_TYPE='USER LEVEL LOCK'
-> \G
*************************** 1. row ***************************
OBJECT_TYPE: USER LEVEL LOCK
OBJECT_SCHEMA: NULL
OBJECT_NAME: foobarbaz
OBJECT_INSTANCE_BEGIN: 139872119610944
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: item_func.cc:5482
OWNER_THREAD_ID: 35
OWNER_EVENT_ID: 3
1 row in set (0.00 sec)
mysql>
The meanings of the columns in this result are mostly adequately documented at https://dev.mysql.com/doc/refman/en/metadata-locks-table.html, but one point of confusion is worth noting: the OWNER_THREAD_IDcolumn does notcontain the connectionID (like would be shown in the PROCESSLISTor returned by CONNECTION_ID()) of the thread that holds the lock. Confusingly, the term "thread ID" is sometimes used as a synonym of "connection ID" in the MySQL documentation, but this is notone of those times. If you want to determine the connectionID of the connection that holds a lock (for instance, in order to kill that connection with KILL), you'll need to look up the PROCESSLIST_IDthat corresponds to the THREAD_IDin the performance_schema.threadstable. For instance, to kill the connection that was holding my lock above...
此结果中各列的含义在https://dev.mysql.com/doc/refman/en/metadata-locks-table.html 中有充分的说明，但有一点值得注意：该OWNER_THREAD_ID列没有包含持有锁的线程的连接ID（就像显示在 中PROCESSLIST或由 返回CONNECTION_ID()）。令人困惑的是，术语“线程 ID”有时在 MySQL 文档中用作“连接 ID”的同义词，但这不是其中之一。如果要确定持有锁的连接的连接ID（例如，为了使用 终止该连接KILL），则需要查找PROCESSLIST_ID与THREAD_ID在performance_schema.threads表中。例如，要杀死上面持有我的锁的连接......
mysql> SELECT OWNER_THREAD_ID FROM performance_schema.metadata_locks
-> WHERE OBJECT_TYPE='USER LEVEL LOCK'
-> AND OBJECT_NAME='foobarbaz';
+-----------------+
| OWNER_THREAD_ID |
+-----------------+
| 35 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT PROCESSLIST_ID FROM performance_schema.threads
-> WHERE THREAD_ID=35;
+----------------+
| PROCESSLIST_ID |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)
mysql> KILL 10;
Query OK, 0 rows affected (0.00 sec)
回答by Robert
SHOW FULL PROCESSLIST;
You will see the locks in there
你会看到里面的锁
回答by Mark Amery
If you just want to determine whether a particularnamed lock is currently held, you can use IS_USED_LOCK:
如果您只想确定当前是否持有特定的命名锁，您可以使用IS_USED_LOCK：
SELECT IS_USED_LOCK('foobar');
If some connection holds the lock, that connection's ID will be returned; otherwise, the result is NULL.
如果某个连接持有锁，则返回该连接的 ID；否则，结果为NULL。
回答by arheops
I found following way which can be used if you KNOW name of lock
如果您知道锁的名称，我发现可以使用以下方法
select IS_USED_LOCK('lockname');
however i not found any info about how to list all names.
但是我没有找到任何关于如何列出所有名字的信息。
回答by Anvesh
Reference taken from this post:
You can also use this script to find lock in MySQL.
您还可以使用此脚本在 MySQL 中查找锁。
SELECT
pl.id
,pl.user
,pl.state
,it.trx_id
,it.trx_mysql_thread_id
,it.trx_query AS query
,it.trx_id AS blocking_trx_id
,it.trx_mysql_thread_id AS blocking_thread
,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl
INNER JOIN information_schema.innodb_trx AS it
ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
ON it.trx_id = ilw.requesting_trx_id
AND it.trx_id = ilw.blocking_trx_id
回答by Sonicthoughts
Another easy way is to use:
另一种简单的方法是使用：
mysqladmin debug
This dumps a lot of information (including locks) to the error log.
这会将大量信息（包括锁）转储到错误日志中。
