0

I have a cron job running every few minutes that goes through the first 50 items on a 'to do' list, runs a script on each item and marks it as run in the database.

This was working well until the table that marks off the item as complete got locked by something and the items didnt get marked off. Then the cron ran again and went through the same 50 items.

This repeated and repeated until the process list in mysql was massive and only getting larger by 50 every few mins.

How could i stop this happenning? my thoughts would be:

  • Add code to the script to see if the cron is still running before running again
  • check the mysql table for the locked table before running the new SELECT query to get the next 50 items

They both seem to have ups and downs and i am not sure how to implement either properly. Anyone have any advice / better ideas?

fiscme
  • 422
  • 1
  • 6
  • 20
  • Add a lock file to your update shell script. 1) check if lock exists: exit else create lock 2) do work 3) remove lock – wildplasser Mar 20 '13 at 17:47

1 Answers1

1

There are several approaches you could take.

  1. Modify you table to keep track of the status. e.g. ToDo, Started, Complete. And only process "ToDo". And don't start a task until you can mark it as Started. You will need to create a way to restart tasks in case the cron job fails.

  2. Use transactions and roll back the content of the task if you're not able to mark it as complete.

  3. Synchronize the execution of the cron job to allow only one execution at a time. To do this you could take a lock on a file. Check the file at the beginning and if it can't get the lock you exit.

  4. Have the cron job run forever in a look that sleeps instead of periodically executing.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Thanks very much for these, I think option 1 would be my best bet, although it would require an extra SQL call. It would add the extra functionality of people being able to see the which items are in progress at any moment. – fiscme Mar 30 '13 at 14:11