Thread: User Level Lock question
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
"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
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
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
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
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
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
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 WHEREcol1 = 'whatever'ANDcol2 = 'whatever'ANDvisible = '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
----- 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 >