How to prevent mysql/php from serving same row on unlikely syncronous requests
All data to be processed is stored in a mysql database on a server (MyISAM engine).
Data is accessed via php using a complex mysql selection query.
Workers come in the form of EXEs that ping the php script for work to do.
Each task can take 2-3 minutes for the worker to complete. As soon as the work row is selected, a update query is issued that date/time stamps the work row as being in progress.
On very rare occaisions, when two workers request new work at the exact same moment(within 120ms or so) and when the work pool is small, two workers get given the same task.
Is there any way via SQL to lock the table from being read by other users/scripts during the time it takes for the selection query to run and the update to be issued?
I have looked into transactions previously. from what I have read transactions prevent the row from being written to, not read.