MANOHAR.

ENGINEERING NOTES / DATABASES

Understanding transaction boundaries in SQLAlchemy

Why transaction ownership matters, how flush differs from commit, and how to structure database operations around business workflows.

MANOHAR · 8 MIN READ · Python · SQLAlchemy · PostgreSQL · 2026-10

KEY ARCHITECTURAL TAKEAWAYS

  • 01.Repositories must never call session.commit() — committing belongs strictly to the service layer.
  • 02.session.flush() writes SQL changes to the transaction buffer and populates generated IDs without committing.
  • 03.The Transactional Outbox pattern depends on saving business entities and outbox events within the exact same database transaction boundary.

01 / The Motivation: Where does a transaction belong?

When starting out with SQLAlchemy, it is common to sprinkle session.commit() inside repository methods. A create_order method saves an order and commits. A reserve_inventory method updates stock and commits. However, this repository-level commit anti-pattern shatters transaction atomicity as soon as a multi-step business workflow emerges.

In OrderFlow, creating an order involves validating item availability, decrementing inventory quantities, inserting the order row, creating order item line records, and writing an outbox event. If the inventory decrement succeeds and commits, but the outbox insert subsequently fails, the database is left in a corrupted, half-applied state.

02 / The crucial difference: flush() vs commit()

Understanding the distinction between flush() and commit() is fundamental in SQLAlchemy. A flush communicates pending object state changes to the database by executing INSERT, UPDATE, and DELETE SQL statements within the active transaction. Crucially, it does NOT end the transaction.

This means PostgreSQL assigns primary keys (e.g. SERIAL or UUID), generates timestamps, and evaluates foreign key constraints, while the changes remain completely invisible to other concurrent transactions (under Read Committed isolation).

services/order_service.py
# Correct: Service-owned transaction boundary
class OrderService:
    def __init__(self, session: Session, order_repo: OrderRepository, outbox_repo: OutboxRepository):
        self.session = session
        self.order_repo = order_repo
        self.outbox_repo = outbox_repo

    def create_order(self, customer_id: str, items: list[OrderItemCreate]) -> Order:
        with self.session.begin():  # Manages BEGIN and COMMIT / ROLLBACK
            order = self.order_repo.add(customer_id=customer_id)
            self.session.flush()  # Flushes to DB: order.id is now generated and accessible!

            for item in items:
                self.order_repo.add_item(order_id=order.id, item=item)

            # Atomic outbox write within the same transaction
            self.outbox_repo.add_event(
                event_type="OrderCreated",
                payload={"order_id": str(order.id), "customer_id": customer_id}
            )
            # Transaction commits automatically at exit of context manager

03 / The debugging war story: Premature commit side effects

During early OrderFlow development, an intermittent bug occurred: orders were created with missing line items when downstream payment validation failed. Investigation revealed that an internal repository helper was committing after inserting the parent order.

When the payment gateway validation threw an exception 50ms later, the subsequent rollback only rolled back the uncommitted line items, leaving an orphaned, empty order in the database. Refactoring all commits into the top-level service context manager resolved the bug completely.

← All notes