Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BAB4263286E for ; Fri, 23 Apr 2010 18:34:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44268-02 for ; Fri, 23 Apr 2010 21:33:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.158]) by mail.postgresql.org (Postfix) with ESMTP id 9B96C632834 for ; Fri, 23 Apr 2010 18:33:51 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 19so406627fgg.1 for ; Fri, 23 Apr 2010 14:33:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=iMdKIc1Sy/wNH8EGpJUHctHdHT3gdKBjCxE7k6q2qqI=; b=g/ckELCceuP+28k7hBkldK6eI6d5aSQnAnDS7tXrs5T6aOd+IEZRgTpQ0UkCXSFIhV SQIPcYQEDLOSfJxQKHMWoiR2qRrkzgeY4X3zsmNuvxL8uHJLfK2MYpbQK9GerfgJ34O2 bHixAb35cBX57umTTeXSSJlxMMeFLS+WkXqCI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=eDUVHElD8okNf+wsyVpUOADruCPyeBnxWmfpOUbBnEO4eIaHfjhG1P5AFzDYUlQ9xW ptOHWH+B+n8PffG38/ypEr1lB3glg1wUkHYff43aMv5t+4E/C7Ol0H+/HnMqoirYfDOe 9aRtnMVFBdnuj2WHycxD/84AWxxDV4q4cq8/E= MIME-Version: 1.0 Received: by 10.239.184.80 with SMTP id x16mr60893hbg.88.1272058429736; Fri, 23 Apr 2010 14:33:49 -0700 (PDT) Received: by 10.239.131.208 with HTTP; Fri, 23 Apr 2010 14:33:49 -0700 (PDT) In-Reply-To: <1272014688.23661.2543.camel@happy.home.mcmillan.net.nz> References: <1272014688.23661.2543.camel@happy.home.mcmillan.net.nz> Date: Fri, 23 Apr 2010 22:33:49 +0100 Message-ID: Subject: Re: pcntl_fork() and database concurrency From: Andre Lopes To: Andrew McMillan Cc: pgsql-php@postgresql.org Content-Type: multipart/alternative; boundary=0016364d2d2dd687a50484ee2e77 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/6 X-Sequence-Number: 4135 --0016364d2d2dd687a50484ee2e77 Content-Type: text/plain; charset=ISO-8859-1 Hi, Thanks for the reply. I forgot to said that I will send e-mails in this process, so I need to interact with the Database and with the PHP. This approach is valid for this kind of problem? I have seen some procedures in SQL Server with row locks(I think this is the term) to the tables in Selects and Updates. PostgreSQL have this mechanisms? Best Regards, On Fri, Apr 23, 2010 at 10:24 AM, Andrew McMillan wrote: > On Fri, 2010-04-23 at 10:09 +0100, Andre Lopes wrote: > > Hi, > > > > I need to write a PHP Script to use with a Crontab. That Crontab will > > run every 10 minutes. > > > > I should use pcntl_fork() to prevent concurrency in database queries, > > but I don't have sure how to use this PHP function. > > > > The reason for use this function is to prevent that if the Crontab > > don't do the Job in 10 minutes, the next Cronjob will not concur with > > the job in the background that is running. > > > > My question. There are PostgreSQL examples on how to use this function > > to prevent database concurrency? > > Hi Andre, > > A better approach would be to maintain a lock row in a database table, > and let the database control whether another instance should be allowed > to run. > > Imagine a state like: > > CREATE TABLE concurrency_control ( > application TEXT PRIMARY KEY, > i_started TIMESTAMP, > my_pid INT > ); > > INSERT INTO concurrency_control VALUES( 'myapp' ); > > Something like; > > // Try and own the application processing record > UPDATE concurrency_control > SET i_started = current_timestamp, > my_pid = $$ > WHERE application = 'myapp' > AND (i_started IS NULL > OR i_started < (current_timestamp - '2 hours'::interval) > > // Check that we owned the application processing record > SELECT * FROM concurrency_control > WHERE application = 'myapp' and my_pid = $$ > > ... if we don't get a row, then we quit ... > > // > // All the processing goes in here. > // > > > // Relinquish the application processing record > UPDATE concurrency_control SET i_started = NULL, my_pid = NULL > WHERE application = 'my_app' AND my_pid = $$ > > // Optionally, for extra credit, clean up the dead rows :-) > VACUUM concurrency_control; > > > This approach has the benefit of just using standard database ACID > compliance to achieve the goal. If there is a race in the first UPDATE, > once must win, and one must not, and only the winner will continue after > the second statement. > > It also means that by setting the '2 hours' to something else, you have > an easy lock expiry mechanism. > > Cheers, > Andrew. > > ------------------------------------------------------------------------ > andrew (AT) morphoss (DOT) com +64(272)DEBIAN > You are not dead yet. But watch for further reports. > ------------------------------------------------------------------------ > > --0016364d2d2dd687a50484ee2e77 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi,

Thanks for the reply.

I forgot to said that I will send e= -mails in this process, so I need to interact with the Database and with th= e PHP.

This approach is valid for this kind of problem?


I have seen some procedures in SQL Server with row locks(I think this is th= e term) to the tables in Selects and Updates. PostgreSQL have this mechanis= ms?

Best Regards,


On Fri, Apr = 23, 2010 at 10:24 AM, Andrew McMillan <andrew@morphoss.com> wrote:
<= div class=3D"h5">On Fri, 2010-04-23 at 10:09 +0100, Andre Lopes wrote:
> Hi,
>
> I need to write a PHP Script to use with a Crontab. That Crontab will<= br> > run every 10 minutes.
>
> I should use pcntl_fork() to prevent concurrency in database queries,<= br> > but I don't have sure how to use this PHP function.
>
> The reason for use this function is to prevent that if the Crontab
> don't do the Job in 10 minutes, the next Cronjob will not concur w= ith
> the job in the background that is running.
>
> My question. There are PostgreSQL examples on how to use this function=
> to prevent database concurrency?

Hi Andre,

A better approach would be to maintain a lock row in a database table,
and let the database control whether another instance should be allowed
to run.

Imagine a state like:

CREATE TABLE concurrency_control (
=A0application TEXT PRIMARY KEY,
=A0i_started TIMESTAMP,
=A0my_pid INT
);

INSERT INTO concurrency_control VALUES( 'myapp' );

Something like;

// Try and own the application processing record
UPDATE concurrency_control
=A0 SET i_started =3D current_timestamp,
=A0 =A0 =A0 my_pid =3D $$
=A0WHERE application =3D 'myapp'
=A0 =A0AND (i_started IS NULL
=A0 =A0 =A0 =A0 OR i_started < (current_timestamp - '2 hours'::= interval)

// Check that we owned the application processing record
SELECT * FROM concurrency_control
=A0 =A0 WHERE application =3D 'myapp' and my_pid =3D $$

... if we don't get a row, then we quit ...

//
// All the processing goes in here.
//


// Relinquish the application processing record
UPDATE concurrency_control SET i_started =3D NULL, my_pid =3D NULL
=A0WHERE application =3D 'my_app' AND my_pid =3D $$

// Optionally, for extra credit, clean up the dead rows :-)
VACUUM concurrency_control;


This approach has the benefit of just using standard database ACID
compliance to achieve the goal. =A0If there is a race in the first UPDATE,<= br> once must win, and one must not, and only the winner will continue after the second statement.

It also means that by setting the '2 hours' to something else, you = have
an easy lock expiry mechanism.

Cheers,
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0Andrew.

------------------------------------------------------------------------ andrew (AT) morphoss (DOT) com =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0+64(272)DEBIAN
=A0 =A0 =A0 =A0 You are not dead yet. =A0But watch for further reports. ------------------------------------------------------------------------

--0016364d2d2dd687a50484ee2e77--