Using MySQL Event Scheduler and how to prevent contention

February 25, 2015

Author

Alexander Rubin

Insight for DBAs

MySQL

Share this Post:

MySQL introduced the Event Scheduler in version 5.1.6. The Event Scheduler is a MySQL-level “cron job”, which will run events inside MySQL. Up until now, this was not a very popular feature, however, it has gotten more popular since the adoption of Amazon RDS – as well as similar MySQL database as a service offerings where there is no OS level.

What is important to understand about the Event Scheduler is that it does not have any protection against multiple execution (neither does linux cron). Let’s imagine you have created an event that executes every 10 seconds, but the logic inside the event (i.e. queries or stored procedure call) can take longer than 10 seconds (may be in case of the high load), so it can pile-up. In the worst case, when an event contains a set of “insert” + “update”/”delete” statement inside a transaction, it can cause a deadlock.

Adding “get_lock” conditions inside of the event will help to prevent such situation:

If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking. Read more at event_scheduler documentation.

Function GET_LOCK() can be used for communications between threads:

The following example can illustrate using get_lock:

DELIMITER //<br>CREATE EVENT testlock_event ON SCHEDULE EVERY 2 SECOND DO<br>BEGIN<br> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION<br> BEGIN<br>   DO RELEASE_LOCK('testlock_event');<br> END;<br> IF GET_LOCK('testlock_event', 0) THEN<br>   -- add some business logic here, for example:<br>   -- insert into test.testlock_event values(NULL, NOW());<br>  END IF;<br>  DO RELEASE_LOCK('testlock_event');<br>END;<br>//<br>DELIMITER ;

1

DELIMITER // CREATE EVENT testlock_event ON SCHEDULE EVERY 2 SECOND DO BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN DO RELEASE_LOCK('testlock_event'); END; IF GET_LOCK('testlock_event', 0) THEN -- add some business logic here, for example: -- insert into test.testlock_event values(NULL, NOW()); END IF; DO RELEASE_LOCK('testlock_event'); END; // DELIMITER ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION is needed here to release lock even if the event failed or was killed.

The above GET_LOCK / RELEASE_LOCK combination will help to prevent contention inside the MySQL Event Scheduler.