Mysql - Running only one instance of stored procedure called from inside scheduled event

Question

Summary

Given an event that calls a stored procedure, what's the best practice to ensure that only one instance of the procedure is running at a time? Specifically in cases where the procedure may sometimes take longer to run than it takes for the event to tick over.

Example

Let's take the following fabricated example, an event that takes 1 second to tick over and a procedure that takes 5 seconds to execute:

Procedure:

DELIMITER ;;
CREATE PROCEDURE `P_wait`()
BEGIN
    SELECT SLEEP(5);
END;;
DELIMITER ;

Event:

DROP EVENT IF EXISTS `E_wait`;
DELIMITER ;;
CREATE EVENT `E_wait`
    ON SCHEDULE
        EVERY 1 SECOND
    DO
        BEGIN
            CALL `P_wait`();   //proc_call
        END;;
DELIMITER ;

As you would expect, when the event is running you will see 5 instances of SLEEP() in the PROCESSLIST:

mysql> SHOW PROCESSLIST;
+-------+------+-----------+-------+---------+------+-------------+------------------+
| Id    | User | Host      | db    | Command | Time | State       | Info             |
+-------+------+-----------+-------+---------+------+-------------+------------------+
| 27045 | root | localhost | temp  | Query   |    0 | NULL        | SHOW PROCESSLIST |
| 27069 | root | localhost | temp  | Connect |    4 | User sleep  | SELECT SLEEP(5)  |
| 27070 | root | localhost | temp  | Connect |    3 | User sleep  | SELECT SLEEP(5)  |
| 27072 | root | localhost | temp  | Connect |    2 | User sleep  | SELECT SLEEP(5)  |
| 27073 | root | localhost | temp  | Connect |    1 | User sleep  | SELECT SLEEP(5)  |
| 27074 | root | localhost | temp  | Connect |    0 | User sleep  | SELECT SLEEP(5)  |
+-------+------+-----------+-------+---------+------+-------------+------------------+

While it's not the case in this example, you can see how if the procedure was blocking (like if it contained a transaction with a SELECT ... FOR UPDATE Statement) this would quickly escalate into problems.

Question

What's the best way of ensuring that when E_wait ticks over each second, that if an instance of P_wait is still running, it won't get called again? I only ever want at most one instance of P_wait running at a time. What's the Best Practice here?

Basically what would I need to put in place of /*PROCEDURE_NOT_RUNNING*/ if I modified the Event as follows:

    ...
        BEGIN
            IF /*PROCEDURE_NOT_RUNNING*/ THEN
                CALL wait_test();   //proc_call
            END IF;
        END;;
    ...

Altering the event from within the procedure (disabling E_wait at the beginning of P_wait and re-enabling it at the end) is not an option, since that could result in the event not running at all if the server restarts while it is disabled.
I could try storing a value in a table and using that to figure out if the procedure was still running, but that seems to cary the same problem of potentially locking up if the server stops running at exactly the wrong time.
The whole point of this discussion is that the procedure could take an arbitrarily long time to execute, so just changing the Event schedule to 5 seconds is not acceptable :P

Desired Result

To run the above event, and only ever see at most one instance of SELECT SLEEP(5) in SHOW PROCESSLIST.

If you want to know if is there any events running: https://stackoverflow.com/a/56371180/1307507 — Marcelo Amorim, May 30 '19 at 03:19

score 6 · Accepted Answer · edited May 23 '17 at 12:07

It has been suggested to use some of the built-in lock functions for this problem. I will give that a shot and will update if I've managed to solve the problem.

EDIT:

I achieved the desired result after modifying the Event to include a lock:

DROP EVENT IF EXISTS `E_wait`;
DELIMITER ;;
CREATE EVENT `E_wait`
    ON SCHEDULE
        EVERY 1 SECOND
    DO
        BEGIN

            SELECT GET_LOCK('temp.E_wait', 0) INTO @got_lock;
            IF @got_lock = 1 THEN

                CALL `P_wait`();

                SELECT RELEASE_LOCK('temp.E_wait') INTO @discard;
            END IF;

        END;;
DELIMITER ;

The answer to this stackoverflow question helped.

Thanks, that's exactly what I was looking for. – TheBrain Aug 15 '19 at 12:07 — TheBrain, Aug 15 '19 at 12:07

score 0 · Answer 2 · answered Jul 12 '21 at 04:25

In my case (using MariaDB) I had to use a table (running) to store when procedure started to run.

CREATE TABLE `running` (
  `process` varchar(100) NOT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`process`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And when running procedure you can just check if it has been started (by anyone). Timeout in my case is 15 minutes.

    set @already_running = (
        select process from running
        where process = "my_proc_name" and started_at > (now() - interval 15 minute)
    );

    if (not isnull(@already_running)) then
        select 'my_proc_name already running...';
        leave `my_proc`;
    end if;

    update running set started_at = now() where process = "my_proc_name";

But you have to name your procedure in order to make leave `my_proc`; exit procedure.

CREATE PROCEDURE `some_other_name`()
`my_proc`:
BEGIN
...
-- before you exit you can put any old time that is more than your interval to check
update running set started_at = (now() - interval 1 hour) where process = "my_proc_name";
END