SQLAlchemy: session.begin_nested() with session.commit()

Question

I am using Flask-SQLAlchemy, with autocommit set to False and autoflush set to True. It's connecting to a mysql database.

I have 3 methods like this:

def insert_something():
   insert_statement = <something>
   db.session.execute(insert_statement);
   db.session.commit()

def delete_something():
   delete_statement = <something>
   db.session.execute(delete_statement);
   db.session.commit()

def delete_something_else():
   delete_statement = <something>
   db.session.execute(delete_statement);
   db.session.commit()

Sometimes I want to run these methods individually; no problems there — but sometimes I want to run them together in a nested transaction. I want insert_something to run first, and delete_something to run afterwards, and delete_something_else to run last. If any of those methods fail then I want everything to be rolled back.

I've tried the following:

db.session.begin_nested()
insert_something()
delete_something()
delete_something_else()
db.session.commit()

This doesn't work, though, because insert_something exits the nested transaction (and releases the savepoint). Then, when delete_something runs db.session.commit() it actually commits the deletion to the database because it is in the outermost transaction. That final db.session.commit() in the code block above doesn't do anything..everything is already committed by that point.

Maybe I can do something like this, but it's ugly as hell:

db.session.begin_nested()
db.session.begin_nested()
db.session.begin_nested()
db.session.begin_nested()
insert_something()
delete_something()
delete_something_else()
db.session.commit()

There's gotta be a better way to do it without touching the three methods..

Edit: Now I'm doing it like this:

with db.session.begin_nested():
    insert_something()
with db.session.begin_nested():
    delete_something()
with db.session.begin_nested():
    delete_something_else()

db.session.commit()

Which is better, but still not great.

I'd love to be able to do something like this:

with db.session.begin_nested() as nested:
    insert_something()
    delete_something()
    delete_something_else()
    nested.commit() #  though I feel like you shouldn't need this in a with block

Answer 1

The docs discuss avoiding this pattern in arbitrary-transaction-nesting-as-an-antipattern and session-faq-whentocreate.

But there is an example in the docs that is similar to this but it is for testing.

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html?highlight=after_transaction_end#joining-a-session-into-an-external-transaction-such-as-for-test-suites

Regardless, here is a gross transaction manager based on the example that "seems" to work but don't do this. I think there are a lot of gotchas in here.

import contextlib

from sqlalchemy import (
    create_engine,
    Integer,
    String,
)
from sqlalchemy.schema import (
    Column,
    MetaData,
)
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy import event
from sqlalchemy.sql import delete, select

db_uri = 'postgresql+psycopg2://username:password@/database'

engine = create_engine(db_uri, echo=True)

metadata = MetaData()

Base = declarative_base(metadata=metadata)

class Device(Base):
    __tablename__ = "devices"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))


def get_devices(session):
    return [d.name for (d,) in session.execute(select(Device)).all()]


def create_device(session, name):
    session.add(Device(name=name))
    session.commit()


def delete_device(session, name):
    session.execute(delete(Device).filter(Device.name == name))
    session.commit()


def almost_create_device(session, name):
    session.add(Device(name=name))
    session.flush()
    session.rollback()


@contextlib.contextmanager
def force_nested_transaction_forever(session, commit_on_complete=True):
    """
    Keep re-entering a nested transaction everytime a transaction ends.
    """
    d = {
        'nested': session.begin_nested()
    }
    @event.listens_for(session, "after_transaction_end")
    def end_savepoint(session, transaction):
        # Start another nested trans if the prior one is no longer active.
        if not d['nested'].is_active:
            d['nested'] = session.begin_nested()

    try:
        yield
    finally:
        # Stop trapping us in perpetual nested transactions.
        # Is this the right place for this ?
        event.remove(session, "after_transaction_end", end_savepoint)

    # This seems like it would be error prone.
    if commit_on_complete and d['nested'].is_active:
        d.pop('nested').commit()



if __name__ == '__main__':

    metadata.create_all(engine)

    with Session(engine) as session:
        with session.begin():
            # THIS IS NOT RECOMMENDED
            with force_nested_transaction_forever(session):
                create_device(session, "0")
                create_device(session, "a")
                delete_device(session, "a")
                almost_create_device(session, "a")
                create_device(session, "b")
            assert len(get_devices(session)) == 2
        assert len(get_devices(session)) == 2