Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 04 Nov 2006 09:56:15 -0800
Message-ID: <1162662973.407071@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:
> joel garry wrote :

>> hasta_l3_at_hotmail.com wrote:
>>> DA Morgan wrote :
>>>> 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.
>>>> --
>>> Yes. While the difference is unimportant (as of now) in my context,
>>> and using a lock minimizes the amount of changes, your suggestion
>>> as a "smell" of rigor and elegance that I like a lot...  I will very
>>> seriously consider it. Thanks.
>>>
>>> Any known caveat on a massive use of autonomous transactions ?
>>> (8.1.7 and later...)
>>>
>>> --- Raoul
>> You may have issues with latching and checkpointing.
>>

>
> Yes.
>

>> Also, it seems strange that you would not care about consistency of
>> your line rows.  See
>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:11504247549852
>>

>
> That's a *very* interesting thread, thanks.
>
> However, it will take me quite a time to digest all the implications,
> and right now I have trouble to see why it would be relevant here.
> Would you have the time to elaborate a bit ?
>
> Short and long transactions don't change - or even look at - the
> same row fields.
>

>> It smells of other dbms's models.
>>

>
> I know what you mean, Joel, and that is not the culprit here.
> I would *vastly* prefer a single transaction over all this stuff.
>
> The problem is that :
>
> a) The response time requirements are what they are,
> for very good reasons, and I have to meet them.
>
> b) Short and long transactions are working on different fields
> (different "facets") of the same row. Unfortunatly (?), the whole
> row is locked.
>
> c) I need to think "out of the box", and that is not easy :-)
>
> Right now, to avoid breaking up the long operation into short
> transactions, I can think of :
>
> - Remove the locking issue altogether, ie split the row fields
> in two tables.
>
> - Make the long transaction lock the list line rows for a short time,
> perhaps building first a temporary table and doing afterwards a
> mass update of the line rows. Of course, list header row would
> be locked for the whole transaction duration.
>
> - other ???

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?

-- 
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 - 11:56:15 CST