Re: dbms_lock.allocate_unique and autonomous transactions (long)

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

>

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