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.
