Re: dbms_lock.allocate_unique and autonomous transactions (long)

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 05 Nov 2006 09:56:00 -0800
Message-ID: <1162749358.292610@bubbleator.drizzle.com>

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.
>
> How would you solve this ?

I'd be inclined to solve it using Advanced Queueing. Why not have your processes ... whatever they are ... either use a stored procedure with AQ or if your applications are J2EE, communicate directly using JMS with a queue.

The queue can easily handle multiple inputs and assign priorities complete with audit trail.

As I said ... I'm not convinced your approach, with locking, is the best possible solution.

I'm not saying you are wrong ... but using locks should be one of the last resorts ... not the first.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Nov 05 2006 - 11:56:00 CST