Using mysql innodb as a queue?

Question

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 unavailable to other threads but allowing them to select other records? is that possible?

thanks!

Answer 1

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.