CODEWITHSUNDEEP
phpmysqlcroncron-task
fiscme
fiscme

Reputation: 422

how to make sure sql table not locked / stop concurrent crons

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:

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

Upvotes: 0

Views: 169

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

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.

Upvotes: 1

Related Questions