Re: User Level Lock question - Mailing list pgsql-hackers

The application actually does not want nor need a consistent view of the
data. It is expecting that records that are locked will not be viewed at
all. The locks are normally held for VERY short periods of time. The fact
that the application is expecting locked records not to be viewed is causing
me problems because under PostgreSQL this is not easy to do. Even if I lock
a record using "SELECT ... FOR UPDATE", I can still do a SELECT and read it.
I need to effectively do a "SELECT ... FOR UPDATE" and make the other
reading clients skip that record completely.

I can do this with a flag column, but this requires the disk access to do
the UPDATE and if the client/backend quits/crashes with outstanding records
marked, they are locked.

The User Level Locks look like a great way to do this as I can set a lock
very quickly without disk access and if the client/backend quits/crashes,
the locks are automatically removed.

I can set the User Level Lock on a record using the supplied routines in the
contrib directory when I do a SELECT, and can reset the lock by doing an
UPDATE or SELECT as well.
But without the ability to test for an existing lock (without ever setting
it) I cannot skip the locked records.

I would set up all the SELECTs in thunking layer (I cannot rewrite the
application, only replace the ISAM library with a thunking library that
converts the ISAM calls to PostgreSQL calls) to look like the following:

SELECT col1, col2, col3
FROM table
WHERE   col1 = 'whatever' AND   col2 = 'whatever' AND   user_lock_test(oid) = 0;

user_lock_test() would return 0 if there is no current lock, and 1 if there
is.

Does this clear it up a little more or make it more complicated. The big
problem is the way that the ISAM code acts compared to a REAL RDBMS. If this
application was coded with a RDBMS in mind, things would be much easier.

Lance

----- Original Message -----
From: "Neil Conway" <nconway@klamath.dyndns.org>
To: "Lance Ellinghaus" <lellinghaus@yahoo.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Friday, March 15, 2002 4:24 PM
Subject: Re: [HACKERS] User Level Lock question


> On Fri, 2002-03-15 at 14:54, Lance Ellinghaus wrote:
> > I know it does not sound like something that would need to be done, but
here
> > is why I am looking at doing this...
> >
> > I am trying to replace a low level ISAM database with PostgreSQL. The
low
> > level ISAM db allows locking a record during a read to allow Exclusive
> > access to the record for that process. If someone tries to do a READ
> > operation on that record, it is skipped.
>
> If the locked record is skipped, how can the application be sure it is
> getting a consistent view of the data?
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC