please?

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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:

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

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, Pennsylvania 19026

Bruce Momjian <maillist@candle.pha.pa.us>

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?

This all sounds like the Oracle NOWAIT option.

Session1.

SQL> select value from sys_param where name = 'ASG_SYSTEM_DESC' for update;

VALUE
------------------------------
tpwmamda

SQL>

Session2.

SQL> select value from sys_param where name = 'ASG_SYSTEM_DESC' for update nowait;
ERROR:
ORA-00054: resource busy and acquire with NOWAIT specified

no rows selected

SQL>

No idea how to impliment it though!!

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.

Assuming you can set a reasonable "too old" time.

-- 
  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, Pennsylvania 19026

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.

Assuming you can set a reasonable "too old" time.

There may be many partial workarounds, depending on the
application, but there seems to be no robust way to have
a failed lock right now. Perhaps in a future version will
PQrequestCancel be able to terminate a waiting-for-lock
state?

Pablo

Pablo Funes <pablo@cs.brandeis.edu> writes:

Perhaps in a future version will PQrequestCancel be able to terminate
a waiting-for-lock state?

Seems like a reasonable suggestion. It's too late to consider this for
6.5 (we were supposed to freeze the feature list quite a while back)
but I support putting it on the TODO list for a future release.

regards, tom lane

Pablo Funes <pablo@cs.brandeis.edu> writes:

Perhaps in a future version will PQrequestCancel be able to terminate
a waiting-for-lock state?

Seems like a reasonable suggestion. It's too late to consider this for
6.5 (we were supposed to freeze the feature list quite a while back)
but I support putting it on the TODO list for a future release.

regards, tom lane

Added:

* Allow PQrequestCancel() to terminate when in waiting-for-lock state

-- 
  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, Pennsylvania 19026

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

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.

Added to TODO:

* PQrequestCancel() be able to terminate backend waiting for lock

Pablo Funes <pablo@cs.brandeis.edu> writes:

Perhaps in a future version will PQrequestCancel be able to terminate
a waiting-for-lock state?

Seems like a reasonable suggestion. It's too late to consider this for
6.5 (we were supposed to freeze the feature list quite a while back)
but I support putting it on the TODO list for a future release.

regards, tom lane

-- 
  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, Pennsylvania 19026