0

I have a stored procedure which picks (nu-processed) one row from a table and do the processing of the data and then mark that as processed. I am calling this stored procedure from MySQL Events/Job and I have selected the schedule of this as every second. Now if this stored procedure take 1 to 5 seconds to process single row based on the server load, I want to handle this execution of stored procedure to run if previous execution is completed otherwise same row will be picked for processing multiple times.

Following is the sample code which I am using right now.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` EVENT `sp_process_data_row_by_row` ON SCHEDULE EVERY 1 SECOND STARTS '2022-10-10 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    DO RELEASE_LOCK('sp_process_data_row_by_row');
END;
IF GET_LOCK('sp_process_data_row_by_row', 0) THEN
BEGIN
    CALL sp_process_data_row_by_row( 'migrate_data' , NULL, NULL, NULL );
END IF;
DO RELEASE_LOCK('sp_process_data_row_by_row');
END$$
DELIMITER ;

Thanks ! RJ

Rabeel Javed
  • 1
  • 1
  • Ther events are executed in separate connections. For signal interchange you need in connection-independent storage. I recommend static service table ENGINE=Memory. – Akina Oct 10 '22 at 11:35
  • I have added the sample code which I am using right now. – Rabeel Javed Oct 10 '22 at 11:36
  • Add facts into the question text (edit it) marking them as updates. – Akina Oct 10 '22 at 11:37
  • *otherwise same row will be picked for processing multiple times.* You may solve this (miltiple processing) by adding a column which stores current status (free for processing, is processing now, was processed already). – Akina Oct 10 '22 at 11:40
  • Think the processing as: It is taking 10 seconds to pick one row for processing, then this one row will be marked as `In-Progress`. during these 10 seconds, MySQL event will be initiated 10 times. @Akina – Rabeel Javed Oct 10 '22 at 11:44
  • Well, the processing needs in 10s, and now there is no working event which processes a row. The table contains M rows to be processed and N processed rows. Current event checks the existence of currently processed row. It not exists, and the event updates one row and starts its processing. Next 9 events finds that the processed row exists, and they exits w/o an action. Now first event finishes the processing and marks the row as processed. Now the table contains M-1 rows to be processed and N+1 processed rows. Next, 11th, event will find that there is no processing, and it processes the row. – Akina Oct 10 '22 at 11:50

0 Answers0