Parameterise GET_LOCK based on Database Schema #mysql

get_lock.sql

-- when creating events on MySQL, 
-- you probably don't want the events to run simultaneously
-- if the execution of a event takes too long
-- this shows you to use GET_LOCK and RELEASE_LOCK with events
-- note that GET_LOCK and RELEASE_LOCK unlike many other MySQL commands
-- do not automatically namespace themselves within the currently used database
-- so we have to use this CONCAT_WS technique
-- note that unlike cron, mysql events when scheduled as recurring without a designated start date
-- will immediately execute upon creation
-- you can confirm this with SELECT * FROM information_schema.events\G

USE exampleDatabase;

DROP EVENT IF EXISTS exampleEvent;

DELIMITER //

CREATE EVENT exampleEvent
ON SCHEDULE EVERY 1 SECOND
DO BEGIN
  IF (SELECT GET_LOCK(CONCAT_WS('.', DATABASE(), 'exampleEvent'), 0)) THEN
    -- do something here...
    SELECT RELEASE_LOCK(CONCAT_WS('.', DATABASE(), 'exampleEvent'));
  END IF;
END //

DELIMITER ;