Re: Locking

On Sat, Aug 12, 2000 at 11:24:31PM -0400, Tom Lane wrote:
> Ian West <ian(at)niw(dot)com(dot)au> writes:
> > Is there any way to request postgres to immediately fail, or better yet
> > fail after some small time when trying to obtain a conflicting lock ?
>
> Deadlocks should be detected within about a second. If you have an
> example where one is not, let's see it...
>
> regards, tom lane

I don't think my problem is a deadlock, and it is almost certainly
a limitation of my code.

The problem arises where one does a 'select [info] from [table] for
update' by one user, which locks the row just fine. The user then
proceeds to edit the info, and in some (bad) cases go to lunch. Another
user then decides to update the same [info] record in the same table.
They hang in the 'select for update' bit forever. (Or at least days) If
I terminate the original user process, they proceed fine, which is as it
should be.

With the old Informix libs, in the same situation I would get an
immediate error on the second client session (record in use by another
user) and it would identify the user who had the record locked, which
was enormously handy. (In sample above where 'user' had literally gone
out to lunch half way through an update.)

I understand that I can use non-blocking io, and poll for data, and send
an abort after a delay if I don't get my lock within a reasonable time,
but this doesn't help with ecpg as far as I can tell. (Although I may
very well be missing the obvious here :-)

The question I think is more can I set the default action on requiring a
lock on a record which is in use to be fail, rather than wait. Or can I
specify how long to wait.

Thanks,
Ian West