Transactions — ACID Properties and Data Consistency

A transaction is a sequence of SQL statements that are treated as a single indivisible unit of work. Either all statements in the transaction succeed and their changes are permanently saved (COMMIT), or all changes are discarded if anything goes wrong (ROLLBACK). Transactions are the mechanism that keeps your database consistent — a bank transfer that debits one account and credits another must either complete both steps or neither. In FastAPI, every route handler that modifies the database should run in a transaction, and SQLAlchemy manages this automatically when you use sessions correctly.

ACID Properties #

Transaction Control #

-- PostgreSQL is always in autocommit mode unless inside an explicit transaction
-- In psql, each statement is its own auto-committed transaction by default

-- ── Explicit transaction ───────────────────────────────────────────────────────
BEGIN;   -- start a transaction (also: BEGIN TRANSACTION; or START TRANSACTION;)

-- All statements between BEGIN and COMMIT/ROLLBACK are one atomic unit
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- If both succeed: make permanent
COMMIT;

-- Or if something went wrong: undo all changes since BEGIN
ROLLBACK;

-- ── SAVEPOINT — partial rollback within a transaction ────────────────────────
BEGIN;

INSERT INTO posts (title, author_id, body) VALUES ('A', 1, 'Body A');
SAVEPOINT after_first_insert;

INSERT INTO posts (title, author_id, body) VALUES ('B', 1, 'Body B');

-- Something went wrong with the second insert:
ROLLBACK TO SAVEPOINT after_first_insert;   -- undoes only the second insert

-- First insert is still pending
COMMIT;   -- commits only the first insert

Transaction Isolation Levels #

-- PostgreSQL supports four isolation levels (default: READ COMMITTED)

-- READ COMMITTED (default): each statement sees the most recently committed data
-- Good enough for most web application queries

-- REPEATABLE READ: a transaction sees a consistent snapshot from its start time
-- Good for reports that read multiple tables and need consistent data

-- SERIALIZABLE: highest isolation — transactions appear to run one after another
-- Required for financial transactions, inventory management

-- Set the isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
SELECT balance FROM accounts WHERE id = 2;
-- Both reads see the same snapshot — no "phantom reads"
COMMIT;

-- Check current isolation level
SHOW transaction_isolation;

Transactions in FastAPI with SQLAlchemy #

from sqlalchemy.orm import Session
from fastapi import Depends, FastAPI, HTTPException

app = FastAPI()

# ── The standard session dependency ────────────────────────────────────────────
def get_db():
    db = SessionLocal()
    try:
        yield db           # FastAPI provides db to the route handler
        db.commit()        # commit if handler returned successfully
    except Exception:
        db.rollback()      # rollback on any exception
        raise
    finally:
        db.close()         # always close the session

# ── Route handler: all DB operations run in the same transaction ───────────────
@app.post("/transfer")
def transfer_funds(
    from_id: int,
    to_id: int,
    amount: float,
    db: Session = Depends(get_db)
):
    sender    = db.query(Account).filter(Account.id == from_id).first()
    recipient = db.query(Account).filter(Account.id == to_id).first()

    if not sender or not recipient:
        raise HTTPException(404, "Account not found")
    if sender.balance < amount:
        raise HTTPException(400, "Insufficient funds")

    sender.balance    -= amount   # both changes...
    recipient.balance += amount   # ...or neither is committed

    # SQLAlchemy tracks these changes — they will be committed together by get_db()
    # If an exception is raised before the handler returns, get_db() calls rollback()
    return {"message": f"Transferred ${amount}"}

Common Mistakes #

Mistake 1 — Not rolling back after an exception in Python #

❌ Wrong — failed transaction leaves the session in an error state:

try:
    db.add(user)
    db.commit()
except Exception:
    # No rollback! Session is in error state; next operation will fail too
    raise HTTPException(500, "Failed")

✅ Correct — always rollback on failure:

try:
    db.add(user)
    db.commit()
except Exception:
    db.rollback()   # ✓ clears error state
    raise HTTPException(500, "Failed")

Mistake 2 — Starting a long transaction before an external API call #

❌ Wrong — holds DB locks while waiting for payment gateway:

with db.begin():
    order = create_order(db, ...)    # DB locked
    payment = payment_api.charge(.) # external call — could take 30 seconds!
    confirm_order(db, order.id)      # DB locked the entire time!

✅ Correct — commit first, then call external service:

order = create_order(db, ...)
db.commit()                         # release locks ✓
payment = payment_api.charge(...)   # external call outside transaction
with db.begin():
    confirm_order(db, order.id)     # new transaction for confirmation ✓

Mistake 3 — Relying on autocommit mode without explicit transaction control #

❌ Wrong — each SQL statement commits immediately, no atomicity:

engine = create_engine(url, execution_options={"autocommit": True})
# Each statement is its own transaction — cannot rollback a group of operations

✅ Correct — use explicit session with begin/commit/rollback for atomic operations.

Quick Reference #