Handling SQLAlchemy Transactions and Errors: A Simple Guide for Python

If you’re working with databases in Python using SQL Alchemy, you’ve probably heard about sessions and transactions. But what happens when an error occurs? Do all your database changes get lost? Or do some updates still work?

Let’s break this down with a simple example and learn how to avoid common mistakes.

What’s a Transaction?

A transaction is like a shopping cart for your database:

You add items (queries) to the cart.(in sessions)
When you’re done, you checkout (commit()) to save the changes.
If something goes wrong, you cancel the cart (rollback()) to discard changes.

In SQL Alchemy, every session creates a transaction automatically.

The Problem: Errors in a Loop

with get_db() as session:  # Single session reused
    for order_result in placed_order_results:
        try:
            order_service.saveInitialOrder(session, ...)  # Database operation
            session.commit()  # Explicit commit
        except:
            # Handle error (BUT NO ROLLBACK CALLED!)

If an error occurs during saveInitialOrder (e.g., a database constraint violation, network error, or invalid SQL), the transaction is invalidated and marked as "zombie" by SQLAlchemy.
Without calling rollback():

The session’s transaction remains in a failed state.
Subsequent iterations (e.g., order_result 2, 3, etc.) cannot execute new queries because the transaction is broken.

Example Flow

Let’s say the loop processes 5 orders, and an error occurs at order_result=3:

Iteration 1 (Order 1)
- ✅ Success → commit() → Saved to DB.
- Transaction ends. New transaction starts.
Iteration 2 (Order 2)
- ✅ Success → commit() → Saved to DB.
- Transaction ends. New transaction starts.
Iteration 3 (Order 3)
- ❌ Error occurs (e.g., database constraint violation).
- No rollback() called → transaction is invalidated.
- Session enters a broken state.

Iteration 4 (Order 4)

❌ Attempts to run saveInitialOrder → fails immediately with:

  sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception...

No database interaction happens.

Iteration 5 (Order 5)
- ❌ Same failure as Iteration 4.

Why Does This Happen?

When a database error occurs, SQL Alchemy automatically invalidates the transaction.
Without rollback(), the session stays in a broken state and cannot execute new queries.
The error message explicitly tells you: "This Session's transaction has been rolled back due to a previous exception" (even though you didn’t call rollback()).

What’s the Final Database State?

Orders 1 & 2: Committed → saved to the database.
Orders 3, 4, 5: Not saved.
- Order 3 failed and wasn’t committed.
- Orders 4 & 5 couldn’t run due to the broken session.

How to Fix It

Always call rollback() in the except block to reset the session and start a new transaction:

Key Takeaways

Call rollback() on errors:
- Resets the session to a clean state.
- Allows subsequent iterations to run in new transactions.
Transactions are atomic:
- Each commit() or rollback() ends the current transaction.
- A new transaction starts automatically after commit()/rollback().
Never leave a session in a broken state:
- Failing to call rollback() makes the session unusable for future operations.

By adding rollback(), you ensure errors in one iteration do not affect others. 🔄

Here is The Fix:

with get_db() as session:  # Single session reused
    for order_result in placed_order_results:
        try:
            order_service.saveInitialOrder(session, ...)
            session.commit()  # Explicit commit
        except:
            session.rollback()  # Explicit rollback
            # Handle error

Transaction Boundaries:
- A transaction automatically starts when the session is created (with get_db() as session).
- Every commit() or rollback() ends the current transaction and starts a new one immediately.
Isolation:
- Each loop iteration operates in its own transaction (due to commit()/rollback() inside the loop).
- Errors in one iteration do not affect subsequent iterations because a new transaction starts after every commit()/rollback().

Step-by-Step Flow

Let’s say your loop processes 5 queries (query1 to query5), and query3 fails:

Iteration 1: query1
- Success → commit() → Saved to DB.
- Transaction ends. New transaction starts for the next iteration.
Iteration 2: query2
- Success → commit() → Saved to DB.
- Transaction ends. New transaction starts.
Iteration 3: query3
- Error → rollback() → Changes discarded.
- Transaction ends. New transaction starts for the next iteration.
Iteration 4: query4
- Success → commit() → Saved to DB.
- Transaction ends. New transaction starts.
Iteration 5: query5
- Success → commit() → Saved to DB.
- Transaction ends.

What Happens to the Data?

query1, query2, query4, query5:
- Committed → Persisted to the database.
query3:
- Rolled back → No trace in the database.

Why This Works

Transactions are isolated: Each commit()/rollback() resets the session’s transaction scope.
No shared state: Errors in one iteration do not leak into others because a new transaction starts after every commit()/rollback().

Key Takeaways

Always call commit() or rollback() after an operation to cleanly end the transaction.
SQL Alchemy automatically starts a new transaction after every commit()/rollback(), so subsequent operations are isolated.
This pattern ensures atomicity: Each iteration’s database changes are either fully saved (commit()) or fully discarded (rollback()).
✅ Pros: Faster for large loops.
⚠️ Cons: Must remember to call rollback().

Fix 2: New Session per Iteration


 for order_result in placed_order_results:  
    with get_db() as session:  # Fresh session for every iteration  
        try:  
            order_service.saveInitialOrder(session, ...)  
            session.commit()  # Commit only this iteration's changes  
        except:  
            session.rollback()  # Discard only this iteration's changes  
            # Handle error

✅ Pros: Each iteration is fully isolated.
⚠️ Cons: Slightly slower due to session setup.

Understanding SQL Alchemy Transactions and Errors