Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 04 Nov 2006 14:35:01 -0800
Message-ID: <1162679699.516600@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:

>> I am led, by what you've written, to question whether you understand
>> basic Oracle architecture and concepts. I still don't quite see this
>> as being anything more than overcomplicating something unnecessarily.
>>
>> Oracle is not SQL Server. Why is it that any locking is required?

>
> I never used SQL Server, Daniel.
>
> To recap the context :
>

>> Assume a (long) restructuring operation L modifies
>> (a) some fields of  the list header row in the master table, then
>> (b) some fields of every list line row in the children table, including
>> -say - row R.
>>
>> Assume that a short transaction S modifies
>> (a) some other fields of the list line row R.
>>
>> If L is in a single long transaction, then it will block S if S happens
>> to want to modify R after L did it.

>
> Right now, that problem is solved by breaking the long operation
> in many small transactions.
>
> I'm just enumerating ways to replace the burst of thousands of
> transactions by a single long one, if profiling shows it to be
> necessary.

I've read all this ... several times ... I still don't see the point?

Why does it have to be two processes?
So what if multiple processes modify a record?

Again ... I don't see the underlying business case for what you are doing. In what way will one transaction lock another unless you explicitly lock it and why would you do that unnecessarily?

Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have?

Again ... I've read what you've written but I still don't see a business case that indicates what you are doing must be done as you are doing it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Nov 04 2006 - 16:35:01 CST