Re: Locking Question (nowait, UPDATE etc)

"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:
> > I hope this question makes sense:
> >
> > We have some code (in stored procedures) that selects data FOR UPDATE
> > NOWAIT, and it behaves as excepted.
>
> [...]
>
> > 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. Other processes want to do just UPDATE or DELETE.

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.

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

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.

Thanks,

 JEE Received on Tue Mar 25 2003 - 17:21:45 CST