SQLAlchemy Interview Questions: Session State, Flush, and Rollback

SQLAlchemy interview questions become much easier when you track two things separately: the state of a Python object and the state of its database transaction. An object can be persistent after a flush while its insertion is still uncommitted. A failed flush can also leave the Session requiring recovery before the next query.

This guide follows a user record through add, autoflush, commit, expiration, a unique-constraint failure, and rollback. The goal is to explain an observable trace rather than memorize isolated definitions.

Evidence boundary: official references use SQLAlchemy's stable 2.0 documentation. The examples were tested locally with SQLAlchemy 2.0.52 and SQLite 3.53.4. They are original practice exercises, not candidate reports or claims about interview frequency. Cross-database isolation, distributed transactions, and production latency are outside this fixture.

Define the small transaction you are debugging

Our model has two columns: an integer primary key and a unique email address. The first address is ada@example.test; a later successful insertion uses bo@example.test. These reserved example addresses make duplicate behavior easy to follow without relying on real account data.

The test records SQL through a before_cursor_execute listener. Schema creation happens before recording starts, and each measured branch clears the trace before the next observation. The official event reference explains that hook. We also inspect ORM state and assert returned rows, so a statement count is not our only evidence.

Be precise about what this trace contains. It records statements passed through the cursor event, not every connection-level transaction action as a literal SQL string. In particular, seeing only INSERT in the commit branch does not mean no database commit occurred. Driver transaction methods and SQL statement events are different observations.

Before answering a similar interview question, state the Session configuration, whether the object is new or loaded, and whether a transaction already contains changes. Those assumptions often explain why two superficially similar snippets behave differently.

Question 1: Does Session.add insert immediately?

In our first branch, creating User(email=...) produces a transient object. Calling session.add(user) makes it pending, with zero captured SQL statements. The object is associated with the Session, but the insertion has not yet been sent in this branch.

user = User(email='ada@example.test')
assert inspect(user).transient
session.add(user)
assert inspect(user).pending

The official state-management guide distinguishes transient, pending, persistent, deleted, and detached states. These terms describe an object's relationship to the ORM and database identity; they are not synonyms for business success.

For example, a service that returns “account created” immediately after add may still encounter a constraint error later. A pending object can look complete in Python while a required database check has not run. Explain where the operation crosses the database boundary before deciding what the API can safely report.

Do not infer insertion from a field you assigned yourself. An application-generated identifier can exist before SQL executes. Our experiment instead uses the state inspector and captured statements to identify the transition.

Question 2: Why did a SELECT trigger an INSERT first?

We next execute an ORM query in the same Session:

rows = session.scalars(select(User)).all()

The trace contains INSERT followed by SELECT. The returned list contains the same Python object, now persistent. With the default configuration used here, the ORM query triggers autoflush of pending changes before reading.

The practical debugging lesson is that the line named in an exception may look like a read even though pending writes caused the failure. If the INSERT violates a unique constraint, investigate earlier additions and modifications in the Session instead of assuming the SELECT itself is malformed.

The Session basics documentation describes autoflush boundaries. Avoid extending our result into “every possible SQL execution always autoflushes.” Our observed operation is an ORM-enabled select(User) executed through this Session.

A useful interview follow-up changes the order: add an incomplete object, run a query needed to finish constructing it, then fill the remaining fields. The candidate should notice that the intermediate read can expose incomplete pending state. The appropriate fix depends on why construction and database lookup are interleaved.

Question 3: If flush succeeded, can rollback still remove the row?

Yes. After that successful autoflush, our test rolls back and queries again. The database returns no users, and the newly added object's state is transient again. This is the clearest evidence in the fixture that successful flush and successful commit are different milestones.

The INSERT reached the database inside the transaction, but the transaction was not committed. We deliberately assert the post-rollback row count rather than trying to infer persistence from the object's still-readable email attribute.

For an API design discussion, ask which layer owns the complete unit of work. Suppose creating a user must also create an audit row. Committing inside the first helper can prevent the caller from treating both operations as one atomic unit. Flushing to obtain a generated key can be useful without prematurely ending the outer transaction.

This is editorial guidance about transaction boundaries, not a requirement that every application use one particular service architecture. The important property is that a reviewer can identify the owner responsible for commit, rollback, and error propagation.

Question 4: What does no_autoflush actually prevent?

Our next Session adds Ada again, then queries inside no_autoflush:

session.add(user)
with session.no_autoflush:
    rows = session.scalars(select(User)).all()

The query returns an empty list, the object remains pending, and the trace contains only SELECT. After leaving the block, calling commit() emits the pending INSERT and completes the transaction.

This is a focused suppression mechanism, not a read-only mode or a promise that writes will never occur. If the code later commits, it still needs to flush pending work. If the code explicitly calls flush, that is also a deliberate database operation rather than an automatic query-triggered flush.

A good reason for a narrow block is assembling an object graph that is temporarily incomplete while resolving related data. A poor explanation is “I disabled autoflush so the constraint error went away.” If the pending data remains invalid, the error has merely moved to a later boundary.

In a review, ask what makes the object valid before the eventual flush. Identify the exact fields or relationships completed in the block, and test the eventual write. Otherwise, the optimization can conceal an inconsistent construction path until a less obvious line fails.

Question 5: Why is rollback required after a failed flush?

Our database now contains Ada. A new Session attempts a second row with the same unique email. flush() raises IntegrityError, and session.is_active becomes false. Catching that exception alone does not make the Session ready for another query.

We deliberately try an ORM SELECT before rollback. It raises PendingRollbackError, with no new cursor statement recorded. After session.rollback(), the Session is active again and a query returns the one previously committed row.

Step	Observed result	What the candidate should explain
Flush duplicate email	IntegrityError	The database rejects the constraint violation.
Inspect Session	is_active is false	The failed transaction needs explicit recovery.
Query before rollback	PendingRollbackError	Catching the first exception did not reset the Session.
Roll back, then query	One committed row	The earlier successful transaction remains intact.
Add Bo and commit	Two rows in a fresh Session	Recovery allows a later valid transaction.

The official Session FAQ explains the distinction between the rollback caused by a flush failure and resetting the Session's transaction framing. The application still needs an explicit rollback or an enclosing transaction context that performs it.

For interview practice, show the failed second query as well as the successful recovery. A test that catches only IntegrityError misses the operational problem this question is designed to expose.

Question 6: Why does an attribute read issue another SELECT?

In the successful commit branch, our default Session configuration expires the user's attributes. We verify inspect(user).expired immediately after commit. Reading user.email then issues one SELECT and returns the stored address.

This explains a familiar surprise: code that appears to format a response can still access the database. The attribute belongs to an ORM object whose state may require loading. Whether that is acceptable depends on the surrounding Session lifetime and response boundary.

The distinction between expired and detached matters. Expiration marks attributes for reloading; detachment removes association with a Session. An expired attribute that needs database access cannot simply reload through a Session that the object no longer has.

One design option is to build an explicit response value while the necessary data is available. Another is to choose expiration settings deliberately. Do not present disabling expiration as a universal fix: retaining local attributes does not establish that they reflect another transaction's newest changes.

Our fixture tests default synchronous behavior only. It does not imply that implicit I/O has the same ergonomics in AsyncSession. For async code, discuss loading and awaited boundaries using the relevant API rather than mechanically copying a synchronous response pattern.

Question 7: Is the identity map a query cache?

After the attribute load, our object is present and unexpired. session.get(User, user.id) returns that same object with zero SQL statements. A separate ORM SELECT by the same primary key issues one statement but still returns the identical Python instance.

Those two observations answer the question more accurately than “the Session caches everything.” Identity reuse and avoiding database execution are different properties. The SELECT can execute to determine matching rows, while the ORM resolves a returned identity to an object it already manages.

In the fixture, we hold a strong reference to the object throughout the comparison. We also clear the trace between branches, so the refresh SELECT is not accidentally counted as part of the later lookup.

If a query returns stale-looking attributes, do not jump directly to a global cache diagnosis. First inspect transaction isolation, expiration, pending changes, and whether the existing instance is being reused. The database read and the Python object's populated state are related but distinct pieces of evidence.

Frame recovery at the right ownership boundary

SQLAlchemy's transaction documentation shows context-managed transaction boundaries. A small service operation can make its success and failure paths explicit:

with Session(engine) as session:
    with session.begin():
        session.add(User(email='new@example.test'))

Keep exception handling outside the transaction block when the block is responsible for rollback and the caller needs to translate the failure. Swallowing an error inside it and pretending the operation succeeded makes the contract harder to reason about.

A global shared Session is also a different concern from a reusable engine or Session factory. The async documentation calls for separate AsyncSession instances for concurrent tasks. In a design answer, assign each unit of work a clear owner instead of sharing mutable transactional state simply to avoid construction.

If a batch must tolerate individual duplicates, discuss savepoints as a separate design with explicit database support and tests. Do not assume the plain rollback in our fixture preserves unrelated uncommitted changes from the same outer transaction.

Practice the related backend questions

Question	Practice focus
Design a bank account ledger	Define atomic updates and durable records.
Debug an Issue-Creation API Failure	Trace a failure to the operation that caused it.
Design a Transactional In-Memory Key-Value Store	Explain commit and rollback semantics.
Analyze and debug Python utilities	Build minimal evidence for surprising behavior.
Build AI-Assisted Full-Stack Transactions App	Connect database changes to a visible application contract.

For SQLAlchemy interview questions, predict the object state, identify the next SQL boundary, and show the recovery path.

Sources and Further Reading

• SQLAlchemy 2.0: Session basics
• SQLAlchemy 2.0: State management
• SQLAlchemy 2.0: Session FAQ and failed flush recovery
• SQLAlchemy 2.0: Transactions and connection management
• SQLAlchemy 2.0: AsyncSession concurrency
• SQLAlchemy 2.0: Core events