Re: Multiple execution of the same job against same data

On Sun, 11 Oct 2015 06:34:55 -0700 (PDT), taguato1_at_gmail.com wrote:

> Hi folks:
>
> Environment: linux, php and mysql.
>
> I have a process that I am planning to execute automatically
> with a CRON, every 5 minutes. This process updates all records
> with status 'P' to 'E', based on certain criteria. That far
> everything good and simple.
>
> But here is the kicker: On the front end, users are constantly
> adding new 'P' records to the table, so it could happen that
> before the update process ends, there may be new 'P' records
> added.
>
> The questions are:
>
> 1) Will the update process update those as well, or they will
> be left 'P' until the next execution?

You need proper isolation, offered by transactions in the InnoDB engine. Read:
http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html

> 2) What if there are so many 'P' records that the update process
> doesn't end within the 5 minutes and CRON fires up another instance
> of the update process? Will that screw up the database?

It might not screw up the database, but it is counter-productive. The previous invocation is probably delayed because there is more to do, or the system is busy with something else. The job script should prevent a second invocation. There are several ways to do that, most commonly a "lock file" is used. Something like:

#!/usr/bin/yourshellhere
#
pidfile=/var/run/mysqljobname.pid
if [ -f $pidfile ]
then

	prvpid=$(cat $pidfile)
	printf "Job already active, pid: %s\n" $prvpid
else
	echo $$ >$pidfile
	#
	# insert your existing script here
	#
	rm $pidfile

(untested)

[improved line wrapping - the lines in your posting were too long]

> Any help appreciated.
>
> Regards,
>
> Lou

-- 
Kees Nuyt