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