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