CODEWITHSUNDEEP
mysqleventslockingmariadb
Gandalf
Gandalf

Reputation: 9855

MySQL/MariaDB GET_LOCK lifecyle with Events

I am attempting to use the GET_LOCK() functionality of MariaDB 10.0.19 to implement a system that limits events run by the event scheduler to only run one at a time. Essentially each event tries to acquire a 'run_lock' and if successful it runs, otherwise it waits till the lock is available or times out. The issue is, in testing, I can find many cases where my stored procedure does not release its lock even if it completes successfully. Now I know when testing at the command line the lock is associated with my session - so if I exit the lock is released. What I cannot seem to test/figure out is if an event terminates (normally or abnormally) will all of it's locks be released?

Upvotes: 1

Views: 691

Answers (2)

abbas
abbas

Reputation: 7081

You should proceed only if the lock is obtained successfully

DELIMITER //

CREATE EVENT test_lock_1 ON SCHEDULE EVERY 1 MINUTE DO 
BEGIN

  IF GET_LOCK('test', 30) IS TRUE THEN
    SELECT SLEEP(65);
  END IF;

END //

DELIMITER ;

Upvotes: 0

Gandalf
Gandalf

Reputation: 9855

So I created two events as such 

DELIMITER //

DROP EVENT IF EXISTS test_lock_1;

CREATE EVENT test_lock_1
    ON SCHEDULE EVERY 1 MINUTE STARTS NOW()
DO this_event:BEGIN

   SELECT GET_LOCK('test', 75);

   SELECT SLEEP(55);

END //

DELIMITER ;


DELIMITER //

DROP EVENT IF EXISTS test_lock_2;

CREATE EVENT test_lock_2
    ON SCHEDULE EVERY 30 SECOND STARTS NOW()
DO this_event:BEGIN

   SELECT GET_LOCK('test', 25);

   SELECT SLEEP(45);

END //

DELIMITER ;

Notice no releasing of the shared lock. The lock did indeed get unlocked after the event exited, normally or abnormally. So I surmise each event runs in it's own session and upon exit all locks are released.

Upvotes: 1

Related Questions