Re: Update a ROW while maintaining exclusive LOCK ?
From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Tue, 24 Jun 2003 03:19:03 GMT
Message-ID: <HqPJa.4035$Bg.2544_at_rwcrnsc54>
Date: Tue, 24 Jun 2003 03:19:03 GMT
Message-ID: <HqPJa.4035$Bg.2544_at_rwcrnsc54>
Then use dbms_lock to lock and unlock the row. Other processes will have to
query dbms_lock to see if they can get the lock, but only one process can
get the lock.
Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "Chris" <ctaliercio_at_yahoo.com> wrote in message news:8cc07162.0306231158.7ca53853_at_posting.google.com...Received on Tue Jun 24 2003 - 05:19:03 CEST
> 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