Transaction Patterns for Web Apps

Transaction = unit of consistency, not unit of speed

Every HTTP request usually wraps its database work in one transaction. The transaction's job is "all of this either lands together or doesn't" — it is not a performance feature. Long-running transactions hold locks, accumulate dead tuples, block VACUUM, and create contention for everyone else.

Five rules that prevent most production database fires

Keep transactions short. Open them as late as possible, commit them as soon as possible. Don't make external HTTP calls inside an open transaction.
Don't hold a transaction across user input. If you BEGIN, then wait for the user to fill out a form, then COMMIT — you've held a row lock for minutes.
Use READ COMMITTED unless you have a reason not to. It's the default for good reason.
Always handle SerializationFailure if you use REPEATABLE READ or SERIALIZABLE. Retry with backoff.
Avoid SELECT FOR UPDATE across multiple round trips. If you must lock-then-think-then-update, do it in one stored procedure or one statement.

The web request transaction template

Every framework has a clean way to wrap a request in a transaction. Use it. Custom BEGIN/COMMIT in route handlers is how transaction leaks happen.

Code

FastAPI + SQLAlchemy template·python

from fastapi import Depends
from sqlalchemy.orm import Session

def get_db():
    db = SessionLocal()
    try:
        yield db
        db.commit()
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()

@app.post("/orders")
def create_order(payload: OrderIn, db: Session = Depends(get_db)):
    order = Order(...)
    db.add(order)
    db.flush()  # gets the autogenerated id
    for item in payload.items:
        db.add(OrderItem(order_id=order.id, ...))
    return order

Don't do this·python

# Holds a transaction across an external HTTP call — a deadlock waiting to happen
def risky(db):
    with db.transaction():
        order = db.execute("INSERT INTO orders ... RETURNING id").fetchone()
        # ❌ external API call inside the open transaction
        ext = httpx.post("https://payments.example.com/charge", json={...})
        db.execute("UPDATE orders SET payment_id = %s WHERE id = %s",
                   (ext.json()["id"], order["id"]))

Transaction Patterns for Web Apps

Transaction = unit of consistency, not unit of speed

Five rules that prevent most production database fires

The web request transaction template

Code

External links

Exercise

Progress

Comments 0