Transactions & Atomicity
In Lesson 4, you defined relationships between models and queried linked data with joins. Now you face a different problem: what happens when a write operation involves multiple steps, and one of them fails halfway through?
Imagine you are transferring $100 from your Food budget to Entertainment. The debit goes through — your Food balance drops by $100. Then the credit fails. Crash. Network error. Doesn't matter why. Your $100 just vanished into thin air. Not in Food. Not in Entertainment. Gone.
You might be thinking: "That can't really happen, right?" It absolutely can. And it does. Every production system that handles money or inventory has battle scars from exactly this scenario. The fix is not hope or retry logic. The fix is a transaction — a boundary that guarantees either both writes happen, or neither does.
- Transaction: A group of database operations that must ALL succeed or ALL fail — there's no middle ground
- Atomicity: The "all-or-nothing" property — like a light switch, it's either on or off, never halfway
- Invariant: A truth that must always hold — "debits and credits in a transfer always net to zero" is an invariant. If it ever breaks, something went wrong.
How Transactions Work
A transaction wraps multiple database operations into a single unit of work. The database keeps all changes in a temporary state until you explicitly say "commit" (make permanent) or "rollback" (discard everything). If anything goes wrong before the commit, every change since the transaction began is undone automatically.
Transaction States:
┌─────────┐ ops succeed ┌──────────┐
│ BEGIN │───────────────────►│ COMMIT │
│ │ │ (durable) │
└────┬─────┘ └──────────┘
│
│ any op fails
│
▼
┌──────────┐
│ ROLLBACK │
│ (undo │
│ ALL ops)│
└──────────┘
Example: Budget Transfer
┌─────────────────────────────────────┐
│ BEGIN │
│ 1. Debit $100 from Food ✓ │
│ 2. Credit $100 to Fun ✗ │
│ │
│ → ROLLBACK: Debit is also undone │
│ → Result: $0 changed (correct!) │
└─────────────────────────────────────┘
That last line is the key insight. After a rollback, your data looks exactly as it did before the transfer attempt. No phantom debits. No missing money. The database pretends the whole thing never happened.
(Partial writes: because nothing says "professional software" like $100 disappearing from both accounts.)
The Transfer Function
Here is an atomic budget transfer using SQLAlchemy. Everything happens inside one session, wrapped in try/except/rollback. If the credit fails, the debit is rolled back. If the debit fails, nothing was written yet. Either way, your data stays consistent.
from datetime import date
from decimal import Decimal
from sqlalchemy import Column, Date, ForeignKey, Integer, Numeric, String, create_engine, select
from sqlalchemy.orm import Session, declarative_base
Base = declarative_base()
class Category(Base):
__tablename__ = "categories"
id = Column(Integer, primary_key=True)
name = Column(String(50), unique=True, nullable=False)
class Expense(Base):
__tablename__ = "expenses"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, nullable=False)
category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)
description = Column(String(200), nullable=False)
amount = Column(Numeric(10, 2), nullable=False)
date = Column(Date, nullable=False, default=date.today)
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
def transfer_budget(user_id: int, from_category_id: int, to_category_id: int, amount: Decimal):
with Session(engine) as session:
try:
from_cat = session.execute(
select(Category).where(Category.id == from_category_id)
).scalars().first()
to_cat = session.execute(
select(Category).where(Category.id == to_category_id)
).scalars().first()
if not from_cat or not to_cat:
raise ValueError("Category not found")
debit = Expense(
user_id=user_id,
category_id=from_category_id,
description=f"Transfer to {to_cat.name}",
amount=-amount,
)
credit = Expense(
user_id=user_id,
category_id=to_category_id,
description=f"Transfer from {from_cat.name}",
amount=amount,
)
session.add_all([debit, credit])
session.commit()
return {"success": True}
except Exception as exc:
session.rollback()
return {"success": False, "error": str(exc)}
Output (successful transfer):
>>> transfer_budget(user_id=1, from_category_id=1, to_category_id=2, amount=Decimal("100.00"))
{"success": True}
Output (invalid category):
>>> transfer_budget(user_id=1, from_category_id=1, to_category_id=999, amount=Decimal("100.00"))
{"success": False, "error": "Category not found"}
Notice the pattern: one session, one try block, one commit at the end, one rollback in the except. This is the core transaction template you will use for every multi-step write in this course.
The Failure Drill
Reading about atomicity is not the same as proving it works. Run this drill to see rollback in action:
- Run transfer with valid categories — expect two new rows
- Run transfer with an invalid destination category — expect zero new rows
- Query the expense count before and after each run
- Confirm the invariant: successful transfers change count by exactly 2, failed transfers change count by exactly 0
This invariant check is stronger than checking return messages alone. A function can return {"success": False} while still leaving partial rows behind if the rollback was missing. The only proof is querying the database directly.
Think about the transfer function you just saw. What would happen if step 1 (debit) committed in one session and step 2 (credit) ran in a different session? What if the second session crashed? Where did the money go?
The Multi-Session Anti-Pattern
The most dangerous mistake is splitting related operations across separate sessions. Here is what that looks like:
Bad pattern:
- Session A writes the debit and commits
- Session B writes the credit and fails
Result: irreversible partial state. The debit is permanent because Session A already committed. Session B's rollback only undoes Session B's work — it cannot reach back into Session A and undo the debit. Your $100 is gone.
(This is why related database writes should never live in separate sessions. If they must succeed together, they must live in the same transaction. Period.)
Another frequent mistake is catching an exception and returning without calling rollback. That leaves the failed transaction state unresolved and causes downstream confusion — later queries in the same session may behave unpredictably because the session is in a "dirty" state.
Input Validation: Necessary but Not Sufficient
Transactions prevent partial writes. They do not correct bad business inputs. You still need validation rules:
- Validate that the amount is positive
- Validate that source and target categories are different
- Validate ownership scope if categories are user-specific
Think of it this way: transactions protect the mechanics of your write (all-or-nothing). Input validation protects the meaning of your write (is this a sensible operation?). You need both.
Invariants: The Test That Catches Everything
An invariant is a truth about your data that must always hold. For budget transfers, the core invariants are:
- Transfer ledger entries for a completed move always net to zero
- Category totals before and after a transfer preserve the global sum
- Failed transfers produce no new rows
Writing these invariants into tests gives you faster confidence than manual spot checking. When a test asserts "the sum of all amounts for this transfer is zero," it catches bugs that return-message checks miss entirely.
When in doubt, choose stronger safety:
- Explicit rollback in every except block
- Explicit invariant assertions in every test
- Explicit post-failure query checks that verify actual database state
Debug Posture for Transaction Bugs
When something goes wrong with a transaction, follow this discipline:
- Distrust return messages without database verification
- Inspect persisted rows directly after both success and failure paths
- Treat any partial side effect as a severity-one defect
If your function returns {"success": False} but the database has one new row instead of zero, you have a transaction bug. The return message lied. The database told the truth. Always trust the database over application-level return values.
What breaks next? Write safety can still fail in production if cloud connection handling is weak. Deployment reliability is next.
Try With AI
Setup: Open Claude or ChatGPT with your budget tracker models from this chapter.
Prompt 1: Atomicity Classifier
For each scenario, classify "needs atomic transaction" vs "does not need atomic transaction":
- single insert (one new expense)
- transfer between two categories (debit + credit)
- monthly summary read (SELECT with GROUP BY)
- merge category migration (move all expenses from category A to B, then delete A)
Explain the failure mode if atomicity is missing for each one that needs it.
What you're learning: Not every database operation needs a transaction boundary. Single reads and single writes are already atomic by default. The skill is recognizing which operations involve multiple steps where partial completion would corrupt your data. This classification instinct prevents both under-protection (missing transactions where needed) and over-protection (wrapping single inserts in unnecessary transaction ceremony).
Prompt 2: Rollback Proof Drill
Write a transaction function that intentionally fails after the first insert.
Then show the post-failure query proving zero rows were committed.
Use SQLAlchemy 2.0 style and explicit rollback.
What you're learning: Proving rollback works requires more than reading the return value. You need to query the database after the failure and verify the row count is unchanged. This drill builds the habit of verifying database state directly — a practice that catches transaction bugs that return-message checking misses.
Prompt 3: Apply to Your Domain
Think of a multi-step operation in a project you're building. Maybe it's: creating a user account + sending a welcome email + logging the event. Or: transferring inventory between warehouses. Break it into steps and ask: "If step 2 fails, what happens to step 1?" Design the transaction boundary.
What you're learning: Transaction design isn't just for banks. Any operation where partial completion would corrupt your data needs an atomic boundary. Recognizing these moments — and wrapping them in try/except/rollback — is a skill that separates reliable systems from fragile ones.
Checkpoint
- I can explain atomicity as all-or-nothing business truth.
- I can implement one-session multi-step writes with rollback.
- I can prove rollback with a deliberate failure drill.
- I can identify multi-session anti-patterns in write workflows.
- I can distinguish schema validity from transaction correctness.