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)
Best Answer
From MySQL 5.7 onwards, this is possible, but requires first enabling the
mdl
instrument in theperformance_schema.setup_instruments
table. You can do this temporarily (until the server is next restarted) by running:Or permanently, by adding the following incantation to the
[mysqld]
section of yourmy.cnf
file (or whatever config files MySQL reads from on your installation):(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 aSELECT
against theperformance_schema.metadata_locks
table. As noted in the docs,GET_LOCK
locks have anOBJECT_TYPE
of'USER LEVEL LOCK'
, so we can filter our query down to them with aWHERE
clause: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 thePROCESSLIST
or returned byCONNECTION_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 withKILL
), you'll need to look up thePROCESSLIST_ID
that corresponds to theTHREAD_ID
in theperformance_schema.threads
table. For instance, to kill the connection that was holding my lock above...