dbms_lock.allocate_unique and autonomous transactions (long)

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.
• processes each line and commits.
• resets the flag, and commits.

Of course, each operation first look (for update) whether the flag is set.

This design fails miserably if the session holding the flag crashes.

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 - 04:05:17 CST