Re: dbms_lock.allocate_unique and autonomous transactions (long)

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 ? 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;)

> - 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 - 05:19:18 CST