Re: Locking Question (nowait, UPDATE etc)

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 25 Mar 2003 20:50:10 -0600
Message-ID: <uk7embrbe.fsf@hotpop.com>

On Tue, 25 Mar 2003, 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:
>> > 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.

You didn't quite answer my question, but, it now seems we might have happened on some more telling information.

The process that "select for update" is doing this so no other process can modify or update anything within the data set which that particular select would retrieve? ie, are you trying to make sure no phantom reads nor non-repeatable reads happen? If this is so, I'm assuming you reread the "select for update" dataset more than once during the same logical session?

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

This just sounds completely wrong. Lets find out what are your needs. I bet there is a very solid solution utilizing Oracle.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Tue Mar 25 2003 - 20:50:10 CST