skip to content

Transaction Scope & Commit

sessionmaker builds Sessions whose transaction begins on first use and ends at commit, rollback or close. Interviewers ask what expire_on_commit does and why scoped_session exists.

on this pageshow

explore

questions

5

In SQLAlchemy 2.1, how does `with Session() as session, session.begin():` differ from `with Session() as session:` followed by `session.commit()`?

level: juniorimportance: must knowfreq 72%

answer

  1. two ways to frame one transaction
  2. who calls commit, and when
  3. autobegin on first use
  4. begin() after a query already ran

basics

~20 s

The begin-once form commits when its block succeeds, rolls back and re-raises on an error, then closes the Session. The commit-as-you-go form autobegins on first use, commits only at each session.commit(), and close() rolls back whatever came after.

solid answer

~40 s

Both start from a `sessionmaker(engine)` built once at module level. `with Session() as session, session.begin():` is **begin once**: the `SessionTransaction` that `session.begin()` returns commits when the block ends normally, rolls back and re-raises if it raises, and the outer context then calls `close()`. `with Session() as session:` alone is **commit as you go**: the Session **autobegins** a transaction at its first use, you call `session.commit()` or `session.rollback()` wherever a unit of work ends, the next use autobegins again, and the closing `close()` rolls back anything left uncommitted. One trap: calling `session.begin()` on a Session that has already autobegun, for example after a `select()`, raises `InvalidRequestError` ("A transaction is already begun on this Session."). `sessionmaker.begin()`, written `with Session.begin() as session:`, is shorthand for the begin-once form.

code

python · 41 lines
python
from sqlalchemy import create_engine, select, String, exc
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), unique=True)
    status: Mapped[str] = mapped_column(String(10), default="open")


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(engine)  # configured once, at module level

# Begin once: commit on success, rollback on error, then close
with Session() as session, session.begin():
    session.add(Invoice(number="INV-1"))

# Commit as you go: autobegin on first use, commit when you choose
with Session() as session:
    invoice = session.scalars(select(Invoice).filter_by(number="INV-1")).one()
    invoice.status = "paid"
    session.commit()
    session.add(Invoice(number="INV-2"))  # never committed
# close() at the end of the block rolls INV-2 back

with Session() as session:
    print(session.scalars(select(Invoice.number)).all())  # ['INV-1']

# The trap: begin() after the Session has already autobegun
with Session() as session:
    session.scalars(select(Invoice)).all()
    try:
        session.begin()
    except exc.InvalidRequestError as err:
        print(err)  # A transaction is already begun on this Session.

go deeper

for a junior

Recall both shapes: with Session() as session, session.begin() commits or rolls back for you; with Session() as session plus session.commit() leaves committing to your code, and close() rolls back the rest.

for a middle

Explain autobegin: the transaction starts at first use, a connection is checked out only at the first SQL, and commit() or rollback() leaves the Session ready to autobegin again.

for a senior

Spot the InvalidRequestError raised when begin() follows a query, and pick begin once for all-or-nothing units and commit as you go for checkpointed batch loops.

for a principal

Weigh a team convention: begin once makes boundaries visible and impossible to forget, while commit as you go suits staged batch work; autobegin=False is the stricter option some teams enforce.

## The factory: `sessionmaker` configured once A SQLAlchemy **`Session`** is the ORM object that tracks your mapped objects and talks to the database inside one transaction at a time. You rarely construct it by hand. Instead an application builds a **`sessionmaker`** once, next to its `Engine`, and calls it whenever a unit of work starts: ```python engine = create_engine("sqlite:///billing.db") Session = sessionmaker(engine) # a factory with fixed configuration ``` The `sessionmaker` holds the configuration (`bind`, `autoflush`, `expire_on_commit`, `autobegin` and so on); each call such as `Session()` returns a fresh `Session` with those settings, and keyword arguments passed to the call override them for that one Session. Both the engine and the factory are safe to share; the Sessions they produce are not. ## Begin once ```python with Session() as session, session.begin(): session.add(Invoice(number="INV-1")) ``` Two context managers are stacked here: - `Session()` as a context manager calls **`Session.close()`** when the block exits, whatever happened. - `session.begin()` returns a **`SessionTransaction`**. Its exit **commits** if the block finished normally; if an exception escaped, it **rolls back** and lets the exception continue upward. It never swallows the error. The whole block is therefore one transaction with a guaranteed outcome. `sessionmaker.begin()` packs the same thing into one call: `with Session.begin() as session:` creates a Session, begins, commits or rolls back, and closes. ## Commit as you go, and autobegin ```python with Session() as session: invoice = session.scalars(select(Invoice).filter_by(number="INV-1")).one() invoice.status = "paid" session.commit() session.add(Invoice(number="INV-2")) # never committed ``` The Session has **autobegin**: a new Session holds no transaction, and the first operation that needs one (an `execute()`, a `scalars()`, even an `add()`) begins it. A database connection is checked out from the engine's pool only when SQL is actually emitted. After `commit()` or `rollback()` the Session has no transaction again, and the next use autobegins a new one. When the `with` block ends, `close()` rolls back whatever is still open, so `INV-2` above is never written. ## Side by side | | begin once | commit as you go | |---|---|---| | Who commits | the `session.begin()` context, on normal exit | your code, at each `session.commit()` | | On an exception | rollback, then re-raise | nothing automatic until `close()` rolls back | | Transactions per block | exactly one | as many as you commit | | Typical use | one unit of work, all or nothing | a loop or script that commits in stages | ## The trap: `begin()` after autobegin `Session.begin()` starts the **outermost** transaction, and the Session allows only one. If the Session has already autobegun, because a query ran or an object was added, `session.begin()` raises `InvalidRequestError` with the message "A transaction is already begun on this Session." The usual cause is code that queries first and wraps the writes in `with session.begin():` afterwards. The fixes: 1. Open the transaction first: `with Session() as session, session.begin():` around the reads and the writes. 2. Or stay with commit as you go and call `session.commit()` at the end. 3. For a nested scope inside a running transaction, use `session.begin_nested()`, which is a SAVEPOINT, not a second outer transaction. A Session built with `autobegin=False` reverses the default: it never begins by itself, and any use before an explicit `session.begin()` raises `InvalidRequestError`. ## What happens, in order, when a begin-once block fails 1. The exception leaves the body of the block. 2. The `SessionTransaction` exit sees it and calls rollback: a `ROLLBACK` is emitted if a connection was in use, pending objects are expunged and the rest are expired. 3. The exception continues outward; the transaction context does not suppress it. 4. The outer Session context runs `close()`, which releases the connection to the pool. The caller therefore sees the original error, and the database sees none of the block's writes. ## Choosing between them in a billing job A command-line billing job that settles one customer's invoices as a single all-or-nothing unit reads best as begin once: the code cannot forget to commit, and a failure cannot leave half the invoices written. A job that walks thousands of invoices and wants to keep progress every hundred rows suits commit as you go, with `session.commit()` at each checkpoint. Either way: - build the `sessionmaker` once and open a new `Session` per unit of work; - keep the `with Session() as session:` block, so `close()` always runs and the connection always returns to the pool; - never rely on garbage collection to end a transaction.

  • Does constructing a SQLAlchemy Session open a database connection?
    No. A new Session holds no transaction and no connection. Its transaction autobegins at the first operation that needs one, even `session.add()`, but a connection is checked out from the engine's pool only when SQL is actually emitted, for example at a query or a flush. Creating Sessions is cheap; holding one open across a slow step is what ties up a pooled connection.
  • What does `autobegin=False` change on a SQLAlchemy Session?
    With `Session(engine, autobegin=False)`, or the same flag on `sessionmaker`, the Session never begins a transaction by itself. After construction and after every `commit()`, `rollback()` or `close()`, code must call `session.begin()` before using it again; otherwise `InvalidRequestError` reports that autobegin is disabled. It makes every transaction boundary explicit, at the cost of more ceremony.

  • Leaving a with Session() block commits pending changes automatically.
  • session.begin() simply starts another transaction even after a query already ran.
  • A Session opens a database connection as soon as it is constructed.
  • In SQLAlchemy 2.x you must call session.begin() before every query.
  • The session.begin() context manager swallows the exception after rolling back.
open as a page

What does SQLAlchemy's `expire_on_commit=True` default do to a Session's objects after commit, and what does it cost the code that reads them?

level: middleimportance: must knowfreq 62%

basics

~20 s

Session.commit() expires every object it holds, erasing the loaded values. The next attribute read autobegins a transaction and SELECTs that row again, so reads after commit cost extra queries, and objects from a closed Session cannot reload.

open as a page

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%

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.

open as a page

Why does SQLAlchemy provide `scoped_session`, and what must a thread-pool worker call on it when each task finishes?

level: middleimportance: should knowfreq 45%

basics

~20 s

A Session must not be shared across threads; scoped_session is a registry that gives each thread its own Session behind one global object. A pool worker calls remove() after each task to close and discard that Session.

open as a page

A SQLAlchemy billing job inserts 500 invoices in one transaction and one duplicate number raises IntegrityError; how does `Session.begin_nested()` let it keep the other 499?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A failed flush rolls back the whole transaction and the Session refuses SQL until rollback(). Wrapping each insert in with session.begin_nested() puts it behind a SAVEPOINT, so only the duplicate is rolled back and the rest commit.

open as a page