skip to content

In SQLAlchemy 2.1, what do `Session.commit()`, `Session.rollback()` and `Session.close()` each do to the connection and to the objects the Session holds?

level: middleimportance: should knowfreq 48%

answer

  1. three ways a transaction ends
  2. connection back to the pool
  3. pending objects after a rollback
  4. close is a reset, not a shutdown

basics

~20 s

All three end the transaction and release the connection to the pool. commit() flushes, commits and expires objects; rollback() expunges the transaction's new objects and expires the rest; close() rolls back and detaches every object.

solid answer

~40 s

`Session.commit()` always flushes pending changes, emits `COMMIT`, releases the connection back to the engine's pool and, with the default `expire_on_commit=True`, expires every object, which stays in the Session. `Session.rollback()` emits `ROLLBACK`, releases the connection, expunges objects that became pending during that transaction (they keep their attribute values), restores objects deleted in it to persistent, and expires everything that remains, whatever `expire_on_commit` says; with no transaction in progress it does nothing. `Session.close()` calls `expunge_all()`, so every object becomes **detached** but keeps the values it had loaded, and returns the connection with any open transaction rolled back. A closed Session is reset, not destroyed: it can be used again and will autobegin a new transaction, unless it was built with `close_resets_only=False`.

code

python · 40 lines
python
from sqlalchemy import create_engine, inspect, select, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker


class Base(DeclarativeBase):
    pass


class Invoice(Base):
    __tablename__ = "invoice"
    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[str] = mapped_column(String(20))
    status: Mapped[str] = mapped_column(String(10), default="open")


engine = create_engine("sqlite:///billing.db")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(engine, expire_on_commit=False)

with Session.begin() as session:  # sessionmaker.begin(): begin once, then close
    session.add(Invoice(number="INV-1"))

session = Session()
paid = session.scalars(select(Invoice)).one()  # autobegin: a connection is checked out
print(engine.pool.checkedout(), session.in_transaction())  # 1 True

paid.status = "void"
draft = Invoice(number="INV-2")
session.add(draft)
session.flush()
session.rollback()  # ROLLBACK, connection released
print(engine.pool.checkedout(), session.in_transaction())  # 0 False
print(inspect(draft).transient)  # True: the pending INSERT was undone, draft expunged
print(paid.status)  # 'open': expired by rollback despite expire_on_commit=False

session.close()  # expunge_all(): paid is now detached, but its loaded values stay
print(inspect(paid).detached, paid.status)  # True open
print(session.scalars(select(Invoice.number)).all())  # ['INV-1']: the Session is reusable
session.close()

go deeper

for a junior

Recall that commit, rollback and close all end the transaction and hand the connection back to the pool, and that only commit makes the changes permanent.

for a middle

Explain the object side: commit expires but keeps objects, rollback expunges new ones and expires the rest whatever expire_on_commit says, close detaches everything without expiring it.

for a senior

Use these rules to debug leaked connections, objects that vanish after a rollback and detached objects that cannot reload, and to place commit, rollback and close in batch code.

for a principal

Judge whether reusable Sessions or close_resets_only=False suit a codebase, and set a rule that every Session has a guaranteed end so no transaction outlives its unit of work.

## The Session's transaction and its connection A SQLAlchemy **`Session`** holds at most one outermost transaction at a time, represented by a `SessionTransaction`. That transaction **autobegins** at first use, and the Session checks out a **connection** from the `Engine`'s pool when it first emits SQL. Three methods end the transaction: `commit()`, `rollback()` and `close()`. All three release the connection, so the pool gets it back as soon as the transaction ends, not when the Session object is garbage collected. What differs is what happens in the database and to the ORM objects the Session holds. ## `Session.commit()` 1. **Flushes unconditionally.** Pending inserts, updates and deletes are written first, whatever the `autoflush` setting. If nothing is pending, no SQL is emitted for the flush. 2. **Emits `COMMIT`** on the connection (on each connection, for a Session bound to several engines). 3. **Releases the connection** to the pool. 4. **Expires every object** when `expire_on_commit=True`, the default, so the next attribute read reloads from the database. The objects remain **persistent**, still in the Session. Calling `commit()` with no transaction in progress begins and commits an internal logical transaction that touches the database only if a flush finds changes; commit events and the expiry rules still run. ## `Session.rollback()` With a transaction in progress, rollback: - emits **`ROLLBACK`** and **releases the connection**; - **expunges** objects that were added (pending) during this transaction, since their INSERTs were undone; they become transient again and keep their attribute values; - returns objects **deleted** during the transaction to the persistent state; - **expires every object that remains**, regardless of `expire_on_commit`, because their in-memory values may describe changes that never happened. With no transaction in progress, `rollback()` passes silently. After a failed flush, rollback is also the call that makes the Session usable again. ## `Session.close()` `close()` calls **`expunge_all()`**: every object leaves the Session and becomes **detached**. Detached objects keep whatever values they had loaded; `close()` does not expire them. Any transaction still open is ended by the connection's return to the pool, which rolls it back. The Session itself is then back in the state it had when constructed and **can be used again**; its next operation autobegins a new transaction. In this default mode `Session.reset()` is an alias of `close()`. A Session constructed with `close_resets_only=False` instead refuses further use after `close()`, raising `InvalidRequestError`, while `reset()` still allows reuse. ## Side by side | | `commit()` | `rollback()` | `close()` | |---|---|---|---| | Flush first | always | no | no | | Database | `COMMIT` | `ROLLBACK` | open transaction rolled back | | Connection | released | released | released | | Objects | stay, expired by default | new ones expunged, rest expired | all expunged, detached, values kept | | Session afterwards | reusable, autobegins | reusable, autobegins | reusable (default), autobegins | ## Edge cases worth knowing - **Several engines.** A Session bound to more than one engine commits or rolls back each connection in turn; those database transactions are not coordinated unless two-phase commit is enabled. - **No transaction in progress.** `rollback()` does nothing, while `commit()` still runs its expiry rules and commit events through an internal logical transaction. - **A failed flush.** The database transaction is already rolled back, but the Session refuses further SQL until code calls `rollback()` or `close()`, so the end of the transaction is always an explicit step. - **Bound to an external Connection.** A Session given a `Connection` that already holds a transaction does not emit a real `COMMIT` itself; how it behaves there is a test-harness concern. ## How a billing job uses them A job that settles invoices typically commits at the end of each unit of work, rolls back in an `except` branch, and closes in all cases, which is exactly what `with Session() as session, session.begin():` does for one unit. The table explains the behaviour it then sees: - a variable holding an invoice after `commit()` reloads on next access, while one held after `close()` keeps its last values but can no longer load anything new; - after a rollback, an invoice added in that transaction is no longer in the Session, so adding it again is how a retry would re-insert it; - because every one of the three releases the connection, a Session that sits idle after commit holds no pooled connection until its next statement. A Session that is never committed, rolled back or closed is the one that keeps a connection, and an open transaction, for as long as it lives.

  • Why can a SQLAlchemy object still be read after `Session.close()` but not after `commit()` followed by `close()`?
    `close()` only expunges objects; it does not expire them, so a detached object keeps the values it had loaded. With the default `expire_on_commit=True`, `commit()` expires every object first, and if the Session is closed before anything reloads them, the detached objects have no values and no Session to fetch them, so the attribute read raises an error.
  • What does `close_resets_only=False` change on a SQLAlchemy Session?
    By default `close()` resets the Session so it can be used again, and `reset()` is an alias. With `close_resets_only=False`, `close()` closes it permanently: later use raises `InvalidRequestError` saying the Session has been permanently closed, while `reset()` remains the way to clear it for reuse. It suits code that wants a closed Session to stay closed.

  • close() commits whatever the Session has pending before closing.
  • A Session cannot be used again after close() under default settings.
  • rollback() leaves objects added in that transaction pending, ready to retry.
  • The connection returns to the pool only when close() is called.
  • close() expires all objects, just like commit().