Selecting record from multiple computers

[eksob]

Here is the scenario:
multiple client computers and 1 server computer - server holds a 'jobs' database with records that need to be processed

The client computer connects to server, and requests the next record. After it does the select statement to get the next record it does an update to mark that record as 'in progress' and no other client computer are supposed to take that record. Before that can happen the second client also requests a job and gets the same one (the update has not happened yet or too slow and each client gets the same record) and each client is supposed to get a unique record.

Does anyone know if it's possible to prevent this? mysql table locking or row locking?

[aspdaddy]

You need to put the select statement inside a transaction and then inside a stored procedure.