Is there a way for MySQL to wait for rows matching a condition to be inserted

Question

Let's say i was writing an aplication where'd i'd need to get notifications in real time from a server, and let's say those notifications are stored on a mysql database. For me to get them i'd have to keep polling the mysql server (keep repeating the same select query till i actually get results) but i figure that is very unefficient way of doing it since most of the time the select would turn up empty . If i do it often it's unreasonable strain on the server if i do it rarely the notifications would come in very late. So i was wondering if there is a way for say a mysql query to block until a result matching a condition becomes available.

list = query ("SELECT * FROM `notifications` WHERE `unread`=1") ;

instead of returning an empty list if there is no unread notifications , it would instead wait till there actually are unread notifications to return

Answer 1

I recommend using the producer consumer pattern, implemented with a new table as the "work queue". There is no need for a stored procedure, because the trigger is so simple.

1. A trigger would populate the work queue
2. Code would poll the work queue table. Because the table would be very small, the query would be fast and low-load.
3. Code would do whatever you need and delete rows from the table when finished - keeping it as small as possible

Create a table with the id of the notification to be processed and a "processing status" column, for example:

create table work_queue (
    id int not null auto_increment,
    notification_id int references notifications,
    status enum ('ready', 'processing', 'failed')
);

Create a simple trigger that populates a the work queue table:

delimiter $
create trigger producer after insert on notifications
for each row begin 
    insert into work_queue (notification_id, status) 
    select new.id, 'ready'
    where new.unread;
end; $
delimiter ;

Your code would have the pseudo code:

1. select * from work_queue where status = 'ready' order by id limit 1
2. update work_queue set status = 'processing' where id = <row.id>
3. Do what you need to notifications where id = <row.notification_id>
4. either delete from work_queue where id = <row.id> or update work_queue set status = 'failed' where id = <row.id> (you'll have to figure out what to do with failed items)
5. Sleep 1 second (this pause needs to be about the same as the peak arrival rate of notifications - you'll need to tune this to balance between work_queue size and server load)
6. goto 1.

If you have a single process polling, there is no need for locking worries. If you have multiple processes polling, you'll need to handle race conditions.