Re: Multiple execution of the same job against same data

On Sun, 11 Oct 2015 15:31:22 -0700 (PDT), taguato1_at_gmail.com wrote:

>El domingo, 11 de octubre de 2015, 12:18:54 (UTC-3), Kees Nuyt escribió:
>> 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
>> fi
>>
>> (untested)
>>
>>
>> [improved line wrapping - the lines in your posting were too long]
>>
[...]
>
> Thanks Kees, I will try that script. Looks pretty straight
> forward. Just one thing and just to be sure: If at the
> begining of the run I have 100 'P' records and DURING the
> run 20 more are added, will the update process update those
> as well? Or only the next run will get those?

If you implement transactions, the UPDATE statement in your script will not see any changes by other processes that were applied after its transaction started.

Good luck.

-- 
Kees Nuyt