Is there an easy way to test the lock on a user level lock without actually
issuing the lock?

I would like to use them, but there is only a LockAcquire() and
LockRelease().. There is no LockTest()..

I guess I could do:

IF LockAcquire() == 0:
    "locked" do whatever if it is locked...
ELSE:
    LockRelease()
    "unlocked" do whatever since it was not locked in the first place..

This just seems to be an inefficient way of doing this...

Thanks,
Lance Ellinghaus


Fri, 15 Mar 2002 00:43:07 -0600
"Lance Ellinghaus" <lellinghaus@yahoo.com>
User Level Lock question
"Lance Ellinghaus" <lellinghaus@yahoo.com> writes:
> Is there an easy way to test the lock on a user level lock without actually
> issuing the lock?

Why would you ever want to do such a thing?  If you "test" the lock but
don't actually acquire it, someone else might acquire the lock half a
microsecond after you look at it --- and then what does your test result
mean?  It's certainly unsafe to take any action based on assuming that
the lock is free.

I suspect what you really want is a conditional acquire, which you can
get (in recent versions) using the dontWait parameter to LockAcquire.

			regards, tom lane

Fri, 15 Mar 2002 10:11:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: User Level Lock question 
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. I have to duplicate this
functionality. The application also allows locking multiple records and then
unlocking individual records or unlocking all of them at once. This cannot
be done easily with PostgreSQL unless I add a "status" field to the records
and manage them. This can be done, but User Level Locks seem like a much
better solution as they provide faster locking, no writes to the database,
when the backend quits all locks are released automatically, and I could
lock multiple records and then clear them as needed. They also exist outside
of transactions!

So my idea was to use User Level Locks on records and then include a test on
the lock status in my SELECT statements to filter out any records that have
a User Level Lock on it. I don't need to set it during the query, just test
if there is a lock to remove them from the query. When I need to do a true
lock during the SELECT, I can do it with the supplied routines.

Does this make any more sense now or have I made it that much more
confusing?

Lance

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Lance Ellinghaus" <lellinghaus@yahoo.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Friday, March 15, 2002 9:11 AM
Subject: Re: [HACKERS] User Level Lock question


> "Lance Ellinghaus" <lellinghaus@yahoo.com> writes:
> > Is there an easy way to test the lock on a user level lock without
actually
> > issuing the lock?
>
> Why would you ever want to do such a thing?  If you "test" the lock but
> don't actually acquire it, someone else might acquire the lock half a
> microsecond after you look at it --- and then what does your test result
> mean?  It's certainly unsafe to take any action based on assuming that
> the lock is free.
>
> I suspect what you really want is a conditional acquire, which you can
> get (in recent versions) using the dontWait parameter to LockAcquire.
>
> regards, tom lane


Fri, 15 Mar 2002 13:54:30 -0600
"Lance Ellinghaus" <lellinghaus@yahoo.com>
Re: User Level Lock question 
Are you trying to do a select for update?

Greg


On Fri, 2002-03-15 at 13: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. I have to duplicate this
> functionality. The application also allows locking multiple records and then
> unlocking individual records or unlocking all of them at once. This cannot
> be done easily with PostgreSQL unless I add a "status" field to the records
> and manage them. This can be done, but User Level Locks seem like a much
> better solution as they provide faster locking, no writes to the database,
> when the backend quits all locks are released automatically, and I could
> lock multiple records and then clear them as needed. They also exist outside
> of transactions!
> 
> So my idea was to use User Level Locks on records and then include a test on
> the lock status in my SELECT statements to filter out any records that have
> a User Level Lock on it. I don't need to set it during the query, just test
> if there is a lock to remove them from the query. When I need to do a true
> lock during the SELECT, I can do it with the supplied routines.
> 
> Does this make any more sense now or have I made it that much more
> confusing?
> 
> Lance
> 
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Lance Ellinghaus" <lellinghaus@yahoo.com>
> Cc: <pgsql-hackers@postgresql.org>
> Sent: Friday, March 15, 2002 9:11 AM
> Subject: Re: [HACKERS] User Level Lock question
> 
> 
> > "Lance Ellinghaus" <lellinghaus@yahoo.com> writes:
> > > Is there an easy way to test the lock on a user level lock without
> actually
> > > issuing the lock?
> >
> > Why would you ever want to do such a thing?  If you "test" the lock but
> > don't actually acquire it, someone else might acquire the lock half a
> > microsecond after you look at it --- and then what does your test result
> > mean?  It's certainly unsafe to take any action based on assuming that
> > the lock is free.
> >
> > I suspect what you really want is a conditional acquire, which you can
> > get (in recent versions) using the dontWait parameter to LockAcquire.
> >
> > regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
15 Mar 2002 15:03:11 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: 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


15 Mar 2002 17:24:02 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: User Level Lock question
On Fri, 2002-03-15 at 16:24, Neil Conway wrote:
> 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
> 


Ya, that's what I'm trying to figure out.

It sounds like either he's doing what equates to a select for update or
more of less needs a visibility attribute for the row in question. 
Either way, perhaps he should share more information on what the end
goal is so we can better address any changes in idiom that better
reflect a relational database.

Greg
15 Mar 2002 16:41:32 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: User Level Lock question
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


Fri, 15 Mar 2002 21:45:19 -0600
"Lance Ellinghaus" <lellinghaus@yahoo.com>
Re: User Level Lock question
On Fri, 2002-03-15 at 21:45, Lance Ellinghaus wrote:
> 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

You keep asserting that these "viewed" records qualify as being called
locked.  It sounds like a record attribute to me.  Furthermore, it
sounds like that attribute reflects a record's visibility and not if
it's locked.  Locks are generally used to limit accessibility rather
than visibility.  This, I think, seems like the primary source of issue
you're having with your desired implementation.

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

That's what transactions are for.  If you have a failure, the
transaction should be rolled back.  The visibility marker would be
restored to it's original visible state.

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

But do you really need to lock it or hide it or both?  If both, you may
want to consider doing an update inside of a transaction or even a
select for update if it fits your needs.  Transactions are your friend. 
:)  I'm assuming you're needing to lock it because you are needing to
update the row at some point in time.  If you are not wanting to update
it, then you are really needing to hide it, not lock it.

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


SELECT col1, col2, col3
FROM table
WHERE
	col1 = 'whatever'
	AND
	col2 = 'whatever'
	AND
	visible = '1' ;


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

I understand that...and that can be hard...but sometimes semantics and
idioms have to be adjusted to allow for an ISAM to RDBMS migration.


Greg
15 Mar 2002 22:35:27 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: User Level Lock question

----- Original Message -----
From: "Lance Ellinghaus" <lellinghaus@yahoo.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, March 16, 2002 6:54 AM
Subject: Re: [HACKERS] User Level Lock question


> 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. I have to duplicate this
> functionality. The application also allows locking multiple records and
then
> unlocking individual records or unlocking all of them at once. This cannot
> be done easily with PostgreSQL unless I add a "status" field to the
records
> and manage them. This can be done, but User Level Locks seem like a much
> better solution as they provide faster locking, no writes to the database,
> when the backend quits all locks are released automatically, and I could
> lock multiple records and then clear them as needed. They also exist
outside
> of transactions!
>
> So my idea was to use User Level Locks on records and then include a test
on
> the lock status in my SELECT statements to filter out any records that
have
> a User Level Lock on it. I don't need to set it during the query, just
test
> if there is a lock to remove them from the query. When I need to do a true
> lock during the SELECT, I can do it with the supplied routines.
>
In INFORMIX you have a similar option except that you have the choice to
decide whether the other client blocks or continue, but in any case it
returns an error status. You even can set a delay while you accept to be
bloked and the lock can be set on database, table or record level. We use
table locking to speed up some time consuming processings.
I guess it would be better to have at least an error code returned. The
application can then choose to ignore the error code.

> Does this make any more sense now or have I made it that much more
> confusing?
>
> Lance
>
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Lance Ellinghaus" <lellinghaus@yahoo.com>
> Cc: <pgsql-hackers@postgresql.org>
> Sent: Friday, March 15, 2002 9:11 AM
> Subject: Re: [HACKERS] User Level Lock question
>
>
> > "Lance Ellinghaus" <lellinghaus@yahoo.com> writes:
> > > Is there an easy way to test the lock on a user level lock without
> actually
> > > issuing the lock?
> >
> > Why would you ever want to do such a thing?  If you "test" the lock but
> > don't actually acquire it, someone else might acquire the lock half a
> > microsecond after you look at it --- and then what does your test result
> > mean?  It's certainly unsafe to take any action based on assuming that
> > the lock is free.
> >
> > I suspect what you really want is a conditional acquire, which you can
> > get (in recent versions) using the dontWait parameter to LockAcquire.
> >
> > regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>



Mon, 18 Mar 2002 10:34:30 +1100
"Nicolas Bazin" <nbazin@ingenico.com.au>
Re: User Level Lock question