Using mysql innodb as a queue?

I have two jobs pulling from a mysql table. They both want to get a row, do some work on it and update that row with the results, however I don't want them selecting the same row to update. Whats the best way using mysql/innodb engine to lock a row in the select so that it will be unavailab开发者_运维百科le to other threads but allowing them to select other records? is that possible?

thanks!

---

You can add the "FOR UPDATE" modifier to the end of your select statement. i.e.

SELECT * FROM table WHERE pkey = N FOR UPDATE;

.. but I think you should be careful when using InnoDB as a queue and aiming for performance. Queues are stack oriented, and need different tools to what InnoDB (and MySQL) are trying to solve.