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

Answer 1

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