Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_lock.allocate_unique and autonomous transactions (long)
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
>
Yes.
>
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.
>
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 :
Right now, to avoid breaking up the long operation into short transactions, I can think of :