Using MySQL Event Scheduler and how to prevent contention

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:

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.

Share this post

Comments (6)

  • Andrey Reply

    It’s better if you put the code of the Event into a stored procedure and your event becomes just CALL your_sp(). By doing this you don’t need to drop and recreate your event if you want to change its body.

    February 25, 2015 at 11:42 am
  • Salko Reply

    Is necessary SQLEXCEPTION?
    When event failed or was killed, lock for this connection disappear too. Or not?

    February 28, 2015 at 6:11 am
  • Demian Bigelow Reply

    What about if you want to create an event in a percona mysql cluster? Creating an event in one node creates it in all the nodes. I also tested the various lock functions that you used in you example in my cluster and they are global to a server, not to a cluster.

    It seems like I need one of these three options:
    1) a way to create the event on only one node
    2) a way for the event to look at a cluster wide lock
    3) a way to synchronize the events so that they take turns executing (I don’t like this idea)


    July 12, 2015 at 8:00 pm
  • Phil Stracchino Reply

    I have a more pointed version of the question above: Can the event scheduler be considered safe *at all* in XtraDB Cluster?

    April 4, 2016 at 9:31 pm
  • Phil Stracchino Reply

    After some further research, I believe I have an answer to the question I posed. It appears that any scheduled event which causes a schema change — and for this purpose, TRUNCATE TABLE must be considered a schema change — is unsafe on XtraDB Cluster, as it creates a race condition in which each node independently tries to put the same table into TOI. If any two or more nodes try to do this exactly simultaneously, the result is what could be called a TOI deadlock. The cluster appear to have no mechanism to resolve this TOI deadlock, and the conflict brings down the cluster.

    April 5, 2016 at 9:08 am

    I will migrate from MYSQL Community to Percona XtraDB Cluster with three nodes and Percona ProxySQL
    I have an issue now, How can I deal with MYSQL Events inside my database. the problem is the event works together in all nodes.

    Could you please tell me how can i deal with them just from one node and if this node fails it should works from another node.

    August 14, 2017 at 9:26 am

Leave a Reply