Re: Using PGSQL to help coordinate many servers - Mailing list pgsql-general

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.

HTH,
Csaba.


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 same
> database. Basically, we are thinking about using a table row as the
> coordination mechanism. When an app-server needs to perform an action that
> 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 do
> 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.
> http://join.msn.com/?page=features/junkmail
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>