Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: dbms_lock.allocate_unique and autonomous transactions (long)
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.
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.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Nov 01 2006 - 13:10:49 CST