Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: joel garry <joel-garry_at_home.com>
Date: 1 Nov 2006 15:55:37 -0800
Message-ID: <1162425337.706963.256190@f16g2000cwb.googlegroups.com>

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.

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

It smells of other dbms's models.

jg

--
@home.com is bogus.
"Fig bars are actually one of the seven perfect foods, the others being
SPAM, bologna, spearmint gum, Ju-Ju Bees, Cheez Whiz and lard." - Dr.
Science

Received on Wed Nov 01 2006 - 17:55:37 CST