How do I prevent wait time with an asynchronous process?

Question

I have a simple reporting script that takes 5-10 minutes to run. It's triggered asynchronously via an fsockopen call to run in the background. It works wonderfully some of the time. But if a user refreshes the main page while the async report triggered from the previous pageload is still running the main page hangs until the async process from the first page load completes.

Below is the rough logic behind my scripts, followed by more details of what works and what doesn't...

---

main.php

if last report was already run within the hour (mysql select last report time)
    display existing report, that's it.
else
    log latest report process request
    run report asynchronously in the background (async.php)
    notification when updated report is completed (simple ajax pinger every 10s)

async.php

run report
update last report request table with "complete" status
the ajax pinger in main.php pulls the "complete" record and triggers notification

---

The problem

1. User loads main.php

   Result: Great. Loads instantly, logs new report request and triggers asynchronous call as expected. Async.php will now take 5-10 minutes to complete report. All in the background.

2. User loads main.php 20 minutes later (after the async.php finishes)

   Result: Great, loads instantly, but skips running the async report process because it's within an hour of last request.

3. User waits an hour and loads main.php again

   Result: All good, as in step 1

   All good so far, but...

4. Now user loads main.php only 2 minutes after step 3.

   Result: FAIL! This page load will hang until the async process triggered in step 3 completes. Even though the mysql table with the request log time is updated instantly in step 3. Therefore step 4 should simply skip the call and just render the existing report like step 2.

What in the world? What do you recommend to debug this? If another user wants to run the same report while user 1 is hanging on step 4, this other user runs a new report just fine.

I'm not doing anything fancy in my code. Just simple if/then and mysql select lookups. The async script largely pulls in from external sources, so it's not locking up mysql tables that might prevent a request log time lookup (a full report may only run a hundred 5ms queries over 10 minutes and none of which touch the request log).

One solution is to just cron the processes, but I'm concerned that I'm running a lot of reports that will never be seen. And instead of a report only taking 5-10 minutes it would get exponentially larger if I ran them all more frequently.

So, sticking with my above plan of attack (for now), what do you recommend? Why would step 2 load correctly, but not step 4? Is there some sort of script lock or limit per user I'm not aware of?

Answer 1

I ended up taking regilero's advice. I set up a cron to run every minute and pick up any new requests in the queue. This eliminated the lag issue entirely. Thanks regilero!