Concurrency problem in select ... for update

[andrejl]

Hello!

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.

[gmouse]

This is very weird and should work just fine. You could try reproducing it with two manual clients where you delay the commit in one client and then try getting the same message in the other client.

In this case, you don’t need select…for update:

UPDATE messages SET inv = 1, id=LAST_INSERT_ID(id) WHERE inv = 0 ORDER BY id LIMIT 1
SELECT * from messages WHERE id=LAST_INSERT_ID()

[andrejl]

Thanks for your answer. But fortunately I have found a cause of problem. This is not … mysql mistake (fuf) . This is program mistake. We use master/slave configuration and different connections for read and write. Transaction is started in one connection but select … for update is in another. Sometimes a lot of abstraction layers in code is evil. I have found this error when tried to implement suggested solution.
Thanks again.