I use mysql for organization of message queue. For accessing to messages the following construction is used:
start transaction;select * from message where inv = 0 order by id limit 1 for update;update messages set inv = 1;commit;
There are several threads which read from table ‘messages’.
It works great if speed of processing a message is slow. Processes don’t try to read from db at the same time. But if several processes send queries to mysql simultaneously sometimes row in table isn’t blocked and we process the same message several times.
Is this mysql problem? Maybe it’s necessary to adjust any settings.
Thanks in advance.