pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pcntl_fork() and database concurrency
4+ messages / 2 participants
[nested] [flat]

* pcntl_fork() and database concurrency
@ 2010-04-23 09:09 Andre Lopes <lopes80andre@gmail.com>
  2010-04-23 09:24 ` Re: pcntl_fork() and database concurrency Andrew McMillan <andrew@morphoss.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Andre Lopes @ 2010-04-23 09:09 UTC (permalink / raw)
  To: pgsql-php

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?

Best Regards,

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pcntl_fork() and database concurrency
  2010-04-23 09:09 pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
@ 2010-04-23 09:24 ` Andrew McMillan <andrew@morphoss.com>
  2010-04-23 21:33   ` Re: pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Andrew McMillan @ 2010-04-23 09:24 UTC (permalink / raw)
  To: Andre Lopes <lopes80andre@gmail.com>; +Cc: pgsql-php

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.
------------------------------------------------------------------------

Attachments:

  signature.asc (application/pgp-signature, 197B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pcntl_fork() and database concurrency
  2010-04-23 09:09 pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
  2010-04-23 09:24 ` Re: pcntl_fork() and database concurrency Andrew McMillan <andrew@morphoss.com>
@ 2010-04-23 21:33   ` Andre Lopes <lopes80andre@gmail.com>
  2010-04-23 22:27     ` Re: pcntl_fork() and database concurrency Andrew McMillan <andrew@morphoss.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Andre Lopes @ 2010-04-23 21:33 UTC (permalink / raw)
  To: Andrew McMillan <andrew@morphoss.com>; +Cc: pgsql-php

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 <andrew@morphoss.com>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.
> ------------------------------------------------------------------------
>
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pcntl_fork() and database concurrency
  2010-04-23 09:09 pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
  2010-04-23 09:24 ` Re: pcntl_fork() and database concurrency Andrew McMillan <andrew@morphoss.com>
  2010-04-23 21:33   ` Re: pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
@ 2010-04-23 22:27     ` Andrew McMillan <andrew@morphoss.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Andrew McMillan @ 2010-04-23 22:27 UTC (permalink / raw)
  To: Andre Lopes <lopes80andre@gmail.com>; +Cc: pgsql-php

On Fri, 2010-04-23 at 22:33 +0100, Andre Lopes wrote:
> 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?

Sure.  You're just storing a row in the database, checking whether you
successfully did so, and quitting if you didn't.  It should be valid for
any problem where you don't want two processes to be happening
concurrently.

Equally you could run your PHP program from a shell script and use some
lockfile program to do a similar thing, but I actually think that the
SQL solution is cleaner, and I trust the ACID capabilities of the
database much more than those of the filesystem.


> 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?

You could use row locking, but I don't think it gives as much potential
for discovering what/why something is locked, and personally I would
consider it likely to be less imediately understood by a programmer
returning to the code in a couple of years.  Whereas anyone who does SQL
knows how UPDATE & SELECT work, they probably have to refer to the
manual to work out exactly what LOCK does.

Cheers,
					Andrew.

> 
> Best Regards,
> 
> 
> On Fri, Apr 23, 2010 at 10:24 AM, Andrew McMillan
> <andrew@morphoss.com> 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.
>         ------------------------------------------------------------------------
>         
> 

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
 Q:        How much does it cost to ride the Unibus?
A:        2 bits.
------------------------------------------------------------------------

Attachments:

  signature.asc (application/pgp-signature, 197B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2010-04-23 22:27 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-23 09:09 pcntl_fork() and database concurrency Andre Lopes <lopes80andre@gmail.com>
2010-04-23 09:24 ` Andrew McMillan <andrew@morphoss.com>
2010-04-23 21:33   ` Andre Lopes <lopes80andre@gmail.com>
2010-04-23 22:27     ` Andrew McMillan <andrew@morphoss.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox