KAHIBARO
Discord Login Register ☰
☰

12.11. Transactions

Table of Contents

Why Transactions Matter in Backend Applications

In real backend applications, you often need several database operations to succeed or fail as one unit. For example:

If any step fails, you do not want a half-created order. This is exactly what database transactions solve.

A transaction is a group of database operations that the database treats as a single logical unit of work.

Key rule: A transaction is all or nothing. Either all the operations in the transaction succeed and are committed, or any failure causes all changes to be rolled back.

When using an ORM like SQLAlchemy, you will rarely write BEGIN or COMMIT SQL manually, but you must understand what they mean and how to control them.

Basic Transaction Lifecycle

Most database systems, and ORMs that talk to them, follow the same basic cycle:

  1. Start a transaction.
  2. Run one or more SQL statements (through ORM methods).
  3. If everything is OK, commit.
  4. If something goes wrong, roll back.

In SQL the pattern looks like this:

sql 
BEGIN;
INSERT INTO users (email) VALUES ('alice@example.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

If anything fails:

sql 
ROLLBACK;

With an ORM, the code is more like:

python 
from sqlalchemy.orm import Session
def create_user_and_charge(session: Session):
    new_user = User(email="alice@example.com")
    session.add(new_user)
    account = session.get(Account, 1)
    account.balance -= 100
    # If we reach here with no errors:
    session.commit()
    # If an error happened, we want session.rollback()

Commit and Rollback with an ORM

Explicit control

Most ORMs support explicit methods for transaction control. Using SQLAlchemy as an example:

python 
def transfer_money(session: Session, from_id: int, to_id: int, amount: int):
    try:
        # 1. Read accounts
        from_account = session.get(Account, from_id)
        to_account = session.get(Account, to_id)
        if from_account.balance < amount:
            raise ValueError("Insufficient funds")
        # 2. Update balances
        from_account.balance -= amount
        to_account.balance += amount
        # 3. Try to commit
        session.commit()
    except Exception:
        # 4. If any error occurs, undo all changes
        session.rollback()
        raise

Important notes:

Using context managers

Context managers are a safer and cleaner pattern:

python 
from sqlalchemy.orm import Session
def transfer_money(engine, from_id: int, to_id: int, amount: int):
    with Session(engine) as session:
        try:
            from_account = session.get(Account, from_id)
            to_account = session.get(Account, to_id)
            if from_account.balance < amount:
                raise ValueError("Insufficient funds")
            from_account.balance -= amount
            to_account.balance += amount
            session.commit()
        except Exception:
            session.rollback()
            raise

Even better, SQLAlchemy offers session.begin():

python 
def transfer_money(session: Session, from_id: int, to_id: int, amount: int):
    with session.begin():  # starts a transaction
        from_account = session.get(Account, from_id)
        to_account = session.get(Account, to_id)
        if from_account.balance < amount:
            raise ValueError("Insufficient funds")
        from_account.balance -= amount
        to_account.balance += amount
    # If no errors, `with` automatically commits.
    # If there is an error, it automatically rolls back.

Grouping Multiple Operations in One Transaction

A transaction is most useful when it groups multiple related operations.

Example: Creating an order

Suppose you have these models:

python 
class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    status = Column(String)  # "pending", "paid", "failed"
class OrderItem(Base):
    __tablename__ = "order_items"
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey("orders.id"))
    product_id = Column(Integer, ForeignKey("products.id"))
    quantity = Column(Integer)
class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    stock = Column(Integer)

When a user places an order, you want to:

  1. Create an Order.
  2. Insert OrderItem rows.
  3. Reduce Product.stock for each product.
  4. If any step fails, do not create a partial order.

With transactions:

python 
def place_order(session: Session, user_id: int, items: list[dict]):
    """
    items example: [{"product_id": 1, "quantity": 2}, {"product_id": 5, "quantity": 1}]
    """
    with session.begin():
        # 1. Create order
        order = Order(user_id=user_id, status="pending")
        session.add(order)
        session.flush()  # ensure order.id is assigned
        # 2. Add items and update stock
        for item in items:
            product = session.get(Product, item["product_id"])
            if product.stock < item["quantity"]:
                raise ValueError("Not enough stock for product " + str(product.id))
            # Create order item
            order_item = OrderItem(
                order_id=order.id,
                product_id=product.id,
                quantity=item["quantity"],
            )
            session.add(order_item)
            # Reduce stock
            product.stock -= item["quantity"]
        # 3. If we get here without Raises, transaction will commit
        order.status = "paid"
    # Any exception inside `with session.begin()` will roll back everything

Key points:

Nested Transactions and Savepoints

Sometimes you want to treat a part of your work as "optional" inside a larger transaction. For example:

You can handle some of this with application logic, or with savepoints and nested transactions.

Savepoints concept

A savepoint is like a bookmark inside a transaction. You can roll back to the savepoint without canceling the entire transaction.

In SQL:

sql 
BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT maybe_shipping;
INSERT INTO shipping_labels (...) VALUES (...);  -- might fail
-- If the insert fails:
ROLLBACK TO SAVEPOINT maybe_shipping;
COMMIT;

With SQLAlchemy:

python 
def create_order_with_optional_label(session: Session, order_data, label_data):
    with session.begin():  # outer transaction
        order = Order(**order_data)
        session.add(order)
        session.flush()
        # Create a savepoint
        nested = session.begin_nested()
        try:
            label = ShippingLabel(order_id=order.id, **label_data)
            session.add(label)
            nested.commit()  # commit subtransaction
        except Exception:
            nested.rollback()
            # mark order for manual shipping
            order.status = "needs_manual_shipping"

Notes:

Transaction Boundaries per Request

In web backends, a very common pattern is:

This is often called "unit of work per request".

Pattern:

  1. A request arrives.
  2. The framework creates a database session.
  3. All database operations are executed inside that session and one transaction.
  4. If the request handler finishes successfully, the transaction commits.
  5. If an exception happens, the transaction rolls back.

Example using a dependency in FastAPI style:

python 
from sqlalchemy.orm import Session
def get_db_session():
    session = Session(engine)
    try:
        yield session
        session.commit()     # commit if no exception
    except Exception:
        session.rollback()   # rollback on error
        raise
    finally:
        session.close()

Then in your route:

python 
@app.post("/orders")
def create_order(order_input: OrderCreate, db: Session = Depends(get_db_session)):
    # Everything here is inside one transaction
    order = Order(user_id=order_input.user_id, status="pending")
    db.add(order)
    # ...
    return {"id": order.id}

Benefits:

Concurrency, Isolation, and Common Pitfalls

Transactions become more interesting when multiple clients use the database at the same time.

Imagine two requests that both try to transfer money from the same account:

Depending on how you write your code and what isolation level the database uses, you might end with a lost update.

You do not need full ACID details here, but you must understand some practical issues.

Example: Lost updates

Bad pattern:

python 
def withdraw(session: Session, account_id: int, amount: int):
    account = session.get(Account, account_id)   # SELECT
    account.balance -= amount                    # in memory
    session.commit()                             # UPDATE

If two withdrawals run at the same time:

  1. Both read balance 100.
  2. One subtracts 70, sets 30.
  3. The other subtracts 50, sets 50.
  4. Final balance is 50 instead of 100 - 70 - 50 = -20 or some conflict.

To avoid this, you can:

Locking rows for update

Many ORMs let you use SELECT ... FOR UPDATE to lock rows within a transaction.

With SQLAlchemy:

python 
from sqlalchemy import select
def safe_withdraw(session: Session, account_id: int, amount: int):
    with session.begin():
        stmt = (
            select(Account)
            .where(Account.id == account_id)
            .with_for_update()
        )
        account = session.execute(stmt).scalar_one()
        if account.balance < amount:
            raise ValueError("Insufficient funds")
        account.balance -= amount

Here:

Important: When you modify shared data from multiple requests or workers, you must think about concurrency. Use:

  • proper transaction boundaries,
  • row-level locks when needed,
  • and database constraints to enforce rules.

Idempotency and Retries with Transactions

In distributed systems, sometimes operations are retried. For example:

If your transaction is not careful, a retry might duplicate work. Transactions help keep things atomic, but they do not automatically make operations idempotent.

Simple pattern for idempotent writes:

  1. Use a unique key for the operation, for example idempotency_key.
  2. Insert a row with that key inside a transaction.
  3. If another request with the same key arrives, the unique constraint prevents duplication.

Example:

python 
class Payment(Base):
    __tablename__ = "payments"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    amount = Column(Integer)
    idempotency_key = Column(String, unique=True)

Handler:

python 
from sqlalchemy.exc import IntegrityError
def process_payment(session: Session, user_id: int, amount: int, idem_key: str):
    with session.begin():
        payment = Payment(
            user_id=user_id,
            amount=amount,
            idempotency_key=idem_key,
        )
        session.add(payment)
        try:
            session.flush()  # attempts INSERT, may raise IntegrityError
        except IntegrityError:
            # Payment with this key already exists.
            # You can fetch it and return the same result.
            existing = (
                session.query(Payment)
                .filter_by(idempotency_key=idem_key)
                .one()
            )
            return existing
        # Continue with work that depends on the new payment
        return payment

Here, the combination of:

guarantees that either the new payment is created once, or an existing one is used.

Practical Tips and Common Mistakes

Tips

Common mistakes

MistakeProblemBetter approach
Forgetting to call commit()Changes stay uncommitted and may be lostAlways commit at the end of a successful unit of work
Doing long external calls inside a transactionHolds locks longer, increases contentionFetch data first, then open a short transaction only for the database changes
Mixing many unrelated operations in one transactionHard to reason about and debugKeep each transaction focused on a single logical operation
Using global session without clear boundariesHidden open transactions and stale stateUse scoped or per-request sessions and clear open/close rules

Transactions are a fundamental part of safe backend development with an ORM. Once you understand how to group operations, commit, roll back, and think about concurrency, you can build reliable data workflows in your applications.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3695 / 24h
Impressum / Imprint
Datenschutz / Data Policy