Re: Nested Transactions, Abort All

On Fri, 9 Jul 2004, Alvaro Herrera wrote:

> On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote:
> > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> >
> > > Yes, we free some things.  Granted it's not a lot, but we have stacks
> > > for several things that will be always be growing with savepoints,
> >
> > They will not always be growing for savepoints, you can free things when
> > using savepoints just as with subtransactions.
>
> I still don't see when I can release a savepoint's state.
>
> You showed a particular case, where we can finish a released savepoint
> that is the innermost transaction.  However, as soon as there is another
> savepoint set after the released savepoint was set, we can't free the
> second.
>
> I mean this:
>
> begin;
>   ... work ...;
>   savepoint foo;
>   ... more work ...;
>   savepoint bar;
>   ... yet more ... ;
>   release foo;
>
>
> At this time I can't release savepoint foo because the implementation
> (nested) requires me to keep it open as long as savepoint bar exists.
> If I released bar at a later time, I could close both, but not before.

According to ANSI 2003, savepoints should be considered in terms of
nesting. That is, the spec talks to nesting levels (4.35.2):

"An SQL-transaction has one or more savepoint levels, exactly one of which
is the current savepoint level. The savepoint levels of an SQL-transaction
are nested, such that when a new savepoint level NSL is established, the
current savepoint level CSL ceases to be current and NSL becomes current.
When NSL is destroyed, CSL becomes current again."

And:

"If a <rollback statement> references a savepoint SS, then all changes
made to SQL-data or schema subsequent to the establishment of the
savepoint are canceled, all savepoints established since SS was
established are destroyed, and the SQL-transaction is restored to its
state as it was immediately following the execution of the <savepoint
statement>."

This is also relevant:

"It is implementation-defined whether or not, or how, a <rollback
statement> that references a <savepoint specifier> affects diagnostics
area contents, the contents of SQL descriptor areas, and the status of
prepared statements."

So, releasing foo would release bar (16.5):

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

Also, the spec makes mention of savepoint behaviour in functions (10.4):

"2) If, before the completion of the execution of the SQL routine body of
R, an attempt is made to execute an SQL-transaction statement that is not
a <savepoint statement> or a <release savepoint statement>, or is a <rollback
statement> that does not specify a <savepoint clause>, then an exception
condition is raised: SQL routine exception   prohibited SQL-statement
attempted. "

It also states that an SQL-invoked function lives in its own savepoint
level (4.27):

"An SQL-invoked procedure may optionally be specified to require a new
savepoint level to be established when it is invoked and destroyed on
return from the executed routine body. The alternative of not taking a
savepoint can also be directly specified with OLD SAVEPOINT LEVEL. When an
SQL-invoked function is invoked a new savepoint level is always
established."

We do not currently support SQL-invoked procedures (that is, routines
executed from SQL with CALL <procname>, which don't need to return a value
and which can accept IN OUT and OUT parameters) so we need only deal with
the SQL-invoked function case.

So, running back to 10.4:

"12) If R is an SQL-invoked function or if R is an SQL-invoked procedure
and the descriptor of R includes an indication that a new savepoint level
is to be established when R is invoked, then the current savepoint level
is destroyed."

So, any savepoints created during the function are destroyed.

What isn't clearly discussed is what they mean by destroy. That is, the
1) ability to reference the savepoint, or 2) all modifications to SQL-data
made since the savepoint was created. I cannot see how it could be (2) can
be the case.

Section 16.5 discusses <release savepoint statement> whose function is to
'destroy a savepoint':

"3) The savepoint identified by S and all savepoints established in the
current savepoint level subsequent to the establishment of S are
destroyed."

It makes no reference to have any effect like rollback.

So, I think that we can only release things once we rollback to a
savepoint or once we commit.

This is not to say we should follow this implementation. I've dug this up
to try and present one (reasonably) consistent perspective on it.

Thanks,

Gavin