Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 4 Nov 2006 19:16:01 -0600
Message-ID: <uodrmh611.fsf@rcn.com>

On 4 Nov 2006, hasta_l3_at_hotmail.com wrote:
> DA Morgan wrote :

>> hasta_l3_at_hotmail.com wrote:
>> >> I am led, by what you've written, to question whether you
>> >> understand basic Oracle architecture and concepts. I still don't
>> >> quite see this as being anything more than overcomplicating
>> >> something unnecessarily.
>> >>
>> >> Oracle is not SQL Server. Why is it that any locking is required?
>> >
>> > I never used SQL Server, Daniel.
>> >
>> > To recap the context :
>> >
>> >> 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.
>> >
>> > Right now, that problem is solved by breaking the long operation
>> > in many small transactions.
>> >
>> > I'm just enumerating ways to replace the burst of thousands of
>> > transactions by a single long one, if profiling shows it to be
>> > necessary.
>>
>> I've read all this ... several times ... I still don't see the point?
>>
>> 1. Why does it have to be two processes?
>> 2. So what if multiple processes modify a record?
>>
>> Again ... I don't see the underlying business case for what you are
>> doing. In what way will one transaction lock another unless you
>> explicitly lock it and why would you do that unnecessarily?
>>
>> Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have?
>>
>> Again ... I've read what you've written but I still don't see a
>> business case that indicates what you are doing must be done as you
>> are doing it.

>
> Oh ?
>
> I cannot be too specific, but roughly and simplifying:
>
> a) There are things to do.
> b) The things to do are performed by automatic devices.
> c) At some point in time, an automatic device will
> ask the application whether a given thing is really to
> do.
> d) The application needs to answer quickly yes. It also needs
> to track that the job has been assigned, because :
> e) Another device might (shortly) later on ask whether
> that same thing is to do, and the application needs
> to answer no (because the job has been assigned already)
> f) All this information is of course stored in a ThingsToDo
> table.
>
> These are the short transactions.
>
> Now, for user convenience, it is customery to group
> the things to do in lists. Thus, the ThingsToDo table
> is given additional fields, such as the ThingsToDo
> list it belongs to, ordering information, etc... Note
> that the process handling the automatic devices does
> not need nor even know this information.
>
> It may happen that a user reorganizes the list, moving lines
> around, perhaps even to another list. The ordering information
> must then be recomputed, according to a non trivial algorithm
> that takes time.
>
> This is the long transaction, and may - it does - happen
> while the automatic devices are querying like crazies.

It seems that the reorganization does not affect the answer that somethingThatHasBeenDone has stillBeenDone, correct? So, then, I would put that whole operation under one transaction so as to not affect the thisJobIsDone answer.

The quick ones are the ones I'd be worried about. While a thingToDo is being performed, another process can query to see if the same thingToDo needs to be done, correct? Then, Oracle would answer yes to that question, until the first one marks it as thisThingIsComplete. What I'd expect is that you mark the thingToDo as thisThingIsBeingWorkedOn, so the subsequent processes know to ask again until they get, thisThingNeedsToBeDone or thisThingIsAlreadyDone.

-- 
Galen Boyer

Received on Sat Nov 04 2006 - 19:16:01 CST