Thread: please?
Please take a sec to read this question. I've posted
it several times but got no comments at all. Thanx, Pablo.
---
Forwarded message:
From pablo Thu May 27 18:42:11 1999
Subject: nonblocking lock?
To: pgsql-hackers@postgresql.org
Date: Thu, 27 May 1999 18:42:11 -0400 (EDT)
Content-Type: text
Content-Length: 890
Is it possible to do a nonblocking lock? That is,
I want several clients to execute,
begin
if table A is locked
then
go around doing stuff on other tables
else
lock A and do stuff on A that takes a long time
endif
the problem is, if I use normal lock, then
after one client has locked and is doing stuff on A
the other one will block and thus it won't be able
to go around doing stuff on other tables. Is it
possible to do a nonblocking lock that will just
fail if the table is locked already?
NOTE: I tried using PQrequestCancel but it won't
cancel the request. It still blocks for as long
as the lock lasts. The only way around I've found so
far is to use PQreset. That's crude but works. But
it leaves a dangling postmaster process that lives
until the orignal lock is freed. Any other ideas?
Thanks a lot
Pablo Funes
Brandeis University
pablo@cs.brandeis.edu
Pablo Funes <pablo@cs.brandeis.edu> writes:
> Is it possible to do a nonblocking lock?
There is no way to do that in 6.4. I am not sure whether the MVCC
additions in 6.5 provide a way to do it or not (Vadim?).
> NOTE: I tried using PQrequestCancel but it won't
> cancel the request. It still blocks for as long
> as the lock lasts. The only way around I've found so
> far is to use PQreset. That's crude but works.
Not really --- what PQreset is really doing is disconnecting your
client from its original backend and starting a new backend. The
old backend is still there trying to get the lock; it won't notice
that you've disconnected from it until after it acquires the lock.
Obviously, this approach doesn't scale up very well... you'll soon
run out of backend processes.
A possible approach is for your clients to maintain more than one
backend connection, and use one of the backends to do the stuff
that might block while using another one to do the stuff that won't.
This would take a little more bookkeeping in the client but it seems
like a logically cleaner way to think about it.
regards, tom lane
> A possible approach is for your clients to maintain more than one
> backend connection, and use one of the backends to do the stuff
> that might block while using another one to do the stuff that won't.
> This would take a little more bookkeeping in the client but it seems
> like a logically cleaner way to think about it.
Or you could do it outside of the database using a Unix filesystem lock
file. There are symantics for no-blocking lock stuff in flock():
#define LOCK_SH 0x01 /* shared file lock */ #define LOCK_EX 0x02 /* exclusive file lock */
#define LOCK_NB 0x04 /* don't block when locking */ #define LOCK_UN 0x08 /* unlock file */
I don't know of any SQL databases that allow non-blocking lock requests.
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian wrote: > > I don't know of any SQL databases that allow non-blocking lock requests. > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for me. -------- Regards Theo
First, thanks all for the feedback and good luck with the new release! > > > A possible approach is for your clients to maintain more than one > > backend connection, and use one of the backends to do the stuff > > that might block while using another one to do the stuff that won't. Yes. Same effect as PQreset() if the code is to be ran only once, but a lot better if inside a loop!. > Or you could do it outside of the database using a Unix filesystem lock > file. There are symantics for no-blocking lock stuff in flock(): > > #define LOCK_SH 0x01 /* shared file lock */ > #define LOCK_EX 0x02 /* exclusive file lock */ > #define LOCK_NB 0x04 /* don't block when locking */ > #define LOCK_UN 0x08 /* unlock file */ Exactly what's wanted in this case. The unix flock() locks a file or, if already locked, either waits or fails depending on what you requested. The lock is released by either an unlock operation or the death of the locking process. It would solve my problem, except it requires all clients to share a filesystem. > I don't know of any SQL databases that allow non-blocking lock requests. I'm not very familiar with full-scale SQL but seems odd not to have such things. I guess from the language point of view there ought to be a way to know when an item is unavailable/undefined (it's been locked for writing), if you don't want to wait a long time to get a value. Imagine I go to the store at 11am and can't buy soap - because the price of soap is unknown because it's a heavy trading day for soap at the ny stock exchange. Even if the shop's definition may not allow for soap to be sold before the stock market closes and the final price is known, I shouldn't be forced to wait there doing nothing. I can do other shopping around and come back later for my soap! ;-) Regards, Pablo
> First, thanks all for the feedback and good luck with the new > release! > > > > > > A possible approach is for your clients to maintain more than one > > > backend connection, and use one of the backends to do the stuff > > > that might block while using another one to do the stuff that won't. > > Yes. Same effect as PQreset() if the code is to be ran only once, but > a lot better if inside a loop!. > > > Or you could do it outside of the database using a Unix filesystem lock > > file. There are symantics for no-blocking lock stuff in flock(): > > > > #define LOCK_SH 0x01 /* shared file lock */ > > #define LOCK_EX 0x02 /* exclusive file lock */ > > #define LOCK_NB 0x04 /* don't block when locking */ > > #define LOCK_UN 0x08 /* unlock file */ > > Exactly what's wanted in this case. The unix flock() locks a file or, > if already locked, either waits or fails depending on what you > requested. The lock is released by either an unlock operation or the > death of the locking process. It would solve my problem, except it > requires all clients to share a filesystem. Sharing file systems. Good point. You could have a table you use to lock. Lock the table, view the value, possibly modify, and unlock. This does not handle the case where someone died and did not remove their entry from the lock table. > > > I don't know of any SQL databases that allow non-blocking lock requests. > > I'm not very familiar with full-scale SQL but seems odd not to have > such things. I guess from the language point of view there ought to be > a way to know when an item is unavailable/undefined (it's been locked > for writing), if you don't want to wait a long time to get a value. > > Imagine I go to the store at 11am and can't buy soap - because > the price of soap is unknown because it's a heavy trading day for soap > at the ny stock exchange. Even if the shop's definition may not allow for > soap to be sold before the stock market closes and the final price is > known, I shouldn't be forced to wait there doing nothing. I can do > other shopping around and come back later for my soap! ;-) Yes, I can see why having such a facility would be nice. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Bruce Momjian wrote: > > > > I don't know of any SQL databases that allow non-blocking lock requests. > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > me. > -------- Please give me more information. How does dirty read fix the problem? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > Bruce Momjian wrote: > > > > > > I don't know of any SQL databases that allow non-blocking lock requests. > > > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > me. > > -------- > > Please give me more information. How does dirty read fix the problem? It allows me to read uncommited records without blocking. -------- Regards Theo
> > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > me. > > > -------- > > > > Please give me more information. How does dirty read fix the problem? > > It allows me to read uncommited records without blocking. I suppose it somehow lets you know whether the read was dirty or clean...
> Bruce Momjian wrote: > > > > > Bruce Momjian wrote: > > > > > > > > I don't know of any SQL databases that allow non-blocking lock requests. > > > > > > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > me. > > > -------- > > > > Please give me more information. How does dirty read fix the problem? > > It allows me to read uncommited records without blocking. Yes, but that does not solve his problem. He wants a single lock, and wants to test the lock, and immediately return if the lock fails. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > > me. > > > > -------- > > > > > > Please give me more information. How does dirty read fix the problem? > > > > It allows me to read uncommited records without blocking. > > I suppose it somehow lets you know whether the read was dirty or > clean... Not that I am aware of. Never heard that of Informix. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > It allows me to read uncommited records without blocking. > > Yes, but that does not solve his problem. He wants a single lock, and > wants to test the lock, and immediately return if the lock fails. > If you know the read was dirty, you know there was somebody else locking/writing the table or record, it's locked, you failed to lock. Of course you should be able to aquire the lock in the same atomic operation... ...Pablo
Bruce Momjian wrote: > > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > > > me. > > > > > -------- > > > > > > > > Please give me more information. How does dirty read fix the problem? > > > > > > It allows me to read uncommited records without blocking. > > > > I suppose it somehow lets you know whether the read was dirty or > > clean... > > Not that I am aware of. Never heard that of Informix. I also cheat. I use a 3 buffer approach, compare fields and see if a record has changed before I do an update. -------- Regards Theo
> Bruce Momjian wrote: > > > > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > > > > me. > > > > > > -------- > > > > > > > > > > Please give me more information. How does dirty read fix the problem? > > > > > > > > It allows me to read uncommited records without blocking. > > > > > > I suppose it somehow lets you know whether the read was dirty or > > > clean... > > > > Not that I am aware of. Never heard that of Informix. > > I also cheat. I use a 3 buffer approach, compare fields and see if a record > has > changed before I do an update. Oh, now we get the full picture. :-) -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Tom Lane wrote: > > Pablo Funes <pablo@cs.brandeis.edu> writes: > > Is it possible to do a nonblocking lock? > > There is no way to do that in 6.4. I am not sure whether the MVCC > additions in 6.5 provide a way to do it or not (Vadim?). I want to have it in later versions. At the moment try to use contrib/userlock/ > > > NOTE: I tried using PQrequestCancel but it won't > > cancel the request. It still blocks for as long And this is bug that should be fixed... after 6.5 Vadim
Theo Kramer wrote: > > Bruce Momjian wrote: > > > > > Bruce Momjian wrote: > > > > > > > > I don't know of any SQL databases that allow non-blocking lock requests. > > > > > > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for > > > me. > > > -------- > > > > Please give me more information. How does dirty read fix the problem? > > It allows me to read uncommited records without blocking. I plan to implement it in 6.6 Vadim
Bruce Momjian wrote: > Sharing file systems. Good point. You could have a table you use to > lock. Lock the table, view the value, possibly modify, and unlock. > This does not handle the case where someone died and did not remove > their entry from the lock table. You can always write the modification time to the table as well and if it's "too old", then try to override it. -------Hannu
Vadim Mikheev wrote: > > It allows me to read uncommited records without blocking. > > I plan to implement it in 6.6 That's the best thing I've heard so far. I will then be able to use postgres for my interactive applications :-). -------- Regards Theo
Theo Kramer wrote: > > Vadim Mikheev wrote: > > > It allows me to read uncommited records without blocking. > > > > I plan to implement it in 6.6 > > That's the best thing I've heard so far. I will then be able to use > postgres for my interactive applications :-). How about savepoints? -:) And implicit savepoint before executing a query, like one in Oracle? Vadim
Vadim Mikheev wrote: > > How about savepoints? -:) > And implicit savepoint before executing a query, like one in Oracle? That would be the cherry on the top. -------- Regards Theo
> > There is no way to do that in 6.4. I am not sure whether the MVCC > > additions in 6.5 provide a way to do it or not (Vadim?). > > I want to have it in later versions. > > At the moment try to use contrib/userlock/ > AHA! It looks like this solves my problem, at least for now, until an official way to do nonblocking locs shows up on a future release. Here's what contrib/userlock/user_locks.doc says: select some_fields, user_write_lock_oid(oid) from table where id='key'; Now if the returned user_write_lock_oid field is 1 you have acquired an user lock on the oid of the selected tuple and can now do some long operation on it, like let the data being edited by the user. If it is 0 it means that the lock has been already acquired by some other process and you should not use that item until the other has finished. [...] update table set some_fields where id='key'; select user_write_unlock_oid(oid) from table where id='key'; [...] This could also be done by setting a flag in the record itself but in this case you have the overhead of the updates to the records and there could be some locks not released if the backend or the application crashes before resetting the lock flag. It could also be done with a begin/end block but in this case the entire table would be locked by postgres and it is not acceptable to do this for a long period because other transactions would block completely.