[ZODB-Dev] Re: [Persistence-sig] Nesting and savepoints

[Phillip J. Eby]

At 10:12 AM 8/1/02 -0400, Phillip J. Eby wrote:
>
>I think I'm going to go back and look at the manuals for some of the other
>databases I'm using or plan to use in future, and verify what nesting or
>savepoint capabilities they have.  It seems to me that one could simulate
>savepoints through the use of nested transactions, or vice versa.  I'll
>also take a look at JDBC's metadata variables for transactions, to get an
>idea of what variations of capabilities are likely to be out there.
>
>After I've got a better idea of what the different DB's do or don't support
>in this area, I'll comment again.  :)
>

An update on the transaction models of some common database systems and APIs:

PostgreSQL: no nesting, no savepoints

BerkeleyDB: nesting; parallel simultaneous child transactions allowed

Sybase: nesting w/sequential children only; rollback rolls *entire*
transaction back unless savepoints are used to mark child transaction
beginnings; savepoints can be named and rollback to any point is possible

Oracle: no nesting; named savepoints with rollback to any point; savepoints
can be used to emulate sequential child transactions to arbitrary nesting
depth.

Java JTA/JTS: support for nested transactions is optional; the XAResource
interface explicitly does *not* support nested transactions, and nothing in
the JTA spec defines the semantics of nested transactions, so whether
nested transactions can be parallel or must be sequential doesn't appear to
be specified.

Java JDBC: spec does not mention nested transactions at all.  Named
savepoints are supported; one calls savepoint =
connection.setSavePoint("savepoint_name"), and rolls back with
connection.rollback(savepoint).  There is a metadata field for whether a
driver supports savepoints.

Note: by "sequential children", I mean that one can only have one
uncommitted child transaction per parent transaction.  In this model, a
"begin" operation always nests within the outer transaction, rather than
creating a "parallel" child transaction.  BerkeleyDB supports parallel
children, where you can have more than one direct child transaction of a
given parent.  In other words, the sequential children model has a stack of
active transactions, while the parallel children model has a tree of active
transactions.

Anybody have any other data points to share on this subject?