On Thu, Nov 3, 2022, at 3:51 PM, Marc-Andre Lemburg wrote:
> On 03.11.2022 19:25, Tony Locke wrote:
>> Hello all, with pg8000 if autocommit is turned on while a transaction is in
>> progress, the autocommit behaviour only takes effect after the current
>> transaction has been explicitly committed or rolled back. Of the options
>> given, I'd go for option 1, raising an exception. I don't like the idea of
>> SQL statements being executed implicitly, which is why I'd be against option
>> 2.
> That's a fair point, but please remember that no statements are executed. The
> transaction control is completely with the driver library and negotiated with
> the server (some use implicit statements for this, but that's just a
> technical detail). In fact, users should *not* use transaction statements on
> connections managed by drivers. That's why we have .rollback() and .commit()
> as separate APIs in the DB-API.
>
> I think we've already settled on option 3, with the added note that many
> database modules implement option 2. pg8000 would not be one of those, but
> that's not a problem.
>
> Writing to the attribute will be deprecated at the same time as documenting
> it, so that we can prepare for DB-API 3.0 using a method instead. This would
> then also address your concern.
>
>>
>> A radical suggestion for DBAPI-3.0 would be to remove autocommit completely.
>> I think this would make the dbapi interface easier for people to understand,
>> because there would be no implicit SQL commands, which is what I think
>> confuses people. Of course the downside is less portability, but I think
>> people accept that if they change their database a lot of things will be
>> different, and autocommit is just one of them. A higher level library such
>> as SQLAlchemy could still emit the correct SQL via the dialect so that users
>> of SQLAlchemy wouldn't need to know about autocommit.
> That's not going to work out :-) Autocommit is essential for some database
> operations, e.g. to avoid locking. Many databases also don't permit running
> certain DDL statements inside transactions.
>
I dont think I got Tony's email directly, but re: autocommit, the fact that
basically all DBAPIs now implement autocommit is very advantageous to
SQLAlchemy and in version 2.0 we have finally removed all semblances of
SQLAlchemy's original homegrown "autocommit" feature, which we now refer
towards as "library level autocommit" in contrast to "driver level autocommit".
What's important about "autocommit" is that in most cases, it doesnt
equate to the driver emitting "COMMIT" after every SQL statement, it instead
allows the driver to forego emitting BEGIN in the first place, and the database
itself runs in its own "autocommit" mode, which is typically higher performing.
With DBAPI including the behavior, this first off allows us to simplify our
library, as users who want to work in "autocommit" fashion can do so by just
setting up the driver to work in this way, without us having to provide / test
entirely different ways of working. But that's only the beginning of the
advantages. The bigger advantage is that support for many other styles of
database connectivity is supported, including for some PGBouncer configurations
which don't tolerate transaction boundaries well, connecting to read-only
replica databases where the overhead of DBAPI's implicit "BEGIN" followed by
our necessary ".rollback()" for a pooled connection can be removed (this is a
big MySQL / MariaDB use case), and of course we can support the various DDL
scenarios (mostly on PostgreSQL) that require autocommit. When SQLAlchemy
used "library level autocommit", we had no way to affect that the DBAPI was
still emitting "BEGIN" and that our pooled connections still had to use
ROLLBACK.
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
