Database Transactions — Core Concepts

Why transactions exist

Without transactions, a multi-step database operation can leave data in a broken state if any step fails. Consider an e-commerce checkout: creating an order, deducting inventory, and recording a payment. If the payment fails after inventory was deducted, you have phantom stock reductions with no matching order. Transactions prevent this by guaranteeing atomicity — all changes commit together or none do.

The ACID properties

Atomicity

A transaction is indivisible. If you insert three rows and the third INSERT fails, the first two are rolled back. The database never contains partial results of a transaction.

Consistency

Transactions move the database from one valid state to another. Constraints (foreign keys, unique indexes, check constraints) are enforced at transaction boundaries. If a change would violate a constraint, the entire transaction is rejected.

Isolation

Concurrent transactions don’t interfere with each other. If two users buy the last item simultaneously, isolation ensures only one succeeds. The level of isolation is configurable — higher isolation means fewer concurrency bugs but more performance overhead.

Durability

Once a transaction commits, the data survives system crashes. Databases achieve this through write-ahead logging (WAL) — changes are written to a log file before being applied, so after a crash the database can replay the log to recover committed data.

Transactions in Python

SQLAlchemy

SQLAlchemy’s session manages transactions automatically. A transaction begins implicitly when you first interact with the database and ends when you call commit() or rollback():

from sqlalchemy.orm import Session

with Session(engine) as session:
    order = Order(customer_id=1, total=99.99)
    session.add(order)

    item = InventoryItem.get(session, product_id=5)
    item.quantity -= 1

    session.commit()  # both changes saved atomically

If an exception occurs before commit(), the context manager calls rollback() automatically.

For explicit transaction control:

with Session(engine) as session:
    with session.begin():  # explicit transaction block
        session.add(Order(customer_id=1, total=99.99))
        update_inventory(session, product_id=5, delta=-1)
    # commit happens automatically at end of begin() block

Django

Django wraps each HTTP request in a transaction by default (when ATOMIC_REQUESTS=True). For manual control:

from django.db import transaction

with transaction.atomic():
    order = Order.objects.create(customer_id=1, total=99.99)
    InventoryItem.objects.filter(product_id=5).update(
        quantity=F('quantity') - 1
    )
# commit happens here; rollback on exception

Isolation levels

Databases offer different trade-offs between correctness and performance:

Level	Dirty Reads	Non-repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

• Read Committed (PostgreSQL default): You see only committed data, but re-reading the same row may return different values if another transaction committed in between.
• Repeatable Read: Once you read a row, you see the same value for the entire transaction, even if others commit changes.
• Serializable: Transactions behave as if they ran one at a time. Most correct, but slowest.

Most applications work fine with Read Committed. Use higher levels only for operations where correctness is critical (financial calculations, inventory management).

Common misconception

“If I wrap my code in a transaction, it’s safe from race conditions.”

Transactions protect against partial failures (atomicity), but they don’t automatically prevent race conditions. Two transactions can both read the same inventory count of 1, both decide there’s stock available, and both commit — overselling the product. Preventing this requires either higher isolation levels, explicit locking (SELECT ... FOR UPDATE), or application-level checks.

Nested transactions and savepoints

Sometimes you want to attempt a sub-operation that might fail without aborting the entire transaction:

with Session(engine) as session:
    with session.begin():
        session.add(order)

        # Try to apply a discount — if it fails, continue without it
        savepoint = session.begin_nested()
        try:
            apply_discount(session, order)
            savepoint.commit()
        except DiscountError:
            savepoint.rollback()
            # order is still valid, transaction continues

Savepoints create checkpoints within a transaction. You can roll back to the savepoint without losing other work in the same transaction.

One thing to remember: Transactions are your safety net for multi-step database operations. Use them whenever changes across multiple tables must succeed or fail together, but remember they don’t automatically solve concurrency problems — that requires isolation levels or explicit locking.