Re: dbms_lock.allocate_unique and autonomous transactions (long)

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.

If you want to increase concurrency *and* part (a) is independent from part (b), then you can split the long transaction into two transactions (obviously) with part (b) doing the same as your short transactions in terms of updating the children table. If part(a) and part(b) must be in the single transaction, then you have to live with the fact that the smaller transactions may be blocked by the longer ones. It just does not matter what locking mechanism you might want use as long as the access needs to be serialized in order for the changes to be consistent. Received on Wed Nov 01 2006 - 10:28:42 CST