SQLAlchemy session rollback exception: cause and fix

Session.rollback() exception usually appears in production APIs or ETL jobs where a database error occurs during commit, leaving the session in a failed state. This forces the next operation to raise InvalidRequestError, silently breaking request handling and retries.

# Example showing the issue
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

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

engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Insert a row
session.add(User(name='alice'))
session.commit()

try:
    # This violates the UNIQUE constraint and triggers an IntegrityError
    session.add(User(name='alice'))
    session.commit()
except Exception as e:
    print('First error:', type(e).__name__)
    # Forgetting to rollback leaves the session dirty
    # session.rollback()  # <-- omitted on purpose

# Any further operation now raises InvalidRequestError
try:
    session.add(User(name='bob'))
    session.commit()
except Exception as e:
    print('Second error:', type(e).__name__)
    print(e)
# Output shows InvalidRequestError on the second commit

The first database error aborts the current transaction, marking the Session as invalid. Any subsequent operation checks the transaction state and raises InvalidRequestError because the previous transaction was not rolled back. This behavior is documented in the SQLAlchemy Session lifecycle guide and mirrors standard DB‑API transaction handling. Related factors:

  • IntegrityError or OperationalError during flush/commit
  • Session left in ‘inactive’ state
  • Automatic autoflush trying to reuse the broken transaction

To diagnose this in your code:

# Detect a dead Session before reusing it
if not session.is_active:
    print('Session is inactive – a previous transaction failed')
    # Optionally inspect the last exception
    print('Last error:', session.info.get('last_error'))

Fixing the Issue

The quickest fix is to roll back the Session immediately after catching the original DB error:

try:
    session.add(new_obj)
    session.commit()
except Exception:
    session.rollback()  # clears the failed transaction
    raise

This restores the Session to a usable state for the current request.

Best practice for production – wrap each unit of work in a context manager that guarantees rollback on failure and validates session health before reuse:

from contextlib import contextmanager
import logging

@contextmanager
def transactional_session(session_factory):
    sess = session_factory()
    try:
        yield sess
        sess.commit()
    except Exception as exc:
        logging.exception('Database operation failed, rolling back')
        sess.rollback()
        raise
    finally:
        # Ensure the session is clean before returning to pool
        if not sess.is_active:
            logging.warning('Session left inactive after error')
        sess.close()

# Usage example
with transactional_session(Session) as s:
    s.add(User(name='charlie'))

The context manager isolates each transaction, logs failures, guarantees a rollback, and closes the Session to avoid leaking a stale connection. This pattern prevents the InvalidRequestError cascade in long‑running services.

The gotcha here is that session.close() does not reset the transaction state – you must call rollback() before closing when an error occurs.

What Doesn’t Work

❌ Using session.close() instead of session.rollback(): close only releases the connection but leaves the transaction state invalid.

❌ Wrapping the error in try: ... except: pass: silently swallowing the exception hides the root cause and leaves the Session broken.

❌ Creating a new Session without disposing the old one: the stale Session stays in the pool and can be handed out later, causing intermittent InvalidRequestError.

  • Skipping session.rollback() after catching IntegrityError
  • Reusing the same Session object across unrelated requests
  • Calling session.close() instead of rollback to clear errors
  • Disabling autoflush and forgetting to flush manually

When NOT to optimize

  • One‑off scripts: Small data migrations that run once can tolerate a manual restart after failure.
  • Read‑only reporting jobs: If the session only performs SELECTs, a failed transaction is unlikely.
  • Prototype notebooks: In exploratory notebooks you may prefer quick fixes over full context management.
  • Trusted data sources: When you know the upstream data never violates constraints, the extra rollback may be overkill.

Frequently Asked Questions

Q: Do I need to call rollback() for IntegrityError only?

Yes – any DB‑API exception that aborts the transaction requires an explicit rollback before the Session can be reused.

Q: Will session.commit() automatically roll back on error?

No – commit raises the original exception but leaves the Session inactive; you must call rollback yourself.

Proper transaction handling is the backbone of reliable SQLAlchemy services. By always rolling back on failure and isolating work in a transactional context, you prevent the cascade of InvalidRequestError that can cripple production APIs. Adopt the context manager pattern and let the Session clean up after itself.

→ Why SQLAlchemy session identity map causes stale objects → Why SQLAlchemy backref creates duplicate rows → Why SQLAlchemy expire differs from refresh