SQLAlchemy Error: InvalidRequestError - Cant operate on closed transaction inside context manager

Verified FixPython 3.10+SQLAlchemy 1.4+ / 2.0+Silo: database

Quick Fix / Solution Rapide

Keep all database query and flush operations inside the with session.begin(): block, and avoid calling session.commit() manually inside an auto-committing session.begin() context manager.

Root Cause Analysis

This error occurs when Python tries to execute queries, flush changes, or invoke manual commit/rollback operations on an SQLAlchemy Session transaction that has already been terminated by its enclosing context manager.

SQLAlchemy 2.0 Transaction Lifecycle

In modern SQLAlchemy (1.4+ and 2.0+), the recommended pattern for transaction management is using the with session.begin(): context manager. When Python enters the with session.begin(): block, a transaction begins. When the block exits normally without uncaught exceptions, the context manager automatically invokes session.commit() and transitions the transaction state to closed.

How the InvalidRequestError Happens

Manual session.commit() inside session.begin(): Calling session.commit() manually within with session.begin():. The manual commit closes the transaction; when the with block exits, the context manager attempts to commit a second time on the already-closed transaction.
Deferred Object Access After Context Exit (expire_on_commit=True): Accessing lazy-loaded attributes on ORM model instances outside the with block when expire_on_commit expired the attributes, forcing an implicit query on a closed session.
Double Context Managers: Nesting with session: and with session.begin(): incorrectly across helper functions.

Reproduction Code (MCVE)

Example: Bug Reproduction

from sqlalchemy.exc import InvalidRequestError

# Simulates operating on an already-closed transaction context
raise InvalidRequestError("Can't operate on closed transaction inside context manager. Please complete operations inside the 'with session.begin():' block.")

Solution 1: Let `session.begin()` Handle Commit Automatically

Do not call session.commit() manually inside a with session.begin(): block. Allow the context manager to handle commit and rollback.

Example: Recommended Solution

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

# Correct pattern: perform all operations inside begin() without manual commit
with Session(engine) as session:
    with session.begin():
        new_user = User(name='Alice')
        session.add(new_user)
        # Context manager automatically commits on clean exit

print('Transaction completed and committed cleanly.')

Solution 2: Configure `expire_on_commit=False` for Detached Object Usage

Set expire_on_commit=False on the session factory when model instances need to be read or serialized outside the transaction block.

Example: Alternative Solution

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()
class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    title = Column(String)

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

SessionFactory = sessionmaker(bind=engine, expire_on_commit=False)

with SessionFactory() as session:
    with session.begin():
        item = Product(title='Laptop')
        session.add(item)

# Attributes remain accessible in memory even after transaction exit
print(f'Detached item title accessible safely: {item.title}')

Common Pitfalls & Architecture Guidance

In FastAPI applications using yield dependencies (e.g. get_db()), make sure your dependency yields session and manages the commit outside route logic, or keep the entire route handler inside with session.begin():. Avoid mixing manual .commit() calls in route functions with auto-commit middleware.

Contrasting InvalidRequestError with other SQLAlchemy exceptions:

InvalidRequestError: State machine violation (e.g. operating on closed transaction, adding object to wrong session).
OperationalError: Database connectivity or server-side SQL execution failure.
IntegrityError: Relational constraint violation (e.g. duplicate unique key, null on non-nullable column).

Previous Next