Hello Ann,

Friday, May 28, 2004, 1:30:02 PM, you wrote:

> At 06:21 PM 5/27/2004, unordained wrote:

>>The semantics I would expect from nested transactions are as follows:
>>
>>- A transaction is truly committed if it has
>>committed and all parent transactions have committed.

> OK.

>>- A transaction is truly rolled back if it has rolled back or
>>any of its parent transactions have rolled back.

> OK.

>>- 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 would expect sub-transactions to see everything their parent
>>transaction(s) did up until the spawning point,

> That would be hard given the current way Firebird works. There's no
> way to differentiate between records created by a transaction before
> and after an event.

>>and I would expect
>>parent transactions to see everything their sub-transactions have
>>done once they've committed (rejoining the parent, thus acting as
>>if the parent has performed those actions directly)

> OK, I guess.

>>I would expect the global database level (and new top-level
>>transactions) to potentially (based on isolation level) see
>>changes by sub-transactions only once those sub-transactions'
>>top-level transactions had committed.

> For read-committed, that makes sense.

>>the order of sub-transactions would be irrelevant ... multiple
>>sub-transactions could be started simultaneously, without
>>previous sub-transactions having finalized yet.

> OK. That's interesting. Assuming you have transaction A and
> start subtransaction B, how do you know if a new subtransaction
> C is a child of A or B?

>>I think of save-points as sequential along the path of actions
>>by a transaction, allowing you to back up to any point along
>>that linear path.

> That's correct. And, of course, their scope is the same as the
> parent.

>>I don't think I'd ask for the split/join semantics I've seen
>>mentioned in some papers.

> That's good, I don't think the split would be any fun at all.

>>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.

>>one transaction is currently expected to do one thing at a time --
>>suddenly it'd be spawning multiple sub-transactions, all of
>>which could be simultaneously active.

> That's less of a problem than you might think, since a connection
> can have multiple transactions active at a time.

> Interesting. Does anybody else feel a need for subtransactions with
> these semantics?

Not here. And, it's not even part of the SQL standards, except for the
support of a local branch transaction when using one transaction
(two-phased commit) that is used for multiple connections/DBs/Servers.
Other than that, no.

I checked with MS-SQL, just for fun. And, it does support nested
transactions, but you only work in one transaction at a time. Also,
when issuing a COMMIT or ROLLBACK, it is applied with the latest
started transaction(the example was with a script, but the document
also mentions the same). And, they implemented it to support
transactions in SPs. And, they do have savepoints as well, which work
in the same way as in FB.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)