Re: Locking Question (nowait, UPDATE etc)

"Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> "Galen Boyer" <galenboyer_at_hotpop.com> wrote in message
> news:uwuiokkg6.fsf_at_hotpop.com...
> > On Mon, 24 Mar 2003, jeggers_at_tetrix.com wrote:
> > > 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.
> >
> > Why can one process be performant while doing the "select for update"
> > yet another process is non-performant while doing the "select for
> > update"?
>
> The process that does SELECT FOR UPDATE is doing the 'FOR UPDATE' only
> for the sake of locking, it really wants to do just a select.

Then why is it doing the 'for update'? If it wants to do just a select, then do just a select.

> Other
> processes want to do just UPDATE or DELETE.

Can you provice an overview of what you are trying to accomplish (rather than the details of how you think you should accomplish it)?

> As it turns out, the SELECT FOR UPDATE lock is pretty darn useless since
> it doesn't have transactional scope: The lock is lost when the stored
> proc that places the lock is complete even though the transaction isn't.

I've just done a little test for which this isn't the case.

> We are considering having a supervisory database session monitor the
> other sessions to see if they are waiting on a lock (by monitoring
> v$session every couple of seconds) and if a session is blocked on a lock
> we just have the supervisor session kill the session that is blocked (not
> the one that is blocking) using ALTER SYSTEM...

What's going to happen when this process get's killed? Is it just going to retry the same thing, reblocking? This sounds like an aweful approach.

> I'd be interested to hear from the gurus if that is a viable approach,
> and any considerations why this might not be such a good idea.

It sounds like an aweful idea to me.

Xho

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