由于延迟时间和多个请求，避免双DB查询

I have a MySql database linked to my website, that has a table with a task list (columns are separated by "/" below), i.e.:

task1 / doTask1.sh / todo

task2 / doTask2.sh / done

task3 / doTask3.sh / todo

task4 / doTask4.sh / todo

etc...

On every request to my website, I want the "todo" tasks to be executed and then marked as "done" by the server. The task can only be executed once. But here comes the problem.

What if:

User A selects the tasks that are marked as "todo". doTask1.sh executes and is marked as done, triggered by request from user A. User B selects the tasks that are marked as "todo" (task3 and task3 only, since task1 is done). doTask3.sh executes and is marked as done, triggered by request from user A. doTask3.sh executes again, triggered by request from user B. Same for doTask4.sh.

Can this happen using PHP/Nginx/MySql, due to simultaneous DB connections? If yes, how can this problem be bypassed/avoided?

In this example, I used doTaskx.sh to simplify the situation. In reality, this column would contain a description of the task and the PHP script powering my website would execute a task depending on that description.

Yes, this can happen.

One approach for solving this problem is to do the following:

  • Create a "pending" status".
  • When a request comes in to execute a task, run an update on the database setting everything matching the ID and status = "todo" to "pending".
  • If the number of records updated is 1, execute the task and mark it as "done".
  • If the number of records updated is 0, it means another request got there first.
  • Either way, update the user interface to show that the task is pending/done.

Lock the list. Take the requested tasks that are still to-do, mark them "in progress", unlock and then pass them to whatever executes the tasks.