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

Question

Let's say I'm writing an application where I will need to receive real-time notifications from the server, and let these notifications be stored in the mysql database. For me, to get them, I would have to continue polling the mysql server (repeat the same query selection until I get the results), but I think this is a very inefficient way to do this, since most of the time the choice will be empty. If I do this often, this is unreasonable voltage on the server, if I rarely notify him, but very late. So I was wondering if there is a way to say mysql query to lock until a result matching the condition is available.

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

instead of returning an empty list if there are no unread notifications, it will instead wait until the unread notifications to return are actually read

Answer 1

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

• The trigger will populate the work queue.
• The code will poll the work queue table. Since the table will be very small, the query will be fast and low in load.
• The code will do everything you need and remove rows from the table when they are completed - as little as possible

Create a table with the notification identifier for processing and the column "processing status", 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 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 will have a pseudo-code:

• select * from work_queue where status = 'ready' order by id limit 1
• update work_queue set status = 'processing' where id = <row.id>
• Do what you need, notifications where id = <row.notification_id>
• either delete from work_queue where id = <row.id> , or update work_queue set status = 'failed' where id = <row.id> (you need to figure out what to do with the failed elements)
• Sleep 1 second (this pause should be about the same as the maximum speed for sending notifications - you need to configure it to balance the size of workload and server load).
• go 1.

If you have one survey process, there is no need to block anxiety. If you have a survey of several processes, you will need to handle the race conditions.