Re: dbms_lock.allocate_unique and autonomous transactions (long)

vc wrote :

> hasta_l3_at_hotmail.com wrote:
> > Dear group,
> >
> > We have lists. Each list has thousands of
> > ordered lines. Lists are implemented with
> > the usual master/detail tables.
> >
> > An operation O may process (a subset of the
> > fields of) the lines of a list.
> >
> > Operations must be serialized, so that not
> > two of them process the same list at the
> > same time.
> >
> > However, other transations may want to modify
> > other fields of the lines of a list being processed,
> > and must not be blocked more than 3 secs.
> > However, an operation may need a few minutes
> > to complete.
> >
> > Operations process only active lists, and there
> > is at most 100 active lists in a given day. Lists
> > become inactive after one day after, and new
> > lists are created.
> >
> > In the current design, an operation notably :
> >
> > - acquires a list lock by setting a flag in the
> > list header row, then commits.
>
> Why commit at this stage ?

Because the transactions that cannot wait more than 3 secs may also change some (unrelated) field of the list header row.

I forgot to mention that, sorry.

> Just select for update would be enough.
> All other concurrent sessions would wait when trying to select for
> update.
>
> >
> > - processes each line and commits.
> >
>
> I assume it commits after processing all the lines. If not, then
> you'd want to (select for update; process a line; commit;)

No, an operation commits after having processed each line, so that other unrelated transactions are not blocked.

However, the operation O must still be atomic with regard to the other operations O (for a given list).

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.

>
>
> > - resets the flag, and commits.
>
> No need to reset any flags, just commit.
>
> >
> > Of course, each operation first look (for update)
> > whether the flag is set.
>
> Other concurrent sessions would wait on select for update.
>
> >
> > This design fails miserably if the session
> > holding the flag crashes.
>
> If the session crashes, the select for update lock will be released.
>
> Dbms_lock is not needed in this scenario.
>
> >
> > I though that a dbms_lock would be a perfect
> > replacement for the flag locking scheme, since :
> >
> > - the lock can persist across transactions.
> >
> > - it will be released if the session crashes.
> >
> > The lock would be allocated the first time it
> > is needed by an operation O; named after the
> > (qualified) primary key of the serialized list
> > header row for easy retrieval; and have a retention
> > delay of one day.
> >
> > The problem is that dbms_lock.allocate_unique
> > (understandably) does commit. That is troublesome,
> > because the beginning of an operation must be
> > atomic.
> >
> > Therefore, I'm thinking of calling allocate_unique
> > in an autonomous transaction. The dbms_lock.request
> > would still be called in the enclosing transaction.
> >
> > I cannot find a problem with this scheme :
> >
> > - It is not a problem if the lock stays allocated,
> > should the enclosing transaction rollback. The lock
> > will likely be needed later on anyway.
> >
> > - It is not a problem if a transaction creates
> > the lock and finds it already requested when
> > trying to request it.
> >
> > - My tests show a slowdown of 0.1 ms when using an
> > autonomous transaction - negligeable in my context.
> >
> > However, Tom Kytes is so suspicious of autonomous
> > transactions that I would like to have your opinion.
> >
> > - Is this a valid use of autonomous transactions ?
> > - Any problems in the design ?
> > - Other caveats ?
> >
> > Oracle 8.1.7 (yes :-) and later.
> >
> > Thanks *a lot*
> >
> > --- Raoul
> >
> > P.S. While not our focus right now, we may revisit
> > the committing scheme later on, and opinions would
> > be welcomed too.
Received on Wed Nov 01 2006 - 06:55:50 CST