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.