SQLAlchemy Flushing vs Committing

May 23, 2022

Why use session.flush:

• session object is an ongoing transaction of changes to database
  • these changes aren't persisted until they are committed (if there is an error, all uncommitted changes will be lost)
• session object registers transaction operations with session.add

but doesn't yet communicate them to the database until session.flush() is called.

session.flush() communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction. The changes aren't persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does).

session.commit() commits (persists) those changes to the database.

flush() is always called as part of a call to commit() (1).

When you use a Session object to query the database, the query will return results both from the database and from the flushed parts of the uncommitted transaction it holds. By default, Session objects autoflush their operations, but this can be disabled.

• you may need the id (autoincremented) of a newly created object in the middle of a transaction

Atomicity:

• an ensemble of operations must all be executed successfully or none of them will take effect
• for example:
  • if you want to operate on some object (A) and then operate on another object (B), if (B) fails you want to revert (A). These operations are atomic.
  • if (B) needs a result of (A), you want to call flush after (A) and commit after (B)