What do zope.sqlalchemy and transaction manager do?

[Mikko Ohtamaa]

Hi,

Not sure if this the right list to ask for regarding zope.sqlalchemy and transaction the package, so please point me to the right direction.

I am looking to have clarifications for the role of transaction package and zope.sqlalchemy packages in the context of SQLAlchemy project. This all started when I was looking for how one can do "Zope-style" transaction retries with SQLAlchemy application. More I kept reading the documentation, more confused I became.

My main question is what are the roles of zope.sqlalchemy and transaction packages in the context of (Pyramid) Python web application? As far as I understand SQLAlchemy provides some of its own transaction machinery. However, the default Pyramid scaffold tosses in zope.sqlalchemy.

When I need zope.sqlalchemy and why would I want to use it?

And then.... how I can create transaction-retry loop, context manager or decorator for a Pyramid application?

SO.com question: http://stackoverflow.com/questions/27351433/sqlalchemy-serializable-transactions-isolation-and-retries-in-idiomatic-python

Opened Github issue: https://github.com/zopefoundation/zope.sqlalchemy/issues/13

Ps. Looks like I might have some issues to get inboud email from zodb-dev so I kindly ask you to cc me in a reply

--
Mikko Ohtamaa
http://opensourcehacker.com
http://twitter.com/moo9000

[Wolfgang Schnerring]

Hi,

* Mikko Ohtamaa <mi...@redinnovation.com> [2014-12-12 21:21]:

> Not sure if this the right list to ask for regarding zope.sqlalchemy
> and transaction the package, so please point me to the right
> direction.

I think this discussion should move to the Pyramid folks[1].

> I am looking to have clarifications for the role of transaction
> package and zope.sqlalchemy packages in the context of SQLAlchemy
> project.

The very short answer is: the `transaction`[2] package is an *abstract*
implementation of the (two-phase) commit protocol[3].
This allows web frameworks like Pyramid (which most often employs
`pyramid_tm`[4] to do that) to offer transaction management regardless
of the actual database being used (be it relational like postgres, or
ZODB, or CouchDB, or...). Of course, since `transaction` is abstract,
you need a concrete backend implementation for the database(s) you want
to use with it. ZODB for example has `transaction` integration built in,
while `zope.sqlalchemy` is an adapter that hooks `sqlalchemy` into the
`transaction` machinery.

Side note: Thank you for asking about this. Unfortunately,
documentation, especially on the conceptual level, for Zope/ZTK, ZODB,
and related packages is close to nonexistent at this time.

Wolfgang

[1] https://groups.google.com/forum/#!forum/pylons-discuss
[2] https://pypi.python.org/pypi/transaction
[3] https://en.wikipedia.org/wiki/Two-phase_commit_protocol
[4] https://pypi.python.org/pypi/pyramid_tm

[Tres Seaver]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/14/2014 05:23 AM, Wolfgang Schnerring wrote:

> * Mikko Ohtamaa <mi...@redinnovation.com> [2014-12-12 21:21]:
>> Not sure if this the right list to ask for regarding
>> zope.sqlalchemy and transaction the package, so please point me to
>> the right direction.
>
> I think this discussion should move to the Pyramid folks[1].
>
>> I am looking to have clarifications for the role of transaction
>> package and zope.sqlalchemy packages in the context of SQLAlchemy
>> project.
>
> The very short answer is: the `transaction`[2] package is an
> *abstract* implementation of the (two-phase) commit protocol[3]. This
> allows web frameworks like Pyramid (which most often employs
> `pyramid_tm`[4] to do that) to offer transaction management
> regardless of the actual database being used (be it relational like
> postgres, or ZODB, or CouchDB, or...). Of course, since `transaction`
> is abstract, you need a concrete backend implementation for the
> database(s) you want to use with it. ZODB for example has
> `transaction` integration built in, while `zope.sqlalchemy` is an
> adapter that hooks `sqlalchemy` into the `transaction` machinery.

The rest of the picture is that pyramid_tm[1] (or repoze.tm2[2], for
non-Pyramid WSGI apps) arranges to begin a transaction at the start of
every request, and to commit the transaction (if the application returns
normally) or abort it (if the application raises an error).

pyramid_tm also has support for retrying request which raise certain
kinds of database-related exceptions (ZODB's ConflictErrorr, etc.: see
http://docs.pylonsproject.org/projects/pyramid-tm/en/latest/#retrying).
For non-Pyramid WSGI apps, repoze.retry[3] does the same.

The key point is that application code in these environments no longer
needs to concern itself with the transaction in the vast majority of
cases: it just needs to raise the appropriate exception, or return a
normal response.

> Side note: Thank you for asking about this. Unfortunately,
> documentation, especially on the conceptual level, for Zope/ZTK,
> ZODB, and related packages is close to nonexistent at this time.

Carlos de la Guardia's ZODB book[4] should be helpful.

[1] http://docs.pylonsproject.org/projects/pyramid-tm/en/latest/
[2] http://docs.repoze.org/tm2
[3] http://docs.repoze.org/retry
[4] http://www.zodb.org/en/latest/documentation/guide/index.html


- --
===================================================================
Tres Seaver +1 540-429-0999 tse...@palladion.com
Palladion Software "Excellence by Design" http://palladion.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlSNj7IACgkQ+gerLs4ltQ6ujACdG9r/WgOfLhmnxKefIzh/Lj4D
EacAnidQboGkwXhyU1183HLS2FolPDdL
=i3gS
-----END PGP SIGNATURE-----

[Mikko Ohtamaa]

> The very short answer is: the `transaction`[2] package is an
> *abstract* implementation of the (two-phase) commit protocol[3]. This
> allows web frameworks like Pyramid (which most often employs
> `pyramid_tm`[4] to do that) to offer transaction management
> regardless of the actual database being used (be it relational like
> postgres, or ZODB, or CouchDB, or...). Of course, since `transaction`
> is abstract, you need a concrete backend implementation for the
> database(s) you want to use with it. ZODB for example has
> `transaction` integration built in, while `zope.sqlalchemy` is an
> adapter that hooks `sqlalchemy` into the `transaction` machinery.

Ok - another question to continue discussion, to get down to the rabbit hole.

For the simplicity, let's assume you are building Pyramid web application and intend to use SQL databases (only) through SQLAlchemy (as this might the most common use case for Pyramid web devs). Nowadays SQLAlchemy offers built-in support for two-phase commits, ATM for MySQL, PostgreSQL:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#enabling-two-phase-commit

Is there any benefit to use zope.sqlalchemy to have Zope transaction manager abstraction layer vs. just using what SQLAlchemy gives out of the box? What I can think of is that pyramid_tm for full HTTP request playback is built on the top of Zope transaction package, but is there something else?

Thanks!
-Mikko
 

[Tres Seaver]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/19/2014 09:16 PM, Mikko Ohtamaa wrote:
> Is there any benefit to use zope.sqlalchemy to have Zope transaction
> manager abstraction layer vs. just using what SQLAlchemy gives out of
> the box? What I can think of is that pyramid_tm for full HTTP request
> playback is built on the top of Zope transaction package, but is there
> something else?

The major benefit is that you write your application without thinking
about the transaction machinery at all: each request starts a
transaction which is automatically either committed (if you return a
normal response) or aborted (if you raise an exception).


Tres.

- --
===================================================================
Tres Seaver +1 540-429-0999 tse...@palladion.com
Palladion Software "Excellence by Design" http://palladion.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlSU6iIACgkQ+gerLs4ltQ5pHACfc8IJ/hvvCiIQqcoQpkFMTk65
p0QAniKjnS8aRX1ZRReN29lboHjEaHFa
=XIcc
-----END PGP SIGNATURE-----

[Laurence Rowe]

On Friday, 19 December 2014 18:16:40 UTC-8, Mikko Ohtamaa wrote:

> The very short answer is: the `transaction`[2] package is an
> *abstract* implementation of the (two-phase) commit protocol[3]. This
> allows web frameworks like Pyramid (which most often employs
> `pyramid_tm`[4] to do that) to offer transaction management
> regardless of the actual database being used (be it relational like
> postgres, or ZODB, or CouchDB, or...). Of course, since `transaction`
> is abstract, you need a concrete backend implementation for the
> database(s) you want to use with it. ZODB for example has
> `transaction` integration built in, while `zope.sqlalchemy` is an
> adapter that hooks `sqlalchemy` into the `transaction` machinery.

Ok - another question to continue discussion, to get down to the rabbit hole.

For the simplicity, let's assume you are building Pyramid web application and intend to use SQL databases (only) through SQLAlchemy (as this might the most common use case for Pyramid web devs). Nowadays SQLAlchemy offers built-in support for two-phase commits, ATM for MySQL, PostgreSQL:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#enabling-two-phase-commit

I think the two phase support in Session existed when I began zope.sqlalchemy (and probably it's predecessor collective.lead.) The zope.sqlalchemy data manager uses this for twophase=True sessions.

Because two-phase commit is more expensive than one-phase commit you only want to enable it if you need to. When you don't the data manager will attempt to commit as the last data manager during the prepare phase. You can only do this safely with a single data manager though.

 

Is there any benefit to use zope.sqlalchemy to have Zope transaction manager abstraction layer vs. just using what SQLAlchemy gives out of the box? What I can think of is that pyramid_tm for full HTTP request playback is built on the top of Zope transaction package, but is there something else?

Once could certainly write a pyramid_tm equivalent that worked directly with a SQLAlchemy Session rather rather than indirectly through transaction and zope.sqlalchemy. If pyramid_tm and zope.sqlalchemy weren't already there (and battle tested and robust) it's probably what I'd do - it would be conceptually simpler. You would lose the possibility just slotting in other transaction aware resources though like pyramid_mailer, etc...

Laurence

 

[Jim Fulton]

On Fri, Dec 19, 2014 at 9:16 PM, Mikko Ohtamaa <mi...@redinnovation.com> wrote:
>
>> > The very short answer is: the `transaction`[2] package is an
>> > *abstract* implementation of the (two-phase) commit protocol[3]. This
>> > allows web frameworks like Pyramid (which most often employs
>> > `pyramid_tm`[4] to do that) to offer transaction management
>> > regardless of the actual database being used (be it relational like
>> > postgres, or ZODB, or CouchDB, or...). Of course, since `transaction`
>> > is abstract, you need a concrete backend implementation for the
>> > database(s) you want to use with it. ZODB for example has
>> > `transaction` integration built in, while `zope.sqlalchemy` is an
>> > adapter that hooks `sqlalchemy` into the `transaction` machinery.
>
>
> Ok - another question to continue discussion, to get down to the rabbit
> hole.
>
> For the simplicity, let's assume you are building Pyramid web application
> and intend to use SQL databases (only) through SQLAlchemy (as this might the
> most common use case for Pyramid web devs). Nowadays SQLAlchemy offers
> built-in support for two-phase commits, ATM for MySQL, PostgreSQL:
>
> http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#enabling-two-phase-commit
>
> Is there any benefit to use zope.sqlalchemy to have Zope transaction manager
> abstraction layer vs. just using what SQLAlchemy gives out of the box? What
> I can think of is that pyramid_tm for full HTTP request playback is built on
> the top of Zope transaction package, but is there something else?

The transaction package provides a transaction manager that can be used
with a variety of back ends. Like any good abstraction, it decouples frameworks
and applications from orthogonal decisions, like what storage mechanism to use.
I gather that many folks use Pyramid with ZODB. By using the
transaction package,
the Pyramid framework isn't tied to one storage mechanism, be it
sqlalchemy, ZODB
or anything else.

Jim


--
Jim Fulton
http://www.linkedin.com/in/jimfulton