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.

hasta_l3 (AT) hotmail (DOT) com wrote:
Why commit at this stage ? Just select for update would be enough.
All other concurrent sessions would wait when trying to select for
update.

I assume it commits after processing all the lines. If not, then
you'd want to (select for update; process a line; commit


No need to reset any flags, just commit.

Other concurrent sessions would wait on select for update.

If the session crashes, the select for update lock will be released.

Dbms_lock is not needed in this scenario.

vc wrote :

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.



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.

hasta_l3 (AT) hotmail (DOT) com wrote:
[...]
So only the transactions modifying the entire list would select for
update the list header and therefore be executed serially, the
smaller transactions that do not care about the list structure would
proceed in the usual way without locking the header first. What is the
problem ?

vc wrote :

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.

hasta_l3 (AT) hotmail (DOT) com wrote:
Look up PRAGMA AUTONOMOUS_TRANSACTION. It may help.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

hasta_l3 (AT) hotmail (DOT) com wrote:
If you want to increase concurrency *and* part (a) is independent from
part (b), then you can split the long transaction into two
transactions (obviously) with part (b) doing the same as your short
transactions in terms of updating the children table. If part(a) and
part(b) must be in the single transaction, then you have to live with
the fact that the smaller transactions may be blocked by the longer
ones. It just does not matter what locking mechanism you might want
use as long as the access needs to be serialized in order for the
changes to be consistent.

DA Morgan wrote :

Yes, Daniel. Autonomous transactions were actually the
main question of my initial (very long :-) post.

But here you do suggest to (1) have the main transaction
lock the list header row, and (2) modify and commit each
list line row in its own autonomous transaction, right ?

It will work, I think, but I dont see how it is much different
from my proposed scheme to (1) lock the list header row with
a dbms_lock, and (2) modify and commit each list line
row in the main transaction(s).

One scheme may be better than the other, but I really cannot
see which or why.

Incidentally, reifying the list/line relationship into its own table
might perhaps be an appealling design. The short transactions
would modify the list line rows, and the long restructuring
transaction would modify the relationship table rows.

Unfortunatly, I cannot contemplate to make *that* kind of
change right now...

hasta_l3 (AT) hotmail (DOT) com wrote:
The difference is that you can commit in the autonomous
transactions and not commit the main transaction. Something
that is different from what you get using DBMS_LOCK.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote :
Yes. While the difference is unimportant (as of now) in my context,
and using a lock minimizes the amount of changes, your suggestion
as a "smell" of rigor and elegance that I like a lot... I will very
seriously consider it. Thanks.

Any known caveat on a massive use of autonomous transactions ?
(8.1.7 and later...)

--- Raoul