Show all current locks from get lock

Question

Is there any way to select   show all current locks that have been taken out using the GET LOCK function   Note that GET LOCK locks are different from table locks  like those acquired with LOCK TABLES - readers who want to know how to see those locks should read Detecting locked tables  locked by LOCK TABLE

User · Answer

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

User · Answer

From MySQL 5 7 onwards  this is possible  but requires first enabling the mdl instrument in the performance schema setup instruments table  You can do this temporarily  until the server is next restarted  by running   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 cnf file  or whatever config files MySQL reads from on your installation     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    Locks you take out after the mdl instrument has been enabled can be seen by running a SELECT against the performance schema metadata locks table  As noted in the docs  GET LOCK locks have an OBJECT TYPE of  USER LEVEL LOCK   so we can filter our query down to them with a WHERE clause   mysql gt  SELECT GET LOCK  foobarbaz   -1    ---------------------------    GET LOCK  foobarbaz   -1     ---------------------------                            1    ---------------------------  1 row in set  0 00 sec   mysql gt  SELECT   FROM performance schema metadata locks      - gt  WHERE OBJECT TYPE  USER LEVEL LOCK      - gt   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 gt     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 ID column does not contain the connection ID  like would be shown in the PROCESSLIST or 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 not one of those times  If you want to determine the connection ID 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 ID that corresponds to the THREAD ID in the performance schema threads table  For instance  to kill the connection that was holding my lock above     mysql gt  SELECT OWNER THREAD ID FROM performance schema metadata locks     - gt  WHERE OBJECT TYPE  USER LEVEL LOCK      - gt  AND OBJECT NAME  foobarbaz    -----------------    OWNER THREAD ID    -----------------                 35    -----------------  1 row in set  0 00 sec   mysql gt  SELECT PROCESSLIST ID FROM performance schema threads     - gt  WHERE THREAD ID 35   ----------------    PROCESSLIST ID    ----------------                10    ----------------  1 row in set  0 00 sec   mysql gt  KILL 10  Query OK  0 rows affected  0 00 sec

User · Answer

Reference taken from this post   You can also use this script to find lock in 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

User · Answer

Starting with MySQL 5 7  the performance schema exposes all metadata locks  including locks related to the GET LOCK   function   See http   dev mysql com doc refman 5 7 en metadata-locks-table html

User · Answer

If you just want to determine whether a particular named lock is currently held  you can use 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

User · Answer

Another easy way is to use   mysqladmin debug    This dumps a lot of information  including locks  to the error log

User · Answer

SHOW FULL PROCESSLIST    You will see the locks in there

[mysql] Show all current locks from get_lock

Examples related to mysql

Examples related to locking