transaction_isolation vs autocommit

[aathan]

Hello. I am engaged in a conversation with SQLAlchemy maintainers and have run into issues that have been discussed there in the past, but which remain unresolved. I'd like to act as a conduit to resolve potentially related issues in both psycopg2 and SQLAlchemy related to transaction_isolation and autocommit. I am not at all familiar with psycopg2 or internals, and therefore decided to open a discussion prior to opening an Issue, so as to minimize noise in your Issues section.

If you would, please take a look at sqlalchemy/sqlalchemy#6489 and (as referenced therein) sqlalchemy/sqlalchemy#3357 (comment), which echoes the conversation I had with the maintainers here: sqlalchemy/sqlalchemy#8252 (reply in thread)

Specifically, that autocommit is a setting that should be treated as orthogonal to any particular isolation level, because, as documented by postgress, autocommit does not mean "no transaction." Rather it simply means "implicit begin/end transaction on a per-statement basis."

It is therefore desirable to set autocommit while, for example, also separately selecting READ COMMITTED or SERIALIZABLE transaction isolation levels.

This is particularly important in highly concurrent apps that utilize a worker thread for database writes, so that queries that may be emitted out of a connection pool see recently written data. If you are in autocommit mode, not being able to set SERIALIZABLE can cause problems if that worker thread ends up rapid-fire writing a bunch of updates across some set of SQLA Sessions and a query follows up soon after waiting on completion of the write-thread-worker's queue.

I have witnessed that in such scenarios, the query may not see the writes that it was waiting for.

Autocommit at the DBAPI layer has to do with preventing SQLA's otherwise "autobegin" behavior from potentially leaving a database transaction open longer than strictly necessary. I.e., it's about transaction duration management.

transaction_isolation on the other hand, is about data visibility.

I said I wasn't familiar with psycopg2 because I surmised from the discussions to which I linked, that SQLA's model of autocommit as a transaction_isolation modality comes from psycopg2. So, fixing this may require coordination between the projetcs.

[dvarrazzo]

As far as I see, autocommit and isolation level are indeed orthogonal in modern psycopg2 versions. Is there anything that you describe which is not covered by connection.set_session() in psycopg2?

• conn.set_session(isolation_level="serializable", autocommit=False) would make each implicit transaction begin with BEGIN ISOLATION LEVEL SERIALIZABLE;
• conn.set_session(isolation_level="serializable", autocommit=True) changes the default_transaction_isolation setting and doesn't create explicit transactions.

The latter wrecks havoc in pgbouncer. As a consequence, the behaviour is dropped in psycopg 3: in this version, connection.isolation_level doesn't affect autocommit connections: if the user wants to set autocommit+serializable they should SET default_transaction_isolation themselves and be responsible for it.