Is there a way to enumerate all locks that are currently being held via get_lock()?

Question

I'm not seeing a way to list all locks currently being held by get_lock. I see you can check is_free if you know the name and release as well.

I don't see an obvious function or infomration_schema table that would hold that information.

Answer 1

You should create your own lock repository

CREATE DATABASE IF NOT EXISTS getlock;
CREATE TABLE IF NOT EXISTS getlock.lockbox
(
    conn_id BIGINT NOT NULL,
    name VARCHAR(64),
    PRIMARY KEY (name,conn_id)
    UNIQUE KEY (name)
) ENGINE=MEMORY;

Then, whenever you need to create a lock, you perform this sequence

SET @lockname = 'whateverlocknameihavechosen';
SELECT GET_LOCK(@lockname) INTO @x;
REPLACE INTO getlock.lockbox VALUES (CONNECTION_ID(),@lockname);
DELETE FROM getlock.lockbox WHERE conn_id NOT IN
(SELECT id FROM information_schema.processlist);

This will acquire the lock, insert or overwrite the previous lock owner, and cleanup the lockbox by locating all entries whose connection had terminated.

Since GET_LOCK() is unsafe for statement-based replication, you can locate the warnings written in the error log and should see the names of the locks.