Re: Update a ROW while maintaining exclusive LOCK ?

andrewst <member14183_at_dbforums.com> wrote in message news:<3025981.1056126540_at_dbforums.com>...
> Originally posted by Chris
> > Hopefully someone might be able to educate me as to a way to
> > accomplish this:
> >
> > Suppose I have a program that uses this select statment to obtain a
> > lock on a row that it wishes to process.
> >
> > SELECT
> > KEYVALUE,
> > STATUS
> > FROM
> > MYTABLE
> > WHERE
> > KEYVALUE = :USERKEY
> > FOR UPDATE NOWAIT ;
> >
> > Now - at various stages of execution, my program needs to update the
> > status of this row so that progress can be monitored by other users.
> > How can I update the row, making the new status visible to all other
> > users, without losing my lock?
> >
> > The only way I know to do this would be:
> >
> > UPDATE
> > MYTABLE
> > SET
> > STATUS = :NEWSTATUS ;
> >
> > COMMIT ;
> >
> > And now repeat the SELECT ... FOR UPDATE NOWAIT statement.
> >
> > This is not good, because there is the possibility (be it however
> > slim) that someone else could lock the record between my UPDATE and
> > re-SELECT statements.
> >
> > Any ideas or suggestions would be much appreciated.
> >
> > Thanks,
> > Chris
> If you just want to monitor progress, you could use an autonomous
> transaction to log progress into another table. Or you could use
> DBMS_APPLICATION_INFO.
>
> It would be wrong (as well as impossible) for any other process to see
> an updated status on mytable before you commit, because that would
> violate integrity.

Andrew,

I appreciate your advice, but I have a couple of problems:

• Perhaps monitor was a bad choice of words. Maybe view would have been better. There are several concerned parties that need to be able to view the current status of the items in MYTABLE while this daemon process runs in the background. They do this by using a simply query screen that shows the items they are interested in, as well as their current status in MYTABLE.
• I understand that the status should not be updated until the commit - that's the behavior I want. The status is updated at the end of a logical transaction - problem is that there are multiple logical transactions within this process. What I am trying to accomplish is not to let people see the updated status prior to the commit; but rather to not lose my lock on the record when I issue the commit. It would be an extremely bad thing for someone else to obtain a lock on the record the daemon is working on in the middle of processing.

Hopefully that clarifies a bit what/why I am looking for.

Thanks again (and in advance) for the advice!

Chris Received on Mon Jun 23 2003 - 21:58:13 CEST