MySQL Locking – Two Events Scheduled After Identical Seconds

lockingMySQL

I am running a survey and i am getting lots of data and i need to process that data quickly from within mysql.I am using a mysql events to help me process the hundreds of rows that i have.

I have written this routine that i am calling from an event.

DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN  

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());

UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

END $$
DELIMITER ;

When i process a row, i mark that row with 1 to show that is read and the ones that are not read read 0.

This is the event i am calling the routine from

CREATE EVENT survey_processor_1
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

My question is,if i have another identical event that calls survey_task at exactly the same time as survey_processor_1

CREATE EVENT survey_processor_2
    ON SCHEDULE EVERY 10 SECOND
    DO
       call survey_task();

will survey_task be called twice or does mysql have a way of making one of the two events to acquire some form of a lock and thus allow one to execute ahead of the other?.

Best Answer

You could stagger survey_task yourself by calling the following functions

This supports the paradigm known as cooperative or advisory locking.

Use in the survey_task as follows:

DELIMITER $$
CREATE PROCEDURE survey_task()
BEGIN  

WHILE IS_FREE_LOCK('survey_task_lock') = 0 DO
    SELECT SLEEP(0.1) INTO @x;
END WHILE;
SELECT GET_LOCK('survey_task_lock',10) INTO @x;

select tel_number, text_message, id INTO @t_number, @t_message, @t_id from incoming_messages where t_status = 0 limit 1;

insert into surveyed_lot(telephone_number, survey_answer,date_of_insert )
        values(@t_number, @t_message,now());
        
UPDATE incoming_messages SET t_status = 1 WHERE id = @t_id;

SELECT RELEASE_LOCK('survey_task_lock') INTO @x;

END $$
DELIMITER ;

The idea is to have the other invocation of survey_task poll every 0.1 seconds until the lock is free. Then acquire the lock and release it when done.

GIVE IT A TRY !!!

Related Question