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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.