Ann W. Harrison wrote:

>At 06:21 PM 5/27/2004, unordained wrote:
>
>> Sub-transactions would have the same isolation properties
>>relative to each other and their parent transaction as concurrent
>>transactions have to each other and the state of the database (outside of
>>any transaction) ... yes, sub-transactions could deadlock.
>>
>>
>
>OK, more or less...
>

I think it would be more useful to have a subtransaction see everything
in the parent transaction without regard to whether the update happened
before or after the subtranaction was started. A more interesting
question is whether a subtransaction should be able to update a record
updated by the parent transaction. I think the answer is yes.

>>The non-linearity would be a big implementation problem, I'd
>>think. While the MGA can store the back-versions appropriately,
>>the inventory tables would have to handle more complex logic for
>>determining which other transactions a given transaction can see
>>into.
>>
>>
>
>Actually, that's part of the transaction state, and could be twiddled
>to work. The less easy part is allowing a child to see part but not
>all of its parent's work.
>

I have this very strong feeling of deja vu about this -- I'd swear I'd
implemented it some point, but maybe we just went deep down the design
tree to explore the ramifications. Whatever. The implementation would
be remarkably simple. A child transaction from the database perspective
is a separate transaction with a separate transaction id. A rollback of
a child transaction would be like any top level transaction. A commit,
however, is close to a no-op, setting a bit on the (child) transaction
block indicating that it is considered committed relative to its
parent. The functions called by VIO to determine whether a record
version is visible and updatable by a particular transaction would need
to understand the rules regarding parent/child transactions, but that's
next to trivial. Finally, a commit of a transaction with children would
need to do a two-phase commit, including logging the intermediate state.

The hard part is getting a new call through the plumbing and protocols
-- probably an order of magnitude more work than the actual
implementation of the semantics.

But the sticky part isn't implementation but the brain dead transaction
model used by SQL. How in a vanilla SQL environment do you indicate
which of several possible transactions you want to us? The API uses
explicit transaction handle and JDBC uses connection objects that can be
used to model independent transactions, the the vanilla ISQL and ODBC
interfaces leave a potential nest transaction feature high and dry.

--

Jim Starkey
Netfrastructure, Inc.
978 526-1376