Savepoint release and nested transactions

[Laurence Rowe]

Hi,

Following a request[1] for savepoint release support in
zope.sqlalchemy, I've been looking into how this might be done. Adding
the necessary support to Zope's transaction module was quite simple
[2], but the mapping of Zope transaction savepoints -> SQLAlchemy
nested transactions -> database savepoints gives a problem...

How can I get SQLAlchemy to release a savepoint, without also
releasing all subsequent savepoints (the nested transactions? This is
demonstrated by the doctest below from my branch of Zope's transaction
module:

Savepoint release
-----------------

Some data managers may only support a limited number of savepoints.

>>> dm['bob-balance'] = 100.0
>>> dm['bob-balance']
100.0
>>> savepoint1 = transaction.savepoint()

>>> dm['bob-balance'] = 200.0
>>> dm['bob-balance']
200.0
>>> savepoint2 = transaction.savepoint()

>>> dm['bob-balance'] = 300.0
>>> dm['bob-balance']
300.0
>>> savepoint3 = transaction.savepoint()

To release resources on the data manager, a savepoint may be released:

>>> savepoint2.release()

The savepoint then becomes invalid and may no longer be used:

>>> savepoint2.rollback()
Traceback (most recent call last):
...
InvalidSavepointError

Subsequent savepoints remain valid:

>>> dm['bob-balance'] = 400.0
>>> dm['bob-balance']
400.0
>>> savepoint3.rollback()
>>> dm['bob-balance']
300.0

As do previous savepoints:

>>> savepoint1.rollback()
>>> dm['bob-balance']
100.0

>>> transaction.abort()

Laurence

[1] http://groups.google.com/group/sqlalchemy/browse_thread/thread/b2594ff621538f3f

[2] http://svn.zope.org/repos/main/transaction/branches/elro-savepoint-release

[Michael Bayer]

On Jan 16, 2010, at 12:35 PM, Laurence Rowe wrote:

Hi,

Following a request[1] for savepoint release support in
zope.sqlalchemy, I've been looking into how this might be done. Adding
the necessary support to Zope's transaction module was quite simple
[2], but the mapping of Zope transaction savepoints -> SQLAlchemy
nested transactions -> database savepoints gives a problem...

How can I get SQLAlchemy to release a savepoint, without also
releasing all subsequent savepoints (the nested transactions? This is
demonstrated by the doctest below from my branch of Zope's transaction
module:

OK, the first thing I see here, unless I'm misunderstanding, is that you're looking to remove a savepoint from an arbitrary point in the nesting.   I.e. not just the "endpoint".

So the next road I went down, OK, we would need a way to manipulate the list of Transactions at the engine level and SessionTransactions at the ORM level, such that a node can be removed from the middle of the list, issuing a RELEASE, and leaving the list otherwise with the same nodes and endpoints.

I then made a quick patch against engine/base.py to try this.     A test looks like this:

trans = conn.begin()

conn.execute("select 1")

trans2 = conn.begin_nested()

conn.execute("select 1")

trans3 = conn.begin_nested()

conn.execute("select 1")

trans2.release()

trans3.release()

trans.commit()

Do I have this right ?   trans2 is the first savepoint, trans3 is the second.  We want to release trans2 first, leaving trans3 intact.

PG at least does not allow this (trimmed):

BEGIN

select 1

SAVEPOINT sa_savepoint_1

select 1

SAVEPOINT sa_savepoint_2

select 1

RELEASE SAVEPOINT sa_savepoint_1

RELEASE SAVEPOINT sa_savepoint_2

sqlalchemy.exc.InternalError: (InternalError) no such savepoint

 'RELEASE SAVEPOINT sa_savepoint_2' {}

Here's what PG docs have to say:

http://www.postgresql.org/docs/8.1/static/sql-release-savepoint.html

RELEASE SAVEPOINT also destroys all savepoints that were established after the named savepoint was established.   

I.e. that statement is in direct contradiction to your request  "without also releasing all subsequent savepoints".

So Postgresql, which I consider the gold standard of transaction operation, would appear to not support this.  Do we know that this is a viable feature to be pursuing ?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Laurence Rowe]

Thanks for looking into this. I hadn't realized that subsequent
savepoints would also be destroyed. I'll make Zope's transaction
module match the PostgreSQL behaviour. As savepoints and nested
transactions are then equivalent I shouldn't need anything more from
SQLAlchemy.

Laurence