# Concurrency problem in select ... for update

**URL:** <https://forums.percona.com/t/concurrency-problem-in-select-for-update/1644>\
**Category:** Other MySQL® Questions\
**Created:** [August 10, 2011, 4:23am UTC](https://forums.percona.com/t/concurrency-problem-in-select-for-update/1644 "2011-08-10T04:23:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrejl](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@andrejl](https://forums.percona.com/u/andrejl)\
**Post date:** [August 10, 2011, 4:23am UTC](https://forums.percona.com/t/concurrency-problem-in-select-for-update/1644/1 "2011-08-10T04:23:59Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [August 10, 2011, 5:04am UTC](https://forums.percona.com/t/concurrency-problem-in-select-for-update/1644/2 "2011-08-10T05:04:59Z")

</div>

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()

---

<div class="post-metadata">

**Author:** ![andrejl](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@andrejl](https://forums.percona.com/u/andrejl)\
**Post date:** [August 10, 2011, 5:40am UTC](https://forums.percona.com/t/concurrency-problem-in-select-for-update/1644/3 "2011-08-10T05:40:34Z")

</div>

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.