[DB-SIG] Some obscurity with paramstyle

[M.-A. Lemburg]

James Henstridge wrote:
> On Mon, Jul 18, 2011 at 3:52 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>> Vernon Cole wrote:
>>> A cursor and/or a
>>> connection should probably be context managers, so that they will work in a
>>> "with" statement.
>>
>> This is was discussed before and it should probably go into
>> the DB API in some form.
>>
>> I'm no particular fan of hiding transactions in context
>> managers, though. In practice this often causes problems, since
>> you usually want to apply error handling logic in case of
>> problems, other than simply issuing a .rollback().
> 
> For most of the applications I work with, the transaction handling has
> been delegated to function decorators, or have it hidden in the
> framework (e.g. Django's TransactionMiddleware).  If there are clean
> up tasks that need to happen on transaction commit or roll back (e.g.
> deleting a file on a failed transaction), then using a global
> transaction manager like Zope's transaction module seems to be a good
> fit.
> 
> For transaction retry or error reporting, I haven't seen much benefit
> in leaving the transaction in an "open by broken" state over cleaning
> up with a rollback.

In more detail: When letting a context
manager do a .rollback() on a connection in case of
an error, the error still propagates up the call chain.

However, since the transaction was rolled back, access
to cursors on the connection and the connection itself
will no longer work, so your error reporting and cleanup
possibilities are left with just the error object.

Likewise, when having the context manager do an implicit
.commit() when leaving the block, you are giving away
control and have to add extra code in case you want to
prevent the changes from being committed, e.g. in a debug
situation.

Things can get really interesting in multi-threaded
applications, if you're sharing connections
between threads - but it's better to avoid that anyway,
so I won't go into detail.

Most of these issues can be resolved by coding explicit
context managers that deal with your particular case, e.g.

with TransactionalContext(connection):
    ...

Overall, I think using a transaction manager that explicitly
controls the .commit() and .rollback() calls is a lot
cleaner and the direct "with connection:" construct causes more
problems than it solves in more complex applications.

On the plus side, in simple applications it can help
the user to not forget the .commit() call (the
.rollback() is implicit at garbage collection time
anyway).

So in conclusion, I think we should add it, but with
a warning to the user that it's often better to write
your own context manager.

>> Another problem is that the connections used in a with
>> statement will usually have already started a transaction
>> before entering the context, so the .rollback() would
>> remove more than just the things added in the with
>> context.
> 
> None of the databases I've worked with fit the Python DB API's
> "implicit begin" behaviour for transactions, so their adapters have
> all needed to run a simple state machine to determine when it is
> necessary to start a transaction.  So it would be pretty easy for them
> to raise an error if an attempt was made to use the context manager
> when a transaction was already in progress.

The implicit start of transactions originates from the ODBC
standard which is used by quite a few database as native API,
so the above cannot easily be generalized.

There's also a difference between starting a transaction and
actually making changes on that transaction. An error should
only be raised in case changes were made, but that would
require a lot tedious internal state keeping by the database
package, unless the backend provides an API to query whether
the transaction actually contains any changes.

I don't think there's much we can do about this particular
aspect of using connections as context managers.

> If all adapters that support transactions function like that, then I
> think it would be sensible to require them to raise an error in that
> case.  If they don't raise an error, you know that someone somewhere
> is going to write code like the following:
> 
>     with transaction:
>         # do stuff
>         with transaction:
>             # do more stuff
> 
> ... and wonder why things break.

Indeed.

>> There's also the problem of intuitive use: some users will
>> likely expect the connection to also be closed when leaving
>> the context - much like what happens with files.
> 
> If that is a concern, you could require a method call to get the
> context manager rather than making the connection itself a context
> manager.  For example:
> 
>     with connection.begin_transaction():
>         ...

True, but as discussed above, what would that method do ?

It could do an implicit rollback, but this would then likely
cause other problems such as clearing out changes the user
actually wants to commit later on.

The safe way I know is opening a new connection:

with db.connect(...) as connection:
    ...

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 20 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/