A similar question about another anti-pattern was asked/answered recently. Most of what Mike says applies in this use-case https://groups.google.com/forum/#!topic/sqlalchemy/W_Rn-EwKvZo especially the locking and integrity issues with long-running transactions. He's written about it elsewhere as well.
Personally, I prefer to use the following approach when dealing with repeated actions and long-running processes: 1. Long running, complicated, processes have their own table, which includes at-least: job_id, current_state, timestamp_start, timestamp_last, timestamp_finish As the job progresses, this table is updated. Having it in the database allows us to find stuck jobs, etc. 2. Long processes span multiple transactions. Some transactions are nested with savepoints. 3. Re-usable functions accept the session as an argument and, as a rule, never commit. When they must commit (it happens) you require a kwarg set and raise an error if it's missing. This way the logic is absolutely clear in the calling function (otherwise, maintenance and code reviews are a headache) We often use secondary sessions with auto-commit to track 3rd party api logging/etc too. For example, a payment processing task on an ecormmerce project I worked on once did the following: session autocommit- log that we're about to charge $x, returning id api integration- charge $x session autocommit- log that we successfully charged $x to id (or failed). session transaction - note the charge, continue with the task automated reports then check for charges that were not completed and not marked as an acceptable fail. those items are errors that need to be reconciled with the payment processor's logs. Other people here have enforce much better standards and practices than I do. We have some Celery jobs that use 4-5 transactions when dealing with external APIs. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.