How to manage a multi user job list in MySQL?

Question

I have a list of jobs for multiple users stored in a MySQL table. I'm currently planning to do the following in the client app:

Ask MySQL server for jobs that are not allocated to anybody.
Mark the first job allocated to myself.

But the problem is, if 2 users somehow get the same list of "unallocated" jobs, they will both mark the same job as allocated. So how to manage such a situation, and ensure that each user gets only a unique unallocated job?

I'm trying to avoid using stored procs since I want all code within the app if possible.

Ricardo Ortega Magaña · Accepted Answer · 2013-01-27T09:04:47.770

2

Sorry, the way you like it, you will need a trigger to avoid sending the same list, allocating at the moment of quering. Or you can blind accept and allocating at the moment of quering... Someting like this:

   Update jobs set allocatedto=myid where status=notallocated limit 1
   select * from jobs where status=allocated and allocatedto=myid limit 1;
   select * from jobs where status=not allocated;

edited Jan 27 '13 at 09:04

answered Jan 27 '13 at 08:57

Ricardo Ortega Magaña

2,403
16
23

But will the update query return the ID? How do I fetch that? – Robin Rodricks Jan 27 '13 at 09:12
[Here you can find the answer](http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql) – Ricardo Ortega Magaña Jan 27 '13 at 09:14

How to manage a multi user job list in MySQL?

1 Answers1