0

I have an event in MySQL that I want to run very frequently at least every 30 seconds.

It is processing data from a queue table that contains recently updated records. Sometimes I receive large batches of updates. When this occurs the event may take longer to run than the usual 2-3 seconds. If it is still running at the time of the next schedule, I want the next event to skip execution.

The best way I could think about doing this is to create a 'state' table in which I set a specific key to 1 when the process starts, set it back to 0 when it is complete. I'd then alter the event to check the current status.

I'd prefer to do something nicer than that. Is there a feature I am missing completely?

I've looked into global variables but based on the documentation these only seem permissible for system variables.

Current Example Code

Here is the example code I'm currently testing.

acca_sync: BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
    @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
    call pa.log(concat("acca-acc_sync"," - Error - ", ifnull(@full_error,    "no error message")));
    UPDATE `acca`.`processing_state`
      SET `value` = 0
      WHERE `key` = 'acca_sync';
  END;

  call pa.log(CONCAT("Started acca_sync @ ", NOW()));
  SELECT `value`
    into @is_locked
    from `acca`.`processing_state`
    where `key` = 'acca_sync';

  IF @is_locked = 0 THEN
    UPDATE `acca`.`processing_state`
      SET `value` = 1
      WHERE `key` = 'acca_sync';
  ELSE
    CALL pa.log(CONCAT("acca_sync deferred due to active sync. @ ", NOW()));
    LEAVE acca_sync;
  END IF;

  call acca.event_sync();
  call pa.log(CONCAT("Completed acca_sync @ ", NOW()));

  UPDATE `acca`.`processing_state`
    SET `value` = 0
    WHERE `key` = 'acca_sync';

END

Table Locking

Based on a comment I want to explain why I am not using a table lock. My experience with table locks is limited so I hope the below is correct and makes sense.

Source Data Tables

I have triggers that notify updates in queue tables. These are my source data tables from which I read data to process it.

My understanding is that a READ lock on these tables would not lock any other events that just read. If I was to use a WRITE lock I would block any updates to any of the rows that I don't currently access.

Target Data Tables

I have multiple data sources that process data in different events. The rows these will amend in the target tables. There may be two different events running at the same time writing to the same table, so I don't artificially want to block the target tables

Other Tables

I could create a fake table that I would only have for the purpose of setting and then checking the existance of a lock. This seems absurd and I'd much rather instead create a single table locks with a lock_key and an is_locked column that I query each time.

Hans
  • 2,800
  • 3
  • 28
  • 40
  • You may want to check this one: http://stackoverflow.com/questions/7676164/how-to-wait-for-2-seconds# –  Jul 01 '16 at 09:39
  • 1
    Have you considered table locks? – kentor Jul 01 '16 at 09:43
  • I have actually - sorry for not mentioning it. The issue is that the source data is messy (often misses some referenced data that will be available later.) So I'd have to lock a fair few tables and I want to keep all this in one event as the sequence is my best chance of getting it all processed in one go. So the best I colud do is create a lock on a single table somewhere blocking the full process. – Hans Jul 01 '16 at 09:49
  • You need to better define how you want these two events to play fiddle together. When you do that, I can suggest something. – Drew Jul 01 '16 at 11:47
  • 1
    I have code to do this well, but it is like 500 lines of code. Mainly about 100 lines, but to illustrate it with 4 tables and 3 events competing, and well documented, the line count spikes up. – Drew Jul 04 '16 at 12:40

1 Answers1

0

You can verify if is there any event running:

 SELECT * 
 FROM performance_schema.threads 
 WHERE NAME LIKE '%event_worker%' 
      AND TYPE='FOREGROUND'

Other interesting table to watch: 

 SELECT * FROM information_schema.processlist
Marcelo Amorim
  • 1,662
  • 23
  • 23