title: Mysql - Running only one instance of stored procedure called from inside scheduled event
body: <h1>Summary</h1>
  
  <p>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.</p>
  
  <hr>
  
  <h1>Example</h1>
  
  <p>Let's take the following fabricated example, an <strong>event</strong> that takes <strong>1</strong> second to tick over and a <strong>procedure</strong> that takes <strong>5</strong> seconds to execute:</p>
  
  <h2>Procedure:</h2>
  
  <pre><code>DELIMITER ;;
  CREATE PROCEDURE `P_wait`()
  BEGIN
      SELECT SLEEP(5);
  END;;
  DELIMITER ;
  </code></pre>
  
  <h2>Event:</h2>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>As you would expect, when the event is running you will see 5 instances of <code>SLEEP()</code> in the <code>PROCESSLIST</code>:</p>
  
  <pre><code>mysql&gt; 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)  |
  +-------+------+-----------+-------+---------+------+-------------+------------------+
  </code></pre>
  
  <p>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 <code>SELECT ... FOR UPDATE</code> Statement) this would quickly escalate into problems.</p>
  
  <hr>
  
  <h1>Question</h1>
  
  <p>What's the best way of ensuring that when <code>E_wait</code> ticks over each second, that if an instance of <code>P_wait</code> is still running, it won't get called again? I only ever want at most one instance of <code>P_wait</code> running at a time. What's the Best Practice here?</p>
  
  <p>Basically what would I need to put in place of <code>/*PROCEDURE_NOT_RUNNING*/</code> if I modified the Event as follows:</p>
  
  <pre><code>    ...
          BEGIN
              IF /*PROCEDURE_NOT_RUNNING*/ THEN
                  CALL wait_test();   //proc_call
              END IF;
          END;;
      ...
  </code></pre>
  
  <ul>
  <li><a href="http://dev.mysql.com/doc/refman/5.1/en/alter-event.html" rel="nofollow">Altering the event</a> from within the procedure (disabling <code>E_wait</code> at the beginning of <code>P_wait</code> 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.</li>
  <li>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.</li>
  <li>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</li>
  </ul>
  
  <hr>
  
  <h1>Desired Result</h1>
  
  <p>To run the above event, and only ever see at most one instance of <code>SELECT SLEEP(5)</code> in <code>SHOW PROCESSLIST</code>.</p>