Re: Locking Question (nowait, UPDATE etc)

From: <ctcgag_at_hotmail.com>
Date: 28 Mar 2003 19:54:48 GMT
Message-ID: <20030328145448.490$ZP@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?

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.

> >
> > 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.

> > > For performance reasons, we
> > > can NOT do another select-for-update-nowait prior to running the
> > > actual UPDATE or DELETE, this would just be way too slow.
> >
> > Have you tried it? What would it slow down that the other select for
> > update isn't already slowing down? How long would you expect it to
> > hold the locks for?

>

> Yes. The code that just wants to do a quick update would be nearly twice
> as slow if it first did a select for update.

You should avoid doing the expensive where clause twice but using a rowid or primary key.

> Also, we'd have to retrofit
> all existing code that does updates, not just the one long running
> process.

If there was a nowait for updates, you'd have to retrofit to add that. Admittedly, it's an easier retrofit to type in, but it would still have to be done.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB

Received on Fri Mar 28 2003 - 13:54:48 CST