mysql database update possible timing issue

Question

I have a strange situation. I have a cronjob running queue tasks one after the other. I have a delay of 1s between the tasks.

After a task is run, it sets processed=1, and continues with the next one, that has processed=0.

I have some situations where a task is executed twice. This means that processed=1 was set, but when we read again for processed=0, it finds it.

I use PHP, and ->execute(). Is it possible changes in the database not to be reflected immediately for some reasons?

• Akina: Do not use CRON - use MySQL Scheduler. Do not use separate tasks (and processed flag) - use one "hyper-task" which executes separate tasks one-by-one.
• Aris: @Akina i do prefer to use my current architecture. But will check mysql scheduler, thanks!
• Rick James: Why even use MySQL Scheduler? Won't it have the same problem as "cron"? Instead, simply have a separate task continually running. (You could have a cronjob performing a "keep-alive" on that task.)

Answer 1

The issue was that since the queue run for 1 minute, there were cases that one cronjob was not finished yet, and another was starting. So they were processing the same tasks from the database.

The solution is to run cronjob using flock(), thus making sure that only 1 is run:

* * * * * /usr/bin/flock -n /tmp/fcj.lockfile /usr/local/bin/frequent_cron_job