Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_lock.allocate_unique and autonomous transactions (long)

Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 01 Nov 2006 11:10:49 -0800
Message-ID: <1162408247.304337@bubbleator.drizzle.com>


hasta_l3_at_hotmail.com wrote:
> DA Morgan wrote :
>

>> hasta_l3_at_hotmail.com wrote:
>>> vc wrote :
>>>
>>>> hasta_l3_at_hotmail.com wrote:
>>>> [...]
>>>>> More concretely, the whole list structure is being changed,
>>>>> and the change should be atomic with regard to other
>>>>> operations that may also change the structure. However,
>>>>> the short transactions - which dont care about the structure -
>>>>> should not block.
>>>> So only the transactions modifying the entire list would select for
>>>> update the list header  and therefore be executed serially,  the
>>>> smaller transactions that do not care about the list structure would
>>>> proceed in the usual way without locking the header first.  What is the
>>>> problem ?
>>> 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.
>> Look up PRAGMA AUTONOMOUS_TRANSACTION. It may help.

>
> Yes, Daniel. Autonomous transactions were actually the
> main question of my initial (very long :-) post.
>
> But here you do suggest to (1) have the main transaction
> lock the list header row, and (2) modify and commit each
> list line row in its own autonomous transaction, right ?
>
> It will work, I think, but I dont see how it is much different
> from my proposed scheme to (1) lock the list header row with
> a dbms_lock, and (2) modify and commit each list line
> row in the main transaction(s).
>
> One scheme may be better than the other, but I really cannot
> see which or why.
>
> Incidentally, reifying the list/line relationship into its own table
> might perhaps be an appealling design. The short transactions
> would modify the list line rows, and the long restructuring
> transaction would modify the relationship table rows.
>
> Unfortunatly, I cannot contemplate to make *that* kind of
> change right now...

The difference is that you can commit in the autonomous transactions and not commit the main transaction. Something that is different from what you get using DBMS_LOCK.

-- 
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 Wed Nov 01 2006 - 13:10:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US