* Thesis *

I belive that using the connection as a context manager is an inadequate API for controlling transactions because it's very likely to result in subtly broken code.

As a consequence, my recommendation would be to deprecate this API.


* Argumentation *

If you nest a naive transaction context manager (BEGIN / COMMIT / ROLLBACK), you'll get very lousy transaction semantics. Look at this example:

with connection:   # outer transaction

    with connection:   # inner transaction
        do_X_in_db()

    do_Y_in_db()

    # once in a while, you get an exception there...

With this code, when you get an exception, X will be presevred in the database, but not Y. Most likely this breaks the expectations of the "outer transaction". Now, imagine the inner transaction in deep inside a third-party library, and you understand that this API is a Gun Pointed At Feet.

Of course, you could say "don't nest", but:

- this clashes with the expected semantics of Python context managers,
- it's unreasonable to expect Python users to audit all their lower level libraries for this issue!


Now, let's look at how popular database-oriented libraires handle this.

SQLAlchemy provides an explicit begin() method: http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.begin

It also provides variants for nested transactions and two-phase commits.

Django provide an all-purpose atomic() context manager: 
https://docs.djangoproject.com/en/stable/topics/db/transactions/#django.db.transaction.atomic

That function takes a keyword argument, `savepoint`, to control whether a savepoint is emitted for nested transactions.


So it's possible to implement a safe, nestable context manager with savepoints. However:

- you need to provide more control, and as a consequence you cannot simply use the connection as a context manager anymore;
- it takes a lot of rather complex code. See Django's implementation for an example:
https://github.com/django/django/blob/stable/1.6.x/django/db/transaction.py#L199-L372

If you ignore the cross-database compatibility stuff, you're probably still looking at over a hundred lines of very stateful code...


That's why I believe it's better to leave this up to user code, and to stop providing an API that looks good for trivial use cases but that's likely to introduce subtle transactional integrity bugs.