Make SQLAlchemy COMMIT instead of ROLLBACK after a SELECT query

Question

I am developing an app together with a partner. I do the database part in PostgreSQL, my partner implements the app on the web-server with Python using SQLAlchemy. We make heavy use of server-side functions. A SELECT query on one of those looks like this in the DB log:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT col_a, col_b FROM some_func(E'myvalue');

ROLLBACK;

In the functions I write certain input to a log table. The app queries by SELECT, SQLAlchemy only sees a SELECT statement and insists on a ROLLBACK. Logging fails. I need it to COMMIT instead. My partner claims there is no easy way, we would have to remove SQLAlchemy altogether. I think he must be wrong but lack the knowledge to claim otherwise.

Is there an easy way to make SQLAlchemy COMMIT instead of ROLLBACK?
What keeps me from just executing trans.commit()? Do I need to set autoflush=False for that?

I have scanned the FAQ, but did not find an answer there.
Searching SO revealed some related questions like here and here, but I am not in the clear.
Maybe this recipe would work?

Huihuie

Are you sure that sqlalchemy is really the one responsible for the rollback? Could it be that you are using a framework that automatically rolls back uncommited sessions at the end of a request loop? – Hankhanke

Not entirely sure, I'll have to ask my partner. I think psychopg is in use. But in that case, couldn't we just COMMIT to keep the framework from rolling back? – Huihuie

It should be enough to either explicitly commit the isolating transaction or set isolation level to zero. – Sullyprudhomme

What I'm asking is if you're using an application framework like Pylons or Zope that in some way manages database state for you; Are you using only sqlalchemy in a plain ol' python script? Just how is the lifetime of each Session instance managed? – Hankhanke

It would be great if you could post the corresponding code of your partner with SA usage. My first guess would be that the session.commit() is not even called, and the rollback comes from the connection pool management to ensure clean connection for new request. – Crinum

I will ask my partner to have a look here and provide the missing info. – Huihuie

I am sorry, my partner refuses to cooperate. Nothing that can be solved on SO. – Huihuie

Answer 1

If you're using SQLAlchemy's connection pooling, then what you're seeing is probably the automatic rollback that happens when a connection is closed after use. It's apparently necessary to guarantee that the connection is 'clean' for the next time it's pulled out of the pool. See this page for more info; search for 'pooling mechanism' near the top.

From what I recall (it's been a couple years since I last worked with this) changing the isolation level to autocommit won't solve the problem, since it won't see the SELECT statement as requiring a commit.

You really just want to wrap that statement in a transaction. I don't know how your code is structured, but you should just be able to use SQLAlchemy's connection.begin and connection.commit. You could even just execute the BEGIN and COMMIT as arbitrary SQL.

Redundancy

Answer 2

You can disable the ROLLBACK and set AUTOCOMMIT while creating the engine.

non_acid_engine = create_engine(
    "mysql://scott:tiger@host/db",
    pool_reset_on_return=None, --disabling the rollback
    isolation_level="AUTOCOMMIT",
)

Note: This is only suggested for non-transactional connections

Reference: https://docs.sqlalchemy.org/en/20/core/pooling.html#pool-reset-on-return

Hierarchy