Skip site navigation (1) Skip section navigation (2)

Re: pcntl_fork() and database concurrency

From: Andrew McMillan <andrew(at)morphoss(dot)com>
To: Andre Lopes <lopes80andre(at)gmail(dot)com>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: pcntl_fork() and database concurrency
Date: 2010-04-23 09:24:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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.


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

In response to


pgsql-php by date

Next:From: Giancarlo BoaronDate: 2010-04-23 16:24:57
Subject: Problems with pg_prepare
Previous:From: Andre LopesDate: 2010-04-23 09:09:57
Subject: pcntl_fork() and database concurrency

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group