How to handle DB transaction failures/request race conditions

[jens.t...@gmail.com]

Hello,

Looking at the SQLAlchemy cookie cutter, a request gets its own transaction-backed db session, and on its way out a response commits that transaction and closes the session. There are activation and veto hooks too.

I'm trying to understand the details behind the request/response ↔ db session/transaction interaction.

During ordinary operation, everything should work just fine and all modifications to ORM objects during a view functions commit with the response. But what happens when a transaction fails, for example because of a CHECK constraint?

How exactly are race conditions handled: suppose two requests to the same endpoint at the same time, both create the same resource, one of which fails (well, should!) to commit. When and how is that handled?

Thank you for pointers and tips and hints in advance 🤓

Jens

[Brian Sutherland]

On Mon, Nov 27, 2017 at 01:00:00AM -0800, jens.t...@gmail.com wrote:
> Hello,
>
> Looking at the SQLAlchemy cookie cutter

> <https://github.com/Pylons/pyramid-cookiecutter-alchemy>, a request gets

> its own transaction-backed db session, and on its way out a response
> commits that transaction and closes the session. There are activation

> <https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/#adding-an-activation-hook>
> and veto
> <https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/#adding-a-commit-veto-hook>

> hooks too.
>
> I'm trying to understand the details behind the request/response ↔ db
> session/transaction interaction.
>
> During ordinary operation, everything should work just fine and all
> modifications to ORM objects during a view functions commit with the
> response. But what happens when a transaction fails, for example because of
> a CHECK constraint?

The details depend on the database you're using, but it should raise an
exception and roll back the transaction.

Ideally the database should prevent the transaction from being committed
once an error occurs on the SQL level. In case someone puts a try:
except: on the python level to catch the exception.

> How exactly are race conditions handled: suppose two requests to the same
> endpoint at the same time, both create the same resource, one of which
> fails (well, should!) to commit. When and how is that handled?

The same as above.

For other cases the details get quite hairy. For PostgreSQL, I've found
this documentation page to be useful whenever I've needed to dive into
the details:

https://www.postgresql.org/docs/current/static/transaction-iso.html

> Thank you for pointers and tips and hints in advance 🤓
> Jens
>

> --
> You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/2a39e5b2-d8fa-43be-891c-4b1cd48d465e%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.


--
Brian Sutherland

[Jonathan Vanasco]

The sqlalchemy cookie cutter uses pyramid_tm to handle the transaction.  The docs on that package should answer all your questions:

   https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/

Basically what happens is this:

* the actual transaction management is provided by zope's transaction package (http://zodb.readthedocs.io/en/latest/transactions.html)
* the glue between zope and sqlalchemy is zope.sqlalchemy (https://pypi.python.org/pypi/zope.sqlalchemy)
* the template uses pyramid_tm to register a transaction around the request using a pyramid tween.

* pyramid_tm manages a "meta transaction" that has different components: one of which is the database "transaction", but there might also be joined transactions for mailing, celery, and other tasks.

When the database transaction fails, an exception is raised.  You can write a view to catch that exception, as explained in the pyramid_tm docs.

In your example, two things can happen based on the The order of access in the race condition and your DB's configuration.

1. One transaction succeeds, the other fails.
2. Both fail (deadlocks or other db errors).

SqlAlchemy should trap almost any driver error regarding this, and abstract it into one of it's own core exceptions (http://docs.sqlalchemy.org/en/latest/core/exceptions.html).

When one of the joined transactions fails, the transaction manager notifies the rest to issue a rollback-- so the database should rollback, email queues are removed, etc.

[Mikko Ohtamaa]

Hi Jens,

You also might find some background information here:

https://websauna.org/docs/narrative/modelling/occ.html

Br,

Mikko

--

You received this message because you are subscribed to the Google Groups "pylons-discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/2a39e5b2-d8fa-43be-891c-4b1cd48d465e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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

[Michael Merickel]

Jonathan gives a pretty good description of how the packages fit together. He left one out, however, which I'll mention.

    https://docs.pylonsproject.org/projects/pyramid-retry/en/latest/

pyramid_retry wraps pyramid_tm and looks for failed transactions that failed due to some serialization/locking issue (or any other custom retryable exception in your app). You can configure pyramid_retry to retry a request before bubbling the error to the client. This can handle some basic locking issues or serialization exceptions based on the isolation levels you choose in your underlying database connection.

A final tidbit is that most of this advice is focused on connections bound to pyramid_tm such as those in the cookiecutter. For example ``request.dbsession`` is always bound to the transaction managed by ``request.tm``. Let's call this the request's primary transaction - the transaction used for all normal operations during the request. It is good to think about it as begun and committed/aborted out of your control when the request begins/ends. There are other use cases where you want to commit work partway through a request... you can do this yourself by making database sessions that are not joined to ``request.tm``. For example in the cookiecutter you can make your own sessions via ``mysession = request.registry['dbsession_factory']()``. You can then commit/abort this transaction yourself in a different lifecycle.

- Michael

--

You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/012c9079-61ed-4462-8d4f-a0a1b8ae4e33%40googlegroups.com.