Python: Database Transactions and Preventing Partial Updates

Introduction

Many backend operations require several database changes to succeed together. Transferring money, placing an order, or reserving inventory may involve updating multiple records.

Without a transaction, a failure halfway through can leave the database in an inconsistent state. Transactions provide a way to group related operations into a single unit of work.

1. Understand atomicity

Consider a bank transfer. The application must subtract money from one account and add it to another.

If the first update succeeds but the second fails, the system must not leave the transfer partially completed.

A database transaction provides the basic structure:

def transfer_money(    connection,    source_id: int,    destination_id: int,    amount: int,) -> None:    if amount <= 0:        raise ValueError("Amount must be positive")    with connection:        # Debit the source account.        # Credit the destination account.        # Record the transfer.        ...

This example is schematic: the actual SQL, connection behavior, and transaction semantics depend on the database driver. The important point is that all related updates must be committed together or rolled back together.

2. Use the transaction API provided by your library

Database libraries differ in how they begin, commit, and roll back transactions.

With SQLAlchemy, for example, a session can provide a transaction boundary:

from sqlalchemy.orm import Sessiondef create_order(engine, order_data):    with Session(engine) as session:        with session.begin():            order = Order(**order_data)            session.add(order)            session.flush()            # Related database changes belong here.            return order.id

This assumes the Order model and order_data validation are defined elsewhere. The transaction commits when the block completes successfully and rolls back if an exception escapes it.

Keep external side effects—such as sending emails or calling a payment provider—out of the database transaction where possible. Database rollback cannot undo an email or a completed remote API operation.

3. Understand isolation and concurrency

A transaction does not automatically prevent every concurrency problem. The result depends on the database’s isolation level, locking strategy, and queries.

For example, two requests may both read that one item remains in stock before either decrements the inventory. If both then proceed independently, the system can oversell.

Possible solutions include atomic conditional updates, appropriate row locks, or stronger isolation where justified. Choose the approach based on the database and workload, and test concurrent operations rather than relying only on sequential unit tests.

4. Keep transactions short

Long-running transactions can hold locks, increase contention, and delay other operations.

Avoid waiting for a user, calling a slow external service, or performing expensive CPU work while holding a database transaction open.

Instead, perform the necessary validation and preparation first, execute the database changes in a focused transaction, and handle follow-up work separately.

For workflows that span a database and a message queue, a transactional outbox can help ensure that a committed change has a corresponding event to publish.

5. Test rollback and failure paths

Test that a failed operation leaves the database in the expected state. Include failures after an initial update, constraint violations, concurrent requests, and retry behavior.

Use the same database engine as production for tests involving locking, isolation, or database-specific features. In-memory substitutes may not reproduce the same semantics.

Conclusion

Transactions protect related database changes from partial completion, but they must be combined with appropriate isolation and concurrency controls. Keep transaction scopes focused, avoid mixing them with irreversible external side effects, and test failure paths as thoroughly as successful operations.