Re: Locking Question (nowait, UPDATE etc)

From: Johannes Eggers <jeggers_at_tetrix.com>
Date: Mon, 31 Mar 2003 08:51:51 -0600
Message-ID: <b69kk1$31lh8$1@ID-178358.news.dfncis.de>

<ctcgag_at_hotmail.com> wrote in message
news:20030328145448.490$ZP_at_newsreader.com...

> "Johannes Eggers" <jeggers_at_tetrix.com> wrote:

> > <ctcgag_at_hotmail.com> wrote in message
> > news:20030327145537.582$gY_at_newsreader.com...
> > > "Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> > > > I hope this question makes sense:
> > > >
> > > > We have some code (in stored procedures) that selects data FOR
UPDATE
> > > > NOWAIT, and it behaves as excepted.
> > >
> > > How is it expected to behave, and why does it do this?

> >

> > It's supposed to lock the records. We do it because we want the records
> > locked (so they're not modified while we process them in our client)

>
> Why do want them locked so they can't be modified while you process them
> in your client?

Correct.

>
> Presumably, the updates happening in your table are occuring because
> real-world facts (which the table models) have changed, and you need to
> reflect that.  If your client wants to ignore any changes occuring
> after a certain point, that's fine.  It is what Oracle's read consistency
> does. But if your client wants to forbid anyone else from acknowledging
> that the real world has changed, it sounds like your client is an ignorant
> bully.

Not really. It's acceptable, from a business process, to not permit updates while the other process is running.

> > > Then do the select for update no wait on this, first.

> >

> > Tried this, it seems the lock doesn't survive the stored procedure that
> > is putting the lock on it, amazingly enough. We can work-around that by
> > doing a simple UPDATE, then select the records.

>
> I did:
>
> create procedure llll (x in number)
> cursor main is select * from blah where col2=x for update;
> begin
>   open main;
> end;
>
> then did an exec llll(30)
> In a separate session, I said "update blah set col1='hi' where col2=30"

and
> it blocked until I typed commit in the first session.

I watched our DB guy do the same thing, and we didn't get your result (although we expected to). Received on Mon Mar 31 2003 - 08:51:51 CST