SQLAlchemy Transactions: Commit, Rollback, Flush, and Refresh

python sqlalchemy transactions commit rollback flush and refresh requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When working with Python SQLAlchemy transactions, understanding the difference between commit, rollback, flush, and refresh is essential for writing reliable database code. These four operations control when SQL is sent to the database, when a transaction ends, and how object state is synchronized. This article explains each operation, how they interact, and where they commonly cause confusion in production code.

The Session and Its Transaction

In SQLAlchemy, a Session is the main entry point for ORM operations. It tracks changes to objects and decides when to send SQL to the database. The Session maintains a transaction that begins implicitly when you first use it. This transaction is what commit and rollback act on. Understanding this lifecycle is the foundation for using flush, commit, rollback, and refresh correctly.

When you create a Session and start adding or modifying objects, the changes are held in memory. The database has not yet seen them. The Session decides the moment to emit SQL based on the operations you call. This is where flush and commit come in.

Flush: Sending SQL Without Committing

Flush is the process of synchronizing the state of the Session with the database by emitting INSERT, UPDATE, and DELETE statements. It does not commit the transaction. After flush, the database has the changes within the current transaction, but they are not durable. You can still roll them back.

from sqlalchemy.orm import Session

session = Session()
user = User(name="Alice")
session.add(user)
session.flush()  # INSERT is sent to the DB, but not committed
print(user.id)   # Now the primary key is populated

Flush is useful when you need the database-generated values, such as an autoincrement primary key, before the transaction ends. Without an explicit flush, the ORM may flush automatically when you query or when you commit. But if you need the ID immediately, you call flush.

Flush does not end the transaction. It only pushes the current state to the database. The transaction remains open until commit or rollback.

Commit: Ending the Transaction

Commit finalizes the transaction. It flushes any pending changes first, then issues a COMMIT to the database. After commit, the changes are durable and visible to other transactions. The Session then begins a new transaction on the next use.

session.add(user)
session.commit()  # Flush + COMMIT

After commit, the Session expires all objects by default. This means that accessing an attribute will trigger a refresh from the database on the next access. This behavior is configurable, but it's the default in SQLAlchemy.

Commit is the operation that makes your changes permanent. If you forget to call commit, your changes are lost when the session closes.

Rollback: Discarding the Transaction

Rollback undoes all changes made in the current transaction. It reverts the database to the state it was in when the transaction began. It also expires all objects in the Session, so they will be reloaded on next access.

try:
    session.add(user)
    session.commit()
except Exception:
    session.rollback()
    raise

Rollback is essential for error handling. If an operation fails partway through, you need to roll back to avoid leaving the Session in an inconsistent state. After rollback, the Session is ready for a new transaction.

Refresh: Re-syncing an Object's State

Refresh re-reads the current state of an object from the database, overwriting any changes that are present in the Session. It is useful when you want to discard local changes or when you need to see the latest committed data.

session.refresh(user)

Refresh issues a SELECT statement to reload the object's attributes. It does not affect the transaction state. You can call refresh inside a transaction, and it will read the current committed data (or the data visible within the current transaction isolation level).

Refresh is different from expire. Expire marks the object as stale, and the next attribute access triggers a reload. Refresh loads immediately.

Ordering and Interaction: When Each Operation Is Needed

The typical flow is: add or modify objects, then commit. Flush is optional unless you need generated values. Rollback is used on errors. Refresh is used when you need to discard local changes or read fresh data.

Note that commit and rollback both end the transaction. After either, the Session starts a new transaction on the next operation.

Error Handling and Transaction Boundaries

A common pattern is to wrap a unit of work in a try/except block. On exception, roll back to ensure the Session is clean. Then you can optionally raise or handle the error.

def create_user(session, name):
    try:
        user = User(name=name)
        session.add(user)
        session.commit()
        return user
    except Exception:
        session.rollback()
        raise

This ensures that a failed operation does not leave partial changes in the Session. Without rollback, the Session might still hold dirty state, causing confusion in later operations.

Performance and Operational Considerations

Flush and commit have different performance implications. Flush sends SQL to the database, which involves network round trips. Commit also flushes, so calling flush before commit is redundant unless you need the generated values earlier.

In batch processing, you might want to flush periodically to free memory, but this also sends SQL. The Session accumulates objects in memory until flush. If you are adding thousands of rows, flushing in chunks can help memory usage, but it also increases the number of round trips. There is a tradeoff.

Another consideration is that commit expires all objects. This means the next attribute access will trigger a SELECT. If you are in a read-heavy path, you may want to disable expiration on commit using session.commit() with expire_on_commit=False in the session configuration. But this can lead to stale data if other transactions modify the same rows.

The key is to understand when each operation is necessary. Overusing flush can add unnecessary load. Underusing it can cause errors when you rely on database-generated values before commit.