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?
answer
- a failed flush rolls back everything
- PendingRollbackError until rollback()
- SAVEPOINT per invoice
- except IntegrityError around each block
basics
~20 sA 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.
solid answer
~40 sWhen a flush fails, SQLAlchemy rolls back the **whole** database transaction and leaves the Session inactive: every invoice not yet committed is gone, and further SQL raises `PendingRollbackError` until the code calls `Session.rollback()`. `Session.begin_nested()` fixes this with a `SAVEPOINT`. Used as `with session.begin_nested():` around each insert, it flushes pending state, emits `SAVEPOINT`, and on normal exit flushes again and emits `RELEASE SAVEPOINT`. If the block raises, it emits `ROLLBACK TO SAVEPOINT`, expires only state changed inside it, and re-raises, so an `except IntegrityError` around the `with` records the duplicate while the outer transaction carries on and commits the other 499. The cost is a savepoint round trip and a separate flush per invoice, and `session.commit()` inside a nested block commits the outermost transaction.
code
python · 29 linesfrom sqlalchemy import create_engine, exc, 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), unique=True)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
with Session() as session:
session.add_all([Invoice(number="INV-1"), Invoice(number="INV-1")])
try:
session.flush() # IntegrityError: the whole transaction is rolled back
except exc.IntegrityError:
pass # swallowed, no Session.rollback()
try:
session.scalars(select(Invoice)).all() # any further SQL is refused
except exc.PendingRollbackError as err:
print(type(err).__name__) # PendingRollbackError
session.rollback() # the Session is usable againgo deeper
Recall that a failed flush rolls back the whole transaction, and that Session.rollback() is needed before the Session can be used again.
Explain what begin_nested() emits: a flush, SAVEPOINT, then RELEASE SAVEPOINT on success or ROLLBACK TO SAVEPOINT and a re-raise on failure.
Diagnose PendingRollbackError after a swallowed IntegrityError, place the try around the nested block, and weigh per-row savepoints against a pre-check or a conflict-skipping insert.
Decide how batch jobs treat partial failure: skip and report bad rows with savepoints, or reject the batch, and what per-row round trips cost at the team's data volumes.
## What a failed flush does without a savepoint A SQLAlchemy **`Session`** writes pending objects in a **flush**, which runs inside the Session's transaction. If a statement in the flush fails, for example an `INSERT` that violates a unique constraint on the invoice number, the Session cannot continue that flush partway. It issues a `ROLLBACK` for the **entire** transaction and moves into an **inactive** state: - every invoice inserted earlier in the transaction, and not yet committed, is rolled back with it; - any further SQL on that Session raises **`PendingRollbackError`** ("This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback()."); - the code must call `Session.rollback()` (or close the Session) before it can be used again. Catching the `IntegrityError` and carrying on with the same Session therefore does not work: the error only moves to the next statement. On PostgreSQL there is a second reason, since a failed statement there aborts the transaction until it ends. ## `begin_nested()`: a SAVEPOINT per risky step **`Session.begin_nested()`** begins a nested transaction backed by SQL `SAVEPOINT`. It returns a `SessionTransaction` that works as a context manager: 1. On entry it **flushes** everything pending, unconditionally, then emits `SAVEPOINT`. 2. On normal exit it flushes the block's changes and emits `RELEASE SAVEPOINT`. 3. If the block raises, it emits `ROLLBACK TO SAVEPOINT`, expires the in-memory state changed since the savepoint (state that was not touched is kept), and **re-raises** the exception. The enclosing transaction stays open throughout. The billing loop becomes: ```python with Session() as session, session.begin(): for number in batch: try: with session.begin_nested(): session.add(Invoice(number=number)) except exc.IntegrityError: skipped.append(number) ``` The duplicate's `INSERT` fails when the nested block flushes on exit, so the `except` has to sit around the `with`, not inside it. The savepoint is rolled back, the handler records the number, and the outer `session.begin()` commits the remaining invoices at the end. ## Reading it in the SQL log With `echo=True` the per-invoice pattern is plain to see. A clean invoice logs `SAVEPOINT sa_savepoint_1`, its `INSERT INTO invoice (number) VALUES (?)` and `RELEASE SAVEPOINT sa_savepoint_1`. The duplicate logs `SAVEPOINT sa_savepoint_3`, the failing `INSERT`, then `ROLLBACK TO SAVEPOINT sa_savepoint_3` instead of a release. A single `COMMIT` closes the batch. If the log instead shows a bare `ROLLBACK` after the failing `INSERT`, the insert ran outside any savepoint and the whole batch went with it. ## Details that bite - **The entry flush is outside your guard.** `begin_nested()` flushes pending objects before emitting `SAVEPOINT`. An object added before the nested block that violates a constraint fails at that flush, not inside the block, and takes the whole transaction with it. - **`session.commit()` targets the outermost transaction.** Called inside a nested block, it commits everything, not just the savepoint. Use the `SessionTransaction` from `begin_nested()` (or the `with` block) to release or roll back only the savepoint. - **The database and driver must support SAVEPOINT inside the transaction.** PostgreSQL and MySQL do. On SQLite, Python's built-in `sqlite3` module runs in a legacy transaction mode by default in which a SAVEPOINT does not join the enclosing transaction, so a later ROLLBACK cannot undo a released savepoint; on Python 3.12 and later, `create_engine(..., connect_args={"autocommit": False})` switches that mode off. ## What it costs | Approach | Statements per invoice | On one duplicate | |---|---|---| | one flush for the batch | batched INSERTs | whole batch rolled back | | `begin_nested()` per invoice | SAVEPOINT, INSERT, RELEASE | only that invoice skipped | A savepoint per row also forces a flush per row, so the ORM cannot batch the INSERTs, and every row costs extra round trips. For a large batch that is mostly clean, two alternatives are often cheaper: - check the incoming numbers against the table in one query first and drop the known duplicates; - let the database skip conflicts with an insert that ignores or updates on conflict, written in Core. `begin_nested()` remains the right tool when each row may fail for its own reason and the job must record which ones did.
- In SQLAlchemy, why does a nested savepoint block that raises not leave the Session in the PendingRollbackError state?Because the failure is contained in the nested transaction. When the `with session.begin_nested():` block raises, the `SessionTransaction` emits `ROLLBACK TO SAVEPOINT` and ends only the nested transaction, so the enclosing transaction is still active and the Session can keep emitting SQL. A failed flush outside any savepoint is what rolls back the outermost transaction and requires `Session.rollback()`.
- What happens to objects loaded before a SQLAlchemy savepoint when it is rolled back?Only state modified since the `SAVEPOINT` began is expired; objects left untouched inside the nested block keep their loaded values. The entry flush is what makes this safe: everything pending was written before the savepoint, so any expired object reloads to its pre-savepoint state.
- Catching IntegrityError lets the job keep using the same Session without a rollback.
- A failed flush rolls back only the statement that failed.
- session.commit() inside begin_nested() commits only the savepoint.
- begin_nested() starts a second, independent database transaction.
- A savepoint per row costs nothing compared with one batched flush.