Re: Multiple execution of the same job against same data
Date: Sun, 11 Oct 2015 15:31:22 -0700 (PDT)
Message-ID: <eb0503ef-e225-46cc-a5bc-6c838bb20926_at_googlegroups.com>
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]
>
>
> > Any help appreciated.
> >
> > Regards,
> >
> > Lou
> --
> Kees Nuyt
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?
Thanks,
Luis