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: <hasta_l3_at_hotmail.com>
Date: 4 Nov 2006 00:50:26 -0800
Message-ID: <1162630226.419732.25450@e3g2000cwe.googlegroups.com>


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 :

  1. The response time requirements are what they are, for very good reasons, and I have to meet them.
  2. Short and long transactions are working on different fields (different "facets") of the same row. Unfortunatly (?), the whole row is locked.
  3. 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 :

Received on Sat Nov 04 2006 - 02:50:26 CST

Original text of this message

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