How do you atomically create an order and update inventory?

WHAT THIS TESTS: The interviewer wants to see if you understand ACID transactions at the application layer, specifically how SQLAlchemy 2.0 sessions map to database transactions and how you prevent partial writes in a FastAPI service. They care about boundary control, exception handling, and concurrency safety, not just syntax.

A GOOD ANSWER COVERS: First, session scoping: explain that you create a single Session bound to one database transaction, typically via an async sessionmaker with sessionmaker.begin() used as a context manager inside a FastAPI dependency. Second, operation ordering: insert the order row, then update the inventory row, keeping both within the same session. Third, flush versus commit: call session.flush() after the inventory update to send SQL to the database and catch constraint or locking errors immediately, while still retaining the ability to roll back. Fourth, error handling: let exceptions propagate out of the context manager so that sessionmaker.begin() automatically calls rollback; only call commit explicitly if you are managing the transaction manually with session.begin(). Fifth, concurrency: mention using SELECT FOR UPDATE or optimistic locking on the inventory row to prevent race conditions between the read and the write.

COMMON WRONG ANSWERS: A major red flag is calling session.commit() after the order creation and before the inventory update, which splits one business operation into two transactions and leaves orphaned orders if the second write fails. Another mistake is opening two separate sessions for the two writes, which breaks atomicity unless you use a two-phase commit or distributed transaction protocol that you almost certainly do not need here. Candidates also err by autoflushing without understanding when it happens, or by catching exceptions inside the scope and returning a soft error without rolling back, leaving the session in a dirty state.

LIKELY FOLLOW-UPS: The interviewer may ask how you handle transactions across multiple microservices, which would shift the answer to the Saga pattern or outbox pattern rather than a single database transaction. They may also ask what happens if the application crashes after commit but before you publish an event, leading to a discussion of idempotency keys or transactional outbox. Another follow-up is performance under high contention, where you would discuss reducing transaction scope, retry logic with exponential backoff, or moving inventory checks into the database via triggers or stored procedures.

ONE CONCRETE EXAMPLE: In a FastAPI route, inject an AsyncSession from a sessionmaker configured with asyncpg. Inside the route, use async with session.begin(): create an Order object and add it to the session, then execute a select on the Product table with select_for_update to lock the inventory row, decrement the count, and add the change to the session. If either operation raises an IntegrityError or StaleDataError, the context manager rolls back the entire transaction, so no order row persists and no inventory is changed. If both succeed, the context manager commits when exiting the block.