I think we can improve the documentation there by extending more about the effects of the interaction between DBAPI transactions and the transaction() blocks. And things are definitely more intuitive if transaction() is used in autocommit: we might want to advise people to do that.
Agree. The DBAPI choice to mandate that autocommit be off by default is a strange and unfortunate choice that in my experience leads to a never ending series of "surprises" such as this one.
My conclusion is that the only sane thing to do is:
1. Only ever create connections in autocommit mode.
2. Only ever use `with connection.transaction()` to control transactions.
3. Forget that `connection.commit()` and `connection.rollback()` exist, and never use them.
I'm in favour of recommending this as strongly as reasonably possible in the documentation.
It's a shame that 1 is not the default. So you still have to remember to not forget to do this explicitly, every time you create a connection.