Re: dbms_lock.allocate_unique and autonomous transactions (long)

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:

1. There are things to do.
2. The things to do are performed by automatic devices.
3. At some point in time, an automatic device will ask the application whether a given thing is really to do.
4. The application needs to answer quickly yes. It also needs to track that the job has been assigned, because :
5. 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)
6. 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.

How would you solve this ? Received on Sat Nov 04 2006 - 17:59:06 CST