Thread: libpq program SELECT FOR UPDATE and TIMEOUT or CANCEL REQUEST

Hi,

I am programming using libpq under Linux.

When I do a "select ... for update" to lock a row, how 
would I allow user to cancel or timeout when requesting the
the same row from another program/transaction.

I do not want the user to have to wait for the update on the 
other end. I would rather send the user an error message
saying that the record is LOCKED -- try again later.

If you could send me an example that would be great!

Thanks for your help.

Jeff.

jpogo@imcg.com

>
> I am programming using libpq under Linux.
>
> When I do a "select ... for update" to lock a row, how=20
> would I allow user to cancel or timeout when requesting the
> the same row from another program/transaction.
>
> I do not want the user to have to wait for the update on the=20
> other end. I would rather send the user an error message
> saying that the record is LOCKED -- try again later.
>
> If you could send me an example that would be great!
>
There was a strongly related discussion on the pgsql-sql list in
February 2003. Search the archives for threads
"Lock timeout detection"
"Can pessimistic locking be emulated?"
If you're having trouble to find those, contact me again.
I recorded the major important stuff - at least I think.
Regards, Christoph

On Mon, May 19, 2003 at 10:55:23 -0700, Jeffrey Pogodzinski <jrpogo@yahoo.com> wrote:
> Hi,
> 
> I am programming using libpq under Linux.
> 
> When I do a "select ... for update" to lock a row, how 
> would I allow user to cancel or timeout when requesting the
> the same row from another program/transaction.

You can set statement_timeout.

> I do not want the user to have to wait for the update on the 
> other end. I would rather send the user an error message
> saying that the record is LOCKED -- try again later.

It is generally bad design to hold database locks while waiting for
user input. It is generally better to keep information about updates
in a table and have the applications use this information to advise
or prevent conncurrent updates to the same data by different people.

On Fri, May 23, 2003 at 13:03:52 -0700, Jeff Pogodzinski <jrpogo@yahoo.com> wrote:
> Hi Bruno, 
> 
> > It is generally bad design to hold database locks while waiting for
> > user input. It is generally better to keep information about updates
> > in a table and have the applications use this information to advise
> > or prevent conncurrent updates to the same data by different people.
> 
> How would I advise of prevent conncurrent updates without locks?

You use tables in your database that are checked by the application.
Typically you would store information about what data is being worked
on and who is working on it. That way you don't block the database waiting
for input from humans. If two people want to work on the same data at the
same time, the application decides how to handle that. You may want to
prevent it (with some way to override) or just issue a warning. You may
also want to issue a warning to the second person to commit their changes
warning them that the data has been changed since they started their work.