(I looked around *very* quickly to see who else might have something comparable:)
(bracketted numbers refer to some of Ann's questions, snipped and repeated below)

[1] So, Berkeley DB seems to have nested transactions, with restrictions. A parent transaction
can't do anything until its child transactions are all finished, except start new ones (they can
run simultaneously, but any children prevent the parent from doing normal sql stuff.) That solution
would certainly take care of the "child only sees what parent did up to spawn point" problem (in
that nothing else gets done.)

The idea is to consider the parent transaction in mostly the same way we consider the global state
of the database, with all the rules that go with that. When a top-level transaction commits, it's
visible at the global level -- and thus a sub-transaction committing makes itself visible in the
parent context (but not necessarily to concurrent sub-transactions, or, for that matter, anybody
else.) Concurrent sub-transactions can block each other, but are first-come, first-served to the
parent's data (no blocking against parent.) The global database state can be understood as
a "transaction 0", parent to all other transactions. All top-level transactions are actually
children to this eternally-running transaction: the big difference is that we can't undo a
database's entire history (except via deletion.) (That, and DDL not being exactly transactional.)

J2EE mentions nested transactions, but as I'm no java person, and not too keen on the buzzwords,
I'm not looking into it. (Just had to be entirely honest on that point.)

PostgreSQL seems to have nested transactions as a "some day" feature, with implementation notes,
but nothing done yet. The open project includes both savepoints and nested transactions, lumped
together. (Just a note for Jonathan Neve: one of the other open projects seems to be "point in time
recovery", which seems to be based on taking a snapshot, a set of recovery files, and recoving only
part of the way ... see http://candle.pha.pa.us/main/writings/pgsql/project/pitr.html -- it's not
what I was remembering, but it's something. But if we (at least I) can joke about mysql's
unimplemented future features ... probably shouldn't rely on this either.)

I agree with Jim that batch-sql runs wouldn't be able to use this feature. In the same way, batch-
sql runs can't currently simulate multiple users running concurrent, independent transactions. Is
it a problem to have a feature not be usable in certain cases?

[2] As to knowing if C is a child of A or B ... I would assume that would depend on the syntax for
declaring a new transaction. If you do it via the API, passing an existing transaction (or none for
a new top-level transaction), then it should be obvious. If like Oracle you're doing it in batch
mode (which is the case in which save-points == nested transactions) then it's based on whether or
not the current sub-transaction has finished. I'm rooting for the API method, because otherwise
it's save-points, which FB already has.

[3] Need? I get by without save-points as it is ... I'm not sure there's a logical proof that this
feature is required -- if it were absolutely needed, we'd probably already have it. It's pretty
much a convenience (and opportunity to get ahead?)

I can see uses for it mostly in cases where you want to avoid queuing up everything the user has
requested; instead you can do it as they go, and still provide easy "undo" functionality for chunks
of what they're requesting. Go ahead and insert that record they want, but if they decide they
don't like it, you have the option of either just undoing that one request, or of going through the
pain of tracking down what they did, and how to undo it yourself (and I'm sure you can agree it's
easier to rollback() than it is to perform all the actions required to undo something, by hand.)
The client can simply track a set of active transactions that are all part of the same transaction,
undoing them individually (without regard for order.) When they're doing doing everything they're
going to do, commit it as a block. Clients wouldn't necessarily feel the need to associate the
concepts of a transaction and a screen, though for the same reason batch-sql can't use nested
transactions, the GUI to indicate "everything I'm doing now, I want to do as a single global
transaction" could be "hard to manage" (to say the least.) Individual screens could retain all of
their undo functionality, their concurrency, without problems.

But again, it's a feature request, a convenience, not a requirement for software to run
(obviously.) It can wait, or be dropped, and we'll all (even I will) survive. I just think it'd be
neat to have. (Interbase/Firebird does have a history of being ahead of its time -- some DBMS'
still do without MGA, good (any) domain support, good constraint support, UDF's, SP's, heck, Mysql
gets by without most of what makes for a good DBMS!)

Anyway, thanks for your interest in the matter -- I hope it can at least get its own post-it on
someone's desk, somewhere, to collect dust ...

-Philip

---------- Original Message -----------
From: "Ann W. Harrison" <aharrison@...>

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

[1]

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

[2]

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

[3]

> Regards,
>
> Ann

------- End of Original Message -------