Re: dbms_lock.allocate_unique and autonomous transactions (long)

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... Received on Wed Nov 01 2006 - 10:42:49 CST