Geotarget Geotarget - 4 years ago 112
SQL Question

How to manage a multi user job list in MySQL?

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.

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download