Session Lifecycle

How a Session begins, what happens at flush, and when the connection actually gets used. The “I get a confusing transactional error” bugs almost always trace to misunderstanding this.

The bookend pattern

with Session(engine) as session:
    with session.begin():
        # do work
        session.add(user)
    # commits on inner block exit, rollback on exception
# closes on outer block exit

This is the canonical 2.0 form. The outer Session(engine) manages the session; the inner session.begin() manages the transaction.

What Session(engine) does (and doesn’t)

session = Session(engine)
# At this point: NO connection acquired, NO transaction started

Lazy. The session doesn’t reach for the pool until it has to (a query, a flush, an explicit session.connection()).

session.execute(select(User))   # NOW: acquires connection, begins transaction
session.commit()                 # commits the transaction; releases the connection back to the pool

After commit/rollback, the session is reusable for another transaction:

session.execute(select(User))   # acquires conn again, begins NEW transaction
session.commit()

Eventually you close it:

session.close()                  # ensures connection returned, session unusable

with Session(...) does the close for you. Always use the context manager.

autobegin

In 2.0, sessions are “autobegin” by default — the first statement after a commit/rollback begins a new transaction implicitly. Before 2.0 you sometimes needed explicit session.begin().

session = Session(engine)
session.execute(select(User))    # autobegin transaction
session.commit()
session.execute(select(User))    # autobegin AGAIN — new transaction
session.commit()

You can opt out with Session(engine, autobegin=False) and manage transactions explicitly via session.begin(). Rarely needed.

flush vs commit revisited

The Session has two layers of state:

1. Pending/dirty/deleted objects — Python-side tracking.
2. The DB transaction — what’s actually being staged in the DB.

flush() translates layer 1 into SQL on layer 2 (still uncommitted). commit() flushes (if needed) and then COMMITs the transaction.

user = User(name="alice")
session.add(user)              # pending in Python; nothing in DB
session.flush()                # INSERT issued; user.id populated; not yet committed
# at this point another connection can't see the row (READ COMMITTED)
session.commit()               # COMMIT — visible to other connections

autoflush=True (default) triggers a flush before every query, so your query sees your pending changes:

session.add(User(name="alice"))
# autoflush kicks in before this query:
count = session.scalars(select(func.count(User.id))).one()
# count includes alice

Sometimes you want to suppress autoflush (e.g. when an autoflush-during-query would itself cause an error). Use:

with session.no_autoflush:
    ...

Rolling back

try:
    session.add(user)
    session.commit()
except Exception:
    session.rollback()
    raise

After rollback():

• All pending/dirty changes are discarded.
• All loaded objects are expired (next attribute access SELECTs again).
• The transaction is gone.

The instances are still attached to the session — you can keep using them, but their attribute values are stale until refreshed.

If you forget to roll back after an error inside an explicit transaction, the session becomes unusable. You’ll get PendingRollbackError on next operation. The context manager (with session.begin():) avoids this.

Nested transactions / savepoints

with session.begin():                       # outer transaction
    session.add(user1)

    with session.begin_nested():            # SAVEPOINT
        session.add(user2)
        # if this raises, only the savepoint is rolled back
        # user1 is still pending in the outer transaction

    session.add(user3)
# commits the outer transaction if no exceptions

begin_nested() issues a SAVEPOINT in the DB. Inner failures roll back to the savepoint without aborting the outer transaction. Useful for “try this risky operation; on failure, keep the rest of the transaction going.”

begin_nested() works at any nesting depth (SAVEPOINTs can stack).

See 09_transactions.md for the SQL-side details.

Closing the session

session.close()

• Returns the underlying connection to the pool.
• Marks all instances as detached.
• Does NOT commit pending changes — call commit first.

After close, accessing un-loaded attributes raises DetachedInstanceError. Already-loaded attributes are fine — they’re just Python objects.

For “I need to use this object after the session is closed” — load all attributes you need before close, or set expire_on_commit=False, or use the request-scoped session pattern (close at end of request).

Session per request — the standard web pattern

# fastapi-style dependency
def get_session() -> Iterator[Session]:
    with SessionLocal() as session:
        yield session

@app.get("/users/{uid}")
def get_user(uid: int, session: Session = Depends(get_session)):
    return session.get(User, uid)

One session per request. Begin on first DB access, commit/rollback at handler exit, close after response sent. The yield keeps the session open across the response serialization.

For background workers / queue consumers: one session per job. Same pattern.

For long-running things (data import scripts): split into batches, commit per batch, optionally close + reopen between batches to release connection pressure.

Don’t share sessions across threads

The Session is NOT thread-safe. Sharing one session across threads = race conditions on identity map / pending state / connection state.

Two safe patterns:

• One session per thread (or scoped_session which gives you a thread-local).
• One session per task in async (Session per asyncio.Task).

Engines and connection pools ARE thread-safe — share one Engine across threads.

Common errors and what they mean

Inspecting the session

inspect(user).persistent          # True if in session and in DB
inspect(user).pending             # True if added but not flushed
inspect(user).detached            # True if session closed / expunged
inspect(user).transient           # True if never added
inspect(user).deleted             # True if marked for delete

session.new                       # set of pending instances
session.dirty                     # set of modified instances
session.deleted                   # set of deletion-marked instances
session.identity_map.keys()       # all (PK, class) keys in the identity map
session.is_active                 # transaction in progress

Common pitfalls

• Forgetting to close the session in a script (no context manager) — connection leaks, eventually pool exhaustion.
• Catching an exception, swallowing it, NOT calling rollback — next session op fails with PendingRollbackError.
• Sharing a session across HTTP requests / threads — race conditions, leaked state, mystery bugs.
• Calling session.commit() after session.close() — error; session is dead.
• Mutating an attribute then commit() and reading it back — works, but with expire_on_commit=True it re-fetches. Hot loops want expire_on_commit=False.

Common interview confusions

• “Session(engine) opens a DB connection.” — lazy. No connection until first statement.
• “commit and close are interchangeable.” — commit finishes the transaction; close ends the session. Closing without committing rolls back pending changes.
• “You always need to call flush before commit.” — commit calls flush internally. You only call flush manually if you need DB-side effects (auto IDs) before committing.

Interview angle

• “What’s the lifecycle of a Session?” — Session(engine) (no resources yet), first statement triggers autobegin + connection acquisition, work, commit() (flush + COMMIT, releases connection), reusable for next transaction, close() finally returns to pool and detaches instances.
• “Difference between session.flush() and session.commit()?” — flush sends pending SQL but doesn’t commit the DB transaction (you can still rollback); commit flushes + commits. Autoflush runs flush before each query.
• “What happens after a session.rollback()?” — pending/dirty changes discarded; loaded objects are expired (next access SELECTs). Transaction is gone; session ready for a new one.
• “What’s begin_nested()?” — issues a SAVEPOINT; inner failures roll back to the savepoint without aborting the outer transaction. For “try this risky operation, keep the rest.”
• “Why can’t you share a Session across threads?” — session state (identity map, pending changes, transaction) is mutable and not synchronized. Use one session per thread or per task.
• “What’s the canonical Session pattern in a web app?” — one session per request, opened via dependency injection / middleware, committed/rolled back at handler exit, closed in finally. Engines and pools are shared.