Handling transactions in Python DBI module

[dieter]

Chris Angelico <rosuav at gmail.com> writes:

> On Thu, Feb 11, 2016 at 6:59 PM, dieter <dieter at handshake.de> wrote:
>> In my context (web applications), I strongly discourage this use - at least
>> when "conn" does not handle subtransactions properly.
>>
>> In a web application, the main transaction should in general be controlled
>> at the request level: a request should either succeed as a whole or have no
>> side effects whatsoever. This prohibits local (somewhere
>> deep in a component) transaction control.
>
> Hmm. I'm not 100% convinced that web apps should behave that way;

In a web application, the user either sees a success or an error response
(for his request). In case of an error, it would be fatal (in the general
case) that persistent data would have been inconsistently modified
(e.g. an purchase performed; without proper acknowledgent to the user).

> but
> part of the simplicity comes from requiring that database-dependent
> code should not call other database-dependent code, as that would
> create a nested transaction.

The web applications I am working with are highly modular.

Many of those modules manage persistent data in a database and
operate on it independently. It would be fatal when a successful
database interaction in one module would as a side effect commit
database operations in another module.

> (That said, though, it's entirely
> possible that psycopg2 could handle a nested "with conn" as
> "SAVEPOINT" and either "RELEASE SAVEPOINT" or "ROLLBACK TO SAVEPOINT".
> But I wouldn't recommend depending on that without confirming it in
> the docs.)

For "psycopg2", I have done this.

> Bear in mind, the rule I gave was a broad and general rule for
> students to follow, not a hard-and-fast rule for all databasing. It's
> designed such that the student can learn the exceptions later on, but
> meanwhile, his code will be correct (if occasionally a little warped
> to avoid nesting transactions).

I want to stress that in the domain of web applications, local
transaction control is not a good rule of thumb. At least there,
transaction control should in general belong to the request framework
(as this is the atomic user interaction level for web applications)
and to local components.