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

Re: Using PGSQL to help coordinate many servers

From: "Alvin Hung" <alvin_hung(at)hotmail(dot)com>
To: nagy(at)ecircle-ag(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using PGSQL to help coordinate many servers
Date: 2003-02-26 11:50:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Hi Csaba,

Thanks for the info.

Can you tell me what happens if a process holding a lock crashed?  Normally, 
it seems the db would detect it and release all locks immediately.  Is this 
reliable?  Or, what happens if the process just hangs instead of crashing?

I tried using statement_timeout but this clears the waiting process instead 
of the locking one....

>From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
>To: Alvin Hung <alvin_hung(at)hotmail(dot)com>
>CC: pgsql-general(at)postgresql(dot)org
>Subject: Re: [GENERAL] Using PGSQL to help coordinate many servers
>Date: 26 Feb 2003 12:25:20 +0100
>MIME-Version: 1.0
>Received: from ([]) by 
> with Microsoft SMTPSVC(5.0.2195.5600); Wed, 26 Feb 
>2003 03:25:08 -0800
>Received: from [] ([])by 
>(8.11.3/8.11.3/SuSE Linux 8.11.1-0.5) with ESMTP id h1QBP7214228;Wed, 26 
>Feb 2003 12:25:07 +0100
>X-Message-Info: dHZMQeBBv44lPE7o4B5bAg==
>In-Reply-To: <F1081goaKX0B5qpwpK800008b0b(at)hotmail(dot)com>
>References: <F1081goaKX0B5qpwpK800008b0b(at)hotmail(dot)com>
>X-Mailer: Ximian Evolution 1.0.8 (1.0.8-10) Message-Id: 
>Return-Path: nagy(at)ecircle-ag(dot)com
>X-OriginalArrivalTime: 26 Feb 2003 11:25:08.0822 (UTC) 
>I have done something similar, but a bit more complex.
>Th table structure:
>CREATE SEQUENCE seq_lockid;
>CREATE TABLE resource_lock (
>     lockid                     BIGINT
>                                CONSTRAINT pk_resource_lock PRIMARY KEY,
>     resource_type              BIGINT,
>     resource_instance          VARCHAR(50),
>     lock_type                  SMALLINT,
>     CONSTRAINT uni_resource_lock_instance UNIQUE ( resource_type,
>resource_instance )
>CREATE TABLE lock_info (
>     lockid                     BIGINT,
>     owner                      VARCHAR(300),
>     last_accessed              TIMESTAMP(0) WITH TIME ZONE
>                                DEFAULT CURRENT_TIMESTAMP(0)
>                                NOT NULL,
>     CONSTRAINT pk_lock_info PRIMARY KEY ( lockid, owner ),
>     CONSTRAINT fk_lock_info_ref_resource FOREIGN KEY ( lockid )
>                REFERENCES resource_lock ( lockid ) ON DELETE CASCADE
>The application can place different types of locks (lock_type), i.e.
>shared, exclusive, or whatever you come up with. The lock types are
>handled by your locking code.
>Also you can have different resource types (resource_type) to lock (this
>is basically the activity which is locked).
>For a specific resource type you can have different resource instances
>to lock (this is analogous to the table/row locking concept:
>table->resource type, row->resource instance).
>The lock info table is needed to record who locked the resource and
>when. Note that for shared locking there can be more than 1 lock info
>record for a specific lockid, this is why the separate table is needed.
>The locking code is relatively simple:
>  - put a DB lock on the lockid you want to handle (you have to handle
>the problems of creating the record if it's not there/locking it if it's
>there... this can be tricky);
>  - check the lock type to see if it is free to place the new lock;
>  - fail if the record is locked OR update the lock info and lock type to
>reflect the placed lock;
>  - release the DB lock;
>The DB lock is kept for relatively short time, hence low contention, but
>the logical lock you just placed is there as long as you want to have
>it... this makes the procedure relatively contention free.
>The problems you can have with this approach is that the application
>must correctly check the lock, and there's no means to assure this other
>than good app design... also bugs in the locking code are hard to find
>and debug.
>In our application it works quite well. Note that we are using it for
>high level activity coordination, so it is not hitting too much the DB.
>On Wed, 2003-02-26 at 11:45, Alvin Hung wrote:
> >
> > Hi,
> >
> > We have a need to coordinate multiple app-servers that are using the 
> > database. Basically, we are thinking about using a table row as the
> > coordination mechanism. When an app-server needs to perform an action 
> > must be exclusive, it will try to lock that table row first. It will 
>wait on
> > that lock if someone else has it.
> >
> > Has anyone done something like this before? What's the performance and
> > resource usage like if the exclusive action is pretty short?  Does PGSQL 
> > FIFO when multiple connections wait on the same lock?
> >
> > BTW we are using JDBC to connect to the server.
> >
> > Thanks for your help.
> >
> >
> >
> >
> > _________________________________________________________________
> > Tired of spam? Get advanced junk mail protection with MSN 8.
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >
> >

MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.


pgsql-general by date

Next:From: Csaba NagyDate: 2003-02-26 12:07:49
Subject: Re: Using PGSQL to help coordinate many servers
Previous:From: Csaba NagyDate: 2003-02-26 11:25:20
Subject: Re: Using PGSQL to help coordinate many servers

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