Your SQLAlchemy Savepoint Starts After the Pending Flush

An inventory adjustment appears to roll back correctly: the candidate row disappears, the exception is handled, and the application continues. Yet an earlier quantity change remains. Before diagnosing a failed rollback or an unexpected commit, identify which transaction boundary actually protected each write.

The fixture starts with committed row A at quantity 10. The writer changes A to 20 in Python, opens Session.begin_nested(), inserts candidate B, and deliberately rejects it. SQLAlchemy's documented savepoint contract requires pending ORM state to flush before the savepoint, even with autoflush=False. A's update therefore precedes the boundary that removes B.

That earlier update is outside the nested rollback scope, but it has not thereby been committed. The surviving outer transaction can still commit it or roll it back. Both decisions must be tested; B's disappearance alone cannot distinguish them.

This playbook follows three layers of evidence: pending Python object state, SQL order around SAVEPOINT, and final rows after the outer transaction ends. The supplied preparation results were independently reproduced with the unchanged five-case driver on CPython 3.13.5, SQLAlchemy 2.0.50, and SQLite 3.46.1. These are measured reference versions, not a newest-release recommendation. The database is disposable and file-backed, sessions are synchronous, and the observer reads only after the writer finishes. No concurrent-visibility claim follows from that observation.

Identify the rollback promise that failed

The business promise sounds straightforward: try an adjustment, and leave none of that adjustment behind if it is rejected. The difficulty is deciding whether “the adjustment” includes only B or includes both A's quantity change and B. A savepoint cannot resolve an ownership ambiguity that the application has not defined.

Use stable identities from the beginning. A is id=1, code='A', initially quantity=10. B is id=2, code='B', with candidate quantity 99. Assigning A's quantity to 20 before entering the nested context creates pending work in the surrounding Session. The candidate exception is raised later, after B has been flushed.

For the broader promise, both the update of A and the insertion of B must fall within the rollback boundary. For a narrower, explicitly authorized B-only operation, A can belong to the outer transaction. Those are different contracts. Choosing between them after seeing the output would make the acceptance test circular.

Do not confuse this diagnosis with driver-level pending-transaction commits. There is no executescript() call in this fixture. The decisive control here is that an outer rollback still restores A to 10. Explaining the result as an earlier commit would contradict that control.

Write down the promise before running the candidate. Name the caller that owns the outer transaction, identify any authorized earlier work, and state whether A belongs to the candidate. An unexpectedly dirty Session is an admission problem, not permission to clean up another component's changes.

A savepoint is not a fresh, independent transaction or an empty workspace. The candidate continues within the surrounding transaction and can be affected by the state established there. In this example, the pre-savepoint value for A is 20, not the original committed 10. Define the rollback promise against the correct starting point rather than assuming each nested operation restarts from committed data.

A useful rejection report therefore answers more than “Was B removed?” It records A's identity and quantity, whether the candidate body was entered, and which owner made the final transaction decision. Missing ownership information warrants HOLD, even when the SQL looks plausible.

Pin the session, driver, and transaction environment

Each case creates lab.db inside a new TemporaryDirectory whose prefix is savepoint-lab-. Schema creation and the committed seed row precede the measured writer activity. The database is not shared with a real application, and no case inherits another case's rows or pending transaction.

The measured stack is CPython 3.13.5, SQLAlchemy 2.0.50, and SQLite 3.46.1. Record all three on each run. sqlite3.sqlite_version identifies the SQLite library used by the Python module; installing a particular SQLAlchemy package does not pin that library or the Python interpreter. Select the intended interpreter and verify its output rather than treating the installation command as a complete environment specification.

A file-backed database keeps the writer and the later observer tied to the same named database without making the experiment depend on the lifetime or sharing rules of an in-memory connection. This is an experimental choice, not a claim that in-memory tests cannot be designed correctly. Keep the database path in the run context when adapting the fixture; its temporary directory is removed when the case finishes.

The engine explicitly passes connect_args={'autocommit': False}. SQLAlchemy's SQLite transaction-control documentation documents this configuration for the Python 3.12-and-later sqlite3 interface. Leaving the driver in an implicit legacy mode would introduce a second transaction-control question into a test intended to isolate ORM behavior.

Readers needing broader context can use the backend persistence foundations roadmap. This experiment assumes familiarity with loading a mapped row and changing an attribute; it does not reproduce a general ORM curriculum.

Separate driver transaction control from ORM autoflush

sqlite3.Connection.autocommit=False and Session(autoflush=False) operate at different layers. The first controls the database driver's transaction behavior. The second suppresses configurable ORM autoflush, including relevant query-triggered flushes. It does not suppress the mandatory flush when a nested transaction begins.

Python's autocommit transaction-control explanation also describes opening another transaction after commit or rollback in this mode. Consequently, a driver-level transaction flag is not, by itself, evidence that the writer's previous unit of work survived. Evaluate the decision and the rows together.

Keep the run self-identifying when reproducing a failure in continuous integration. Store the environment fields beside the case ledger, preserve the exact script revision, and identify the temporary database path outside the reader-facing evidence. A filename such as savepoint-evidence.json is merely an output name; without its recorded stack and options, it does not establish which environment produced the result.

Keep documentation versions separate from measured package versions. The consulted SQLAlchemy 2.0 transaction page displayed a later patch header than 2.0.50. The documentation supplies the series-level contract; the reproduction supplies the tested patch. Neither should be silently relabelled to match the other.

Build the inventory fixture and capture its SQL

Save the complete source below as savepoint_lab.py in an owned working directory. Use an isolated environment for package installation. With the reference interpreter and SQLite library selected, run the two commands shown. Do not use Python's optimized mode: the driver uses assertions as its diagnostic checks.

python -m pip install "SQLAlchemy==2.0.50"
python savepoint_lab.py > savepoint-evidence.json

import json
import platform
import sqlite3
import tempfile
from pathlib import Path

import sqlalchemy as sa
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.exc import IntegrityError

class Base(DeclarativeBase):
    pass

class Stock(Base):
    tablename = 'stock'
    id: Mapped[int] = mapped_column(primary_key=True)
    code: Mapped[str] = mapped_column(sa.String, unique=True)
    quantity: Mapped[int]

class RejectCandidate(Exception):
    pass

def rows(session):
    return [list(r) for r in session.execute(
        sa.select(Stock.id, Stock.code, Stock.quantity).order_by(Stock.id)
    )]

def run(case, pending_first=True, finish='commit', no_autoflush=False,
        bad_pending=False):
    with tempfile.TemporaryDirectory(prefix='savepoint-lab-') as tmp:
        engine = sa.create_engine(
            'sqlite:///' + str(Path(tmp) / 'lab.db'),
            connect_args={'autocommit': False},
        )
        Base.metadata.create_all(engine)
        with Session(engine) as seed, seed.begin():
            seed.add(Stock(id=1, code='A', quantity=10))
        events = []
        @sa.event.listens_for(engine, 'before_cursor_execute')
        def sql(conn, cursor, statement, parameters, context, executemany):
            events.append({'sql': ' '.join(statement.split()),
                           'parameters': repr(parameters)})
        @sa.event.listens_for(engine, 'commit')
        def commit(conn):
            events.append({'event': 'COMMIT'})
        @sa.event.listens_for(engine, 'rollback')
        def rollback(conn):
            events.append({'event': 'ROLLBACK'})
        with Session(engine, autoflush=False) as s:
            outer = s.begin()
            item = s.get(Stock, 1)
            if bad_pending:
                s.add(Stock(id=3, code='A', quantity=30))
            elif pending_first:
                item.quantity = 20
            admission = {'new': len(s.new), 'dirty': len(s.dirty),
                         'deleted': len(s.deleted)}
            entered_body = False
            try:
                from contextlib import nullcontext
                with (s.no_autoflush if no_autoflush else nullcontext()):
                    with s.begin_nested():
                        entered_body = True
                        if not pending_first:
                            item.quantity = 20
                        s.add(Stock(id=2, code='B', quantity=99))
                        s.flush()
                        raise RejectCandidate('inert rejection')
            except RejectCandidate:
                after_nested = rows(s)
                assert after_nested == [[1, 'A', 20 if pending_first else 10]]
                if finish == 'commit':
                    outer.commit()
                else:
                    outer.rollback()
            except IntegrityError:
                assert bad_pending and not entered_body and not s.is_active
                after_nested = 'outer session inactive; no query attempted'
                s.rollback()
            sql_before_observer = list(events)
        with Session(engine) as observer:
            final = rows(observer)
        expected = 20 if pending_first and finish == 'commit' and not bad_pending else 10
        assert final == [[1, 'A', expected]]
        stmts = [x['sql'] for x in sql_before_observer if 'sql' in x]
        savepoints = [i for i, x in enumerate(stmts) if x.startswith('SAVEPOINT')]
        if bad_pending:
            assert not savepoints
        else:
            upd = next(i for i, x in enumerate(stmts) if x.startswith('UPDATE'))
            assert (upd < savepoints[0]) == pending_first
            assert any(x.startswith('ROLLBACK TO SAVEPOINT') for x in stmts)
        out = dict(case=case, before=admission, entered_body=entered_body,
                   after_nested=after_nested, final=final,
                   evidence=sql_before_observer)
        engine.dispose()
        return out

if name == '__main__':
    print(json.dumps({'python': platform.python_version(),
                      'sqlalchemy': sa.__version__,
                      'sqlite': sqlite3.sqlite_version,
                      'driver_autocommit': False,
                      'session_autoflush': False,
                      'cases': [run('S1 preflush then outer commit'),
                                run('S2 preflush then outer rollback', finish='rollback'),
                                run('S3 mutation inside savepoint', pending_first=False),
                                run('S4 no_autoflush is not a repair', no_autoflush=True),
                                run('S5 invalid pending object', bad_pending=True)]},
                     indent=2))

The reproduction completed all five cases with their assertions satisfied. Its JSON recorded Python 3.13.5, SQLAlchemy 2.0.50, SQLite 3.46.1, driver_autocommit=False, and session_autoflush=False. These new execution results agree with the supplied preparation ledger, but remain evidence from a synthetic fixture, not a customer incident.

The SQL listener is installed after seeding. Its before_cursor_execute callback records statements and parameters prepared for cursor execution; the separate commit and rollback listeners record SQLAlchemy transaction events. The connection-event interfaces distinguish those hooks. A before-execution entry is evidence of an attempted statement, not proof that the statement succeeded. Likewise, a commit event alone is not a completed-commit receipt. Normal completion and the final reader supply the additional evidence here.

The saved writer ledger is copied before the observer opens. Observer queries and its closing transaction activity cannot therefore be mistaken for candidate SQL in that saved list. This instrumentation does not capture every operation performed internally by the driver, such as an automatically issued BEGIN; it is not a complete wire-level trace.

The neighboring SQLite connection-level enforcement checks concern a different admission layer. Here the connection mode is fixed, and the object of inspection is pending ORM work before a savepoint.

The JSON is written only after the case list has been evaluated. A process error or an incomplete output file is therefore not a successful five-case result. Preserve standard error and the exit status during an operational rerun, and inspect each named case rather than accepting the existence of the output filename. Do not reuse an older successful ledger after a new run fails.

Keep both error paths in the source. Deliberate candidate rejection and pre-entry uniqueness failure mean different things. On the expected paths, sessions close, the engine is disposed, and the temporary directory is removed. The preserved driver is a diagnostic fixture, not a production cleanup framework: an unexpected exception before engine.dispose() can bypass that call. Do not treat it as a general error-management wrapper.

Reproduce an UPDATE emitted before SAVEPOINT

S1 opens an outer transaction and loads A. The assignment item.quantity = 20 changes tracked Python state; it does not immediately issue the inventory update. At the admission checkpoint, the reproduced counts are new=0, dirty=1, and deleted=0.

That checkpoint is deliberately before begin_nested(). Inspecting the collections only after entering the nested context would miss the pending state that its mandatory flush has already processed. An apparently clean Session at the later checkpoint would not explain where the update was placed.

Entering the nested operation processes A's pending change. B is then added inside the nested context, s.flush() sends its insertion, and RejectCandidate triggers nested rollback. The important evidence is the order of the writer's statements, not the visual grouping of nearby Python lines.

The table abbreviates the SELECT and DML text for readability; the JSON contains the full normalized statements and parameter representations. Do not paste the table into a console as an executable SQL script.

Read the trace as an ordered boundary ledger

The SQL sequence supports a precise statement: A's update is earlier than the SQL savepoint, while B's insertion is later. It does not support “A was committed before the savepoint.” No such commit separates those statements in this run, and S2 tests the opposite outer decision.

Keep the logical Session boundary separate from the instant a SQL statement reaches the connection. Creating a nested Session scope and emitting its database SAVEPOINT need not occur at the same Python line. In the controlled path, the required order is that pre-existing work flushes before SAVEPOINT and candidate DML follows it. Do not infer SQL timing from context entry alone.

Transaction-event names also need context. ROLLBACK TO SAVEPOINT is a SQL statement undoing a nested portion. The outer ROLLBACK event in S2 terminates the enclosing writer transaction. A search that merely finds the word “rollback” would conflate them.

The initial seed is absent from this ledger by design. Preserve that exclusion when reviewing the trace, but do not omit earlier writer statements. A truncated log starting at SAVEPOINT could hide the very UPDATE this test is meant to expose.

Show what the nested rollback actually removes

After rejecting B in S1, the driver queries through the same Session while the outer transaction remains usable. The reproduced result is [[1, 'A', 20]]. B is absent. A retains quantity 20 because that quantity was already the database state within the outer transaction when the savepoint was established.

SQLite's savepoint rollback rules distinguish rolling back to a named marker from cancelling the enclosing transaction. In this fixture, the named rollback does exactly the limited job requested of it. The mismatch is between that job and an application promise that mistakenly includes A.

S3 changes the placement. It reaches admission with no new, dirty, or deleted instances. The program enters the nested Session context, assigns A=20 there, adds B, and flushes. The recorded database sequence is SAVEPOINT, UPDATE A, INSERT B, and ROLLBACK TO SAVEPOINT. Both candidate writes follow the same SQL marker.

The distinction about timing matters here. The trace proves SAVEPOINT precedes candidate SQL, not that it necessarily precedes the Python attribute assignment. The corrected workflow depends on assigning candidate state inside the nested Session scope and observing the resulting SQL order, rather than forcing an unsupported claim about eager SQL emission.

S3's post-rejection query returns [[1, 'A', 10]]. Its outer commit leaves that same row. A's quantity and B's membership now match the intended all-candidate rollback scope. S1 and S3 each leave one row, but that count conceals the important difference between quantities 20 and 10.

Do not generalize the result into a promise that every Python object is blindly reset. SQLAlchemy documents expiration of persistent state changed within the nested scope so that it can be reloaded appropriately; object lifecycle details differ between existing and newly added instances. The fixture's rows() query selects column values, rather than treating a cached attribute display as the row oracle.

The same-session query is deliberately an intermediate checkpoint. Reading A=20 there in S1 establishes what remains available to the outer transaction after the rejection. It does not grant that value a final disposition. Conversely, reading A=10 in S3 is meaningful only alongside evidence that both candidate writes actually occurred and were followed by nested rollback, rather than never being attempted.

For a larger adaptation, identify every value that belongs to the candidate before running the test. A check that names only B can demonstrate B's rejection while entirely missing A's unwanted survival. The boundary contract needs row identities, expected values, and write ownership, not just a success flag or a count.

Prove that flush is not commit

S2 is the durability control. It repeats S1's initial row, pending assignment, mandatory pre-savepoint UPDATE, insertion of B, and nested rejection. Immediately after the nested rollback, the same-session query still returns A=20. Up to that point, S1 and S2 have the same meaningful boundary evidence.

Only the outer decision changes. S1 calls outer.commit(); S2 calls outer.rollback(). Once the writer finishes, a separate Session reads A=20 in S1 and A=10 in S2. B is absent in both. The independent reproduction confirmed those exact final values.

Keep the initial committed state identical across the pair. Resetting A by a separate corrective UPDATE between observations would introduce another explanation for the final 10 and would no longer demonstrate rollback of the original writer transaction. The fixture avoids that ambiguity by creating and seeding a fresh disposable database for each case, then changing only the specified outer decision.

This comparison rules out the explanation that the pre-savepoint flush itself made A durable. A change already committed in the way alleged would not be undone by this later rollback of the still-open writer transaction. The ORM data-manipulation tutorial explains flush as sending pending changes into the current transaction, which remains open until its lifecycle is completed.

The operational wording should therefore be: A was flushed before SAVEPOINT, so nested rollback could not undo it; outer rollback still could. “A survived rollback” is too vague because it omits which rollback. “The savepoint committed A” is wrong for these observations.

The final decision belongs to the outer transaction owner. A candidate helper may report that its own work was rejected, but it has not acquired permission to commit the remaining work. A plausible inventory quantity, a handled exception, or a successfully exited nested context is not that authorization.

SQLAlchemy 2.0's nested-transaction handle rules also matter when editing the example. Session.commit() addresses the outermost Session transaction. Use the nested transaction handle or its context manager for savepoint completion; do not substitute a Session-level commit for a savepoint operation.

Use the outer decision as the durability control

The observer starts after the writer transaction ends. It verifies final database contents under an orderly completed run, not concurrent isolation behavior, cross-process visibility, or resilience to a power failure. A separate Session also does not, by itself, prove a newly created physical connection; the engine may reuse a pooled connection after the writer releases it.

Neither limitation undermines this comparison. Both observers read the same file after the corresponding outer decision. The experiment needs that final-state check, while a claim about what another client could see during the writer transaction would require a different schedule and additional evidence.

Reject no_autoflush as the proposed repair

S4 wraps the operation in with s.no_autoflush: while retaining the pending A=20 assignment before entry. The Session already has autoflush=False, so the wrapper adds another explicit demonstration of the proposed suppression mechanism rather than changing write ownership.

Its reproduced boundary is the same as S1: dirty A at admission, UPDATE before SAVEPOINT, B inserted after SAVEPOINT, nested rejection, and A=20 still visible inside the outer transaction. The outer commit leaves only A=20 in the later observer.

The Session Basics flushing rules distinguish configurable autoflush from flushes required at transaction boundaries. begin_nested() remains a boundary flush even when ordinary autoflush is disabled. This result matches the documented behavior; it is not evidence of a SQLAlchemy defect.

no_autoflush can be useful when an intermediate ORM query must not flush incomplete work. That is a different requirement from placing all candidate work after a savepoint. Preventing one possible earlier query-triggered flush does not prevent the boundary flush that this operation performs.

Compare S1 and S4 statement by statement, including parameters and the final transaction event. Comparing only the presence of no_autoflush in source code would test configuration syntax, not the proposed repair. Comparing only the final quantity would miss the explanation for why the supposed repair failed.

Keep the separate MySQL implicit-commit problem outside this diagnosis. No schema-changing command intervenes in the measured candidate sequence. The setup creates a table before measurement, but the boundary under investigation consists of ordinary inventory writes and savepoint operations. S2 remains the control showing that the earlier update is still reversible by the outer transaction.

The repair must address placement or ownership. Move candidate changes into the nested scope, reject an unexpected pending unit of work before entry, or redesign the operation around a separately owned transaction. Do not relabel no_autoflush as successful merely because B still disappears. That was already true in the failing interpretation.

An application that cannot determine who owns A's pending change should stop at HOLD. Adding more autoflush suppression does not supply the missing authorization, and it does not make the savepoint reach backwards over previously emitted SQL.

Move candidate changes inside the intended boundary

When A=20 belongs to the candidate, S3 is the minimal placement repair. Load A without changing it, inspect pending state, then enter the nested Session scope before constructing any candidate mutation. Set A to 20 and add B only within that scope.

Qualify the repair by its evidence, not by the new indentation alone. Admission must match the declared policy; candidate UPDATE and INSERT must follow SAVEPOINT in the trace; nested rejection must restore A=10 and remove B; the final observer must agree after the authorized outer commit. S3 reproduced that complete sequence.

A separately owned Session is an alternative design when candidate work cannot be isolated within the caller's unit of work. It is not automatically a drop-in repair. Depending on binding and configuration, it may use another transaction or connection; it does not create shared atomicity with the caller merely by existing. Decide whether changing that ownership model preserves the application's business contract.

Qualify a conservative pending-state admission check

The following policy example refuses any currently new, dirty, or deleted instance before a candidate-only operation. Call it immediately before begin_nested(), before creating candidate mutations. It does not commit, roll back, detach, or silently discard the state it finds.

class CandidateAdmissionError(RuntimeError):
    pass

def require_clean_pending_state(session: Session) -> None:
    pending = {
        "new": len(session.new),
        "dirty": len(session.dirty),
        "deleted": len(session.deleted),
    }
    if any(pending.values()):
        raise CandidateAdmissionError(
            f"candidate savepoint refused; pending ORM state={pending}"
        )

The five-case driver records admission counts rather than enforcing this guard: otherwise S1, S2, S4, and S5 could not demonstrate their diagnostic paths. Treat the guard as a separate admission policy, not an explanation for why those cases pass.

The Session API's dirty-state description explicitly permits dirty entries without net changes. Assigning an already loaded value back to itself can therefore produce a conservative refusal even when a flush would emit no UPDATE. Count pending instances as a signal, not as a prediction of the number of SQL statements.

An empty pending set proves even less about transaction history. An earlier successful flush can leave the collections empty while its uncommitted UPDATE remains in the outer transaction. This check cannot certify that no SQL was previously emitted or that no earlier writes belong to another component.

Session.is_modified() can help classify net attribute changes, but a richer admission rule needs its own tests, particularly around unloaded or expired values. The policy here intentionally prefers a reviewable refusal over silently assuming that an unexpected object is harmless. Ownership and transaction-history checks remain separate responsibilities.

Handle a flush failure before the nested body begins

S5 adds Stock(id=3, code='A', quantity=30) before calling begin_nested(). The committed row already uses code A, so this pending insertion conflicts with the mapped uniqueness constraint. A's quantity is not changed in this case. Admission records new=1, dirty=0, and deleted=0.

The mandatory pre-entry flush attempts the duplicate insertion. In the reproduction, IntegrityError occurs before the nested body is reached, entered_body remains false, and the writer trace contains no SAVEPOINT. Candidate B has not been constructed. Calling this “B rejected inside the savepoint” would misidentify both the failing write and the active boundary.

The recorded SQL is the initial SELECT, the attempted INSERT with (3, 'A', 30), and an outer rollback event. The attempted INSERT in a before-execution log is not evidence that row 3 persisted. The independent final reader returns only [[1, 'A', 10]].

A second distinction is essential: database rollback and Session recovery are not interchangeable events. The failed-flush recovery requirement says that the underlying transaction has already been rolled back, but the Session still requires an explicit Session.rollback() before it can be reused. The driver checks not s.is_active, avoids querying in that inactive state, and calls s.rollback().

This corrects a dangerous recovery interpretation. Holding for an owner after this failure does not preserve all earlier flushed work in the database transaction: the failed-flush path has already rolled that transaction back. Ownership review is still necessary to determine how the application reports failure, rebuilds work, and completes Session cleanup, not to pretend the old transaction is still safely waiting for a commit decision.

In the disposable fixture, the test owns the outer lifecycle and performs that cleanup immediately. S5 then closes the writer and checks final rows through another Session. That final reader does not, by itself, demonstrate successful reuse of the same Session; the documented recovery contract is a distinct source of that expectation.

Do not treat is_active as a complete ownership or durability report. In S5 it is useful because it is checked immediately after a known flush failure. The exception, the attempted duplicate parameters, the absence of SAVEPOINT, and the unreached body establish the failure path together. Removing those contextual checks would turn a useful lifecycle signal into an unsupported diagnosis.

Operational logs should identify the pending object's owner and the boundary reached. Catching every IntegrityError around a nested operation as an ordinary candidate rejection would hide pre-entry failures. Stop further database work on an inactive Session, notify the outer owner, and rebuild only under an explicit recovery decision. A blind retry in that same failed lifecycle is not a candidate-only repair.

Choose ACCEPT, REPAIR, ROLLBACK, or HOLD

A successful SQL call does not establish an acceptable business boundary. The admission decision combines declared ownership, pending-state inspection, statement order, nested outcome, outer outcome, and final rows. Fix the intended scope before interpreting any case.

S3 qualifies as ACCEPT for the A-plus-B candidate promise. S1 and S4 do not: their successful outer commits preserve A's pre-savepoint update. Their diagnostic assertions pass precisely because they reproduce the unsafe interpretation. A passing diagnostic case is not equivalent to an accepted application policy.

S2 qualifies the deliberate outer-rollback recovery path and proves that preflush was not commit. S5 qualifies a different failure class requiring outer Session recovery. Neither case should be relabelled as a successful candidate-only rollback simply because the final quantity happens to be 10.

An explicitly B-only contract could authorize A as independent outer work. Under such a separately qualified contract, A's survival after nested rollback would be intentional. That possibility does not excuse an unexpectedly dirty Session under the conservative candidate-only admission policy. Knowing an object's owner is necessary, but knowledge alone does not move its pending write inside a savepoint.

Require independent evidence for acceptance

The minimum evidence bundle records runtime versions and options, initial rows, pending counts before entry, mutation placement, ordered SQL and parameters, whether the nested body was reached, post-rejection rows, the outer decision, and final row identities. These are complementary checks, not interchangeable substitutes.

A missing UPDATE in a partial trace cannot prove that no earlier update occurred. A rollback marker cannot prove that the intended writes followed it. A final A=10 cannot distinguish successful nested reversal from a full outer rollback without the intervening evidence.

When ownership is unclear, HOLD means stop admitting new work and return control to the transaction owner. It does not authorize an indefinite live transaction, a cleanup commit, or silent detachment. The application's lifecycle policy must bound that escalation. Acceptance requires an explanation of the whole observed sequence, not a convenient final value.

Reconcile values and identities after every control

Use exact rows as the oracle. Every successful final check in this fixture contains precisely one row with id=1 and code='A'; B is absent, and the duplicate row with id=3 is absent. The quantity distinguishes the outcomes that a row count would collapse together.

The same discipline motivates row-identity reconciliation after batch writes. Here the question is not generated-ID reporting, but whether the savepoint and the outer decision leave the required identified rows and values. No cursor status substitutes for that comparison.

“Expected” comes from the supplied case specification and documented boundary behavior. “Reproduced” refers to the independent execution of the unchanged driver for this playbook. Agreement does not convert the synthetic values into a performance measure, an incident-frequency estimate, or production evidence.

Compare S1 with S2 to isolate the outer decision. Compare S1 with S4 to test the proposed no_autoflush repair. Compare S1 with S3 to test mutation placement. S5 changes the error class: the pending uniqueness failure prevents the nested body from running at all.

Record the complete final row list as well as the compact table. A remaining row with the wrong code, an unexpected third row, or a correct quantity attached to a different identity should fail reconciliation. The driver's exact-list assertions provide that stronger check for these deliberately small fixtures.

When adapting the experiment, do not weaken the oracle to “one UPDATE succeeded” or “one row remains.” Affected-row counts describe an operation's immediate result, while this contract concerns membership, values, and transaction placement. Preserve all three dimensions through the final reader.

Recover without discarding someone else's work

In an owned disposable transaction, recovery can be direct: abandon the outer unit explicitly, retain the evidence, and rebuild a clean unit of work from the committed state. S2 demonstrates that choice while A's earlier flush remains uncommitted. S5 instead reaches a failed lifecycle in which database rollback has already occurred and Session cleanup remains necessary.

A shared application Session requires a different ownership conversation, not a different definition of rollback. Identify the component responsible for the whole transaction. SQLAlchemy's Session lifecycle guidance recommends managing that lifecycle outside the individual functions that manipulate data. A candidate helper should report evidence rather than seize the outer commit decision.

Do not use expunge_all() as SQL rollback. Removing objects from a Session does not undo database writes already flushed into its transaction. An unconditional commit could make unwanted work durable; an outer rollback of an active transaction could cancel other authorized but uncommitted work. Neither action follows automatically from a local desire for clean pending collections.

Separate recovery before commit from correction after commit. The former can use the owned outer rollback demonstrated here. After an actual completed commit, a later rollback does not rewind that completed transaction; a compensating or corrective operation needs its own business authorization and lies outside this fixture.

Blind retry is equally inadequate. First establish whether the earlier database transaction still exists, whether the Session is active, which writes were already emitted, and who owns the next decision. REPAIR means restore a verifiable boundary and rerun. ROLLBACK means terminate or reset the appropriate owned lifecycle. HOLD means stop until the unresolved ownership or evidence can be addressed safely.

Assign transaction-boundary checks to an operational owner

Keep the fixture in a version-pinned regression suite rather than relying on an isolated screenshot or copied trace. Rerun it after changing the interpreter, SQLAlchemy patch, SQLite library, or connection configuration. Retain the source revision, recorded versions and options, case results, and SQL ledger together.

The application owner defines the business scope: which mutations are outer work, which belong to a candidate, and who can commit or roll back the enclosing transaction. The reviewer checks that the code places those mutations where the contract requires. The database or reliability reviewer checks statement order and the final-row oracle.

Assign a specific review action when a control changes. An unexpected statement before SAVEPOINT calls for a write-placement review; a different final quantity calls for reconciliation of the outer decision; a failure before body entry calls for pending-state ownership review. Record the finding and rerun the relevant controls after correction instead of weakening assertions to recover a green test result.

The test owner preserves the exact executed source and evidence. A successful run of an edited driver is evidence for that edited driver, not proof that a previously supplied script ran unchanged. Record changes to error handling, event hooks, or observation timing, because any of those can alter what the output establishes.

Treat pending-state admission as an explicit application policy. The conservative guard is intentionally narrower than a transaction-history audit. Its simple collection checks were also exercised in separate synthetic checks on the reference stack, including new, dirty, deleted, same-value dirty, and already-flushed states. Those supporting checks do not add an is_modified()-based guard or qualify application-specific ownership conventions.

Keep evidence handling proportionate to the data. This fixture contains inert row identities and quantities. A production adaptation might place sensitive values in parameter representations, so restrict access and retention, and keep such evidence out of public examples. Do not confuse removing sensitive payloads with deleting the boundary information required to investigate safely.

Record what remains outside the test

The five cases do not qualify async sessions, distributed transactions, other database engines, concurrent visibility, bulk operations, or a richer clean-session guard. Production migration commands, DDL implicit-commit workflows, and foreign-key activation remain outside the operational procedure. Setup schema creation is not an implicit-commit experiment.

Connection pooling, framework transaction wrappers, external transaction bindings, and event hooks are qualification boundaries. The tested engine uses its ordinary configuration; this is not a survey of pool behavior or every way a Session can join an existing connection transaction. Record those differences before transferring the policy into another environment.

For each extension, preserve the method: declare ownership, fix the environment, inspect pending state, capture SQL order, provoke a controlled rejection, identify the outer outcome, and reconcile rows. A documented contract supplies expectations; only the executed adaptation supplies new observations about that adaptation.

Build stronger database recovery judgement

The practical skill is distinguishing pending Python state, SQL already emitted, a savepoint's rollback scope, and the outer decision that determines the final result. Keep those distinctions explicit whenever a local rejection shares a transaction with earlier work.

For broader study, inspect Refonte Learning's Database Administrator Essentials curriculum. Its official page describes three months at 12–14 hours per week, with database design, SQL optimization, backup and recovery, monitoring, security, and migration topics. It describes practical projects and mentoring, recommends basic programming knowledge, and requires applicants to be working toward a bachelor's or higher-level degree.

The page names a Training Certificate and Certificate of Internship on successful completion, while describing an internship as potential. These statements are not a placement or employment guarantee. The curriculum's general recovery relevance also does not establish that SQLAlchemy, SQLite, or this exact Session.begin_nested() fixture is taught. Use the curriculum to assess those broader skills, not as proof of specialized coverage.