Transactions and Atomicity

Scenario

Your e-commerce app charges the user's Stripe card successfully, but then the database crashes before creating the Order record. The user was charged, but has no receipt and no item is shipped.

Mental model

A transaction is an all-or-nothing wrapper. If you're buying a house, the money transfer and the deed transfer are a transaction. If one fails, the other is reversed. You never end up without money AND without a house.

Atomicity ensures that a sequence of database operations is treated as a single indivisible unit. If any operation in the sequence fails, the entire sequence is rolled back, leaving the database exactly as it was before.

Deep dive

Atomicity (the 'A' in ACID) means a series of database operations either completely succeed or completely fail. In SQLAlchemy, a Session represents an ongoing transaction.

A Session is a transaction manager.

When you add objects to a session, nothing is permanently written to the database yet. You must call session.commit() to finalize the transaction. If something goes wrong before the commit, or if you explicitly call session.rollback(), all changes are discarded.

Changes are staged until commit(). rollback() discards them.

In FastAPI, it's a best practice to manage the session lifecycle using a dependency (using yield). If the endpoint completes successfully, you commit. If an exception is raised anywhere in the endpoint, the dependency catches it and rolls back the transaction.

FastAPI dependencies can automatically commit on success and rollback on error.

This guarantees that partial data is never saved. For example, if you deduct inventory and then fail to create the invoice, the inventory deduction is rolled back, preventing corrupted state.

Atomicity prevents inconsistent, corrupted database states.

Code examples

The Transactional Dependency

async def get_db():
    async with async_session() as session:
        try:
            yield session
            await session.commit()  # Success: save everything
        except Exception:
            await session.rollback() # Error: undo everything
            raise

This FastAPI dependency ensures every HTTP request runs inside a single database transaction. If the code throws an exception, the database automatically reverts any partial changes made during that request.

All-or-Nothing execution

@app.post("/transfer")
async def transfer_money(from_id: int, to_id: int, db: AsyncSession = Depends(get_db)):
    sender = await db.get(Account, from_id)
    receiver = await db.get(Account, to_id)
    
    sender.balance -= 100
    db.add(sender)
    
    # If the app crashes HERE, the commit is never reached.
    # The get_db dependency triggers a rollback.
    # The sender doesn't lose their 100.
    
    receiver.balance += 100
    db.add(receiver)
    # get_db will commit both changes atomically

Because both add operations share the same session, they are committed together. The system can never end up in an inconsistent state where money is destroyed.

Key points

• ACID properties: Atomicity guarantees all-or-nothing execution, protecting against crashes midway through a process.
• Session = Transaction: In SQLAlchemy, the Session acts as the transaction manager. Changes are staged until commit().
• Rollback on error: Always ensure session.rollback() is called if an exception occurs to clean up the session state.

Common mistakes

• Committing too early: Calling commit() multiple times inside a single endpoint breaks atomicity. If step 1 commits, and step 2 fails, step 1 cannot be rolled back.

Recall questions

• What does atomicity mean in the context of databases?
• How do you discard pending changes in a SQLAlchemy session?
• Why is it recommended to use a FastAPI dependency (yield) to manage session commits?

Questions & answers

In our billing service, we deduct funds from a user's wallet, and then call a third-party API to issue a gift card. If the API times out, the user loses their money but gets no card. How do we fix this?

Wrap the database deduction and the API call in a single transaction block. If the API call fails or times out, raise an exception which triggers a database rollback, restoring the user's funds.

Why is it dangerous to call session.commit() inside a loop?

It breaks atomicity. If the loop crashes halfway through, the items processed before the crash are permanently saved, while the rest are lost, leaving the system in an inconsistent state.

Continue learning

Previous: Database Migrations with Alembic

Next: Database Connection Pooling

Return to Python Backend Roadmap