Re: Nested Transactions, Abort All

<posted & mailed>

Dennsnippetssklund wrote:

> On Fri, 9 Jul 2004, Mike Rylander wrote:
>
>> Nested transactions and savepoints serve two different purposes. They
>> have some overlap, but for the most part solve two distinct problems.
>
> Then show some examples that illustrait the difference. So far all
> examples shown that uses subtransactions could just as well have been
> written using savepoints.
>

After seeing some more snippets of the SQL2003 spec it seems that this is
true, and that there is more of a syntactic difference than functional.
This does not seem to be the case for Oracle (the other major
implementation that has been cited for SAVEPOINT syntax), as savepoints in
Oracle are not logically nested. Note that I am going on the statements
from others on this list for this point...

> I don't agree that they have two different purposes.

They do, if only to make particular constructs easier to write. This is an
opinion, but for example an EXCEPTION framework for plpgsql would be easier
to implement and use if it used the nested transactions rather than
savepoint syntax:

CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
BEGIN
BEGIN NESTED;
do some work...
BEGIN NESTED;
do other work...
EXCEPTION WHEN SQLSTATE = already_exists THEN
do alternate work with its own error checking...
END NESTED;
EXCEPTION WHEN SQLSTATE = fkey_violation THEN
ROLLBACK NESTED;
END NESTED;
END;';

I realize this can be done with nested savepoints and that is what the spec
requires, but in other major implementations of savepoints this nested
exception handling would be more difficult to write. Again, simply my
opinion.

>
>> I don't think so, especially as there has been some talk of implementing
>> savepoints as a subset of nested transactions.
>
> It is not a subset. It's the other way around. Nested transactions are a
> subset of savepoints

Perhaps I got my wires crossed a bit there. And again, after looking at
some more of the SQL2003 spec this does seem to be the case. I cry your
pardon! :)

>
> Savepoints have more possibilities, you can invalidate older savepoints
> then the last (with subtransactions you can only commit/rollback the
> last).

This implies that savepoints are flat. It won't be that way under the
covers, but it does give that impression, and flat savepoint space is
definitely suited to a different class of problems than nested
transactions.

> If you don't use that then it's exactly the same as
> subtransactions.
>

I don't see this. Nested transactions present a hierarchal interface to the
user, savepoints don't, especially considering that those familiar with
PL/SQL know that savepoints are not nested. Now, savepoints can be used IN
a hierarchy, but they do not DEFINE one as nested transactions do.

I look at it this way: Let's have both, and where a user wants a flat
transaction space, as that may suit the needs of the problem, they will use
SAVEPOINT syntax; if the user would perfer an explicit hierarchy they can
use nested transactions. Everyone wins!

> The only "feature" subtransactions have that savepoints doesn't is the
> lack of names. Every savepoint have a name. If we want an extension it
> could be to get the database to generate a fresh savepoint name. The
> client can of course also generate unique savepoint names if it want.

I don't think they can be compared like that, feature for feature. Although
I agree with you that they provide extremely similar feature sets, the
present different interfaces to the user. They may end up being backed by
the exact same code but the syntax and logical structure will surely
differ, and when a user wants labeled rollback point they will use
savepoints. When s/he wants hierarchical rollback points they will use the
nested transactions syntax.

BTW, I would imagine that savepoints will be implemented as nested
transactions with detachable labels... the label can move from a
transaction to one of its descendants, and that outer (sub)transaction will
be implicitly COMMITed with its parent.

>
> That subtransactions do more than savepoints is just smoke an mirrors. So
> far there have been no example to validate that point of view, and I don't
> think there will be any. If anyone know of something that you can do with
> subtransactions and not with savepoints, please speak up.
>

You have opened my eyes to the fact that savepoints and nested transactions
can be used for most of the same problems, however I don't see this as a
one-or-the-other proposition.

Alvaro found it easier to implement nested transactions, he forged ahead and
did it. Now, because of good design or simple luck, we should be able to
implement savepoints fairly easily. To me this is the best we could have
hoped for, as it means that not only will be support the entire SQL2003
spec WRT savepoints, we actually get to present a richer interface to the
user, one that includes a feature explicitly designed to model the
hierarchical nature of certain datasets and/or solutions. Of course anyone
reading this can guess which interface I am looking forward to, but the
point is that we will have both where most others don't have a complete
implementation of either!

--miker