[sqlalchemy] Re: Guidance regarding nested session usage

[alex]

Thank you very much for the guidance Jonathan and Mike. I've implemented 
nesting counting on my context manager and turned off autocommit and 
subtransactions. It looks like it's working well! 

Alex 

On Wednesday, August 9, 2017 at 5:14:09 PM UTC+1, al...@withplum.com wrote:
>
> Hey,
>
> I'd like some help regarding nested session usage please.
>
> I'm working on an application that has an API layer but also has a lot of 
> cron jobs (via Celery) and scripts. I'm trying to design the app in a way 
> that my "business" logic is contained and re-usable by any of these 
> interfaces. 
>
> The SQLAlchemy session scope is request/task-wide (i.e requests and tasks 
> remove the scoped session at the end) but I am doing explicit commits 
> instead of committing on request end because I sometimes have to deal with 
> complicated logic like creating/submitting transactions to payment 
> processors etc. 
>
> To start off, I use a context manager, much like the docs, which commits 
> or rollbacks as necessary. I then have a layer of actions, which are 
> considered "top-level" functions that can do a simple operation e.g update 
> something or a collection of operations i.e create and submit a 
> transaction. These actions use the context manager above to persist stuff 
> and I've opted to keep all session "usage" in these actions alone and 
> nowhere else in the code. Pretty soon, the need to use some of the simpler 
> actions inside other, bigger actions arose which, after reading the docs, 
> led me to turn autocommit=True and use session.begin(subtransactions=True). 
> Note that I don't want to use savepoints, I just want to be able to use my 
> actions inside other actions. The docs recommend that expire_on_commit is 
> set to False with autocommit, which I've done but that led to a couple of 
> situations where I was operating on out-of-date data hence I want to turn 
> expire_on_commit to True again. 
>
> My questions:
>
> (1) Does my application layout make sense from a SQLAlchemy perspective? 
> (2) What is the problem with expire_on_commit=True and autocommit=True?
> (3) I feel that, even with the context manager, the transaction boundaries 
> are still blurry because the developer does not know what will actually get 
> committed in the database. For example, if a previous part of the code 
> changed something, then called an action that commits the session, the 
> previous change will get committed as well. I've searched around and found 
> this: https://github.com/mitsuhiko/flask-sqlalchemy/pull/447 which 
> basically issues a rollback on entering the context manager to ensure that 
> only what is within the context manager will get committed. What do you 
> think of it? I can immediately see a problem where if I query for an object 
> before passing it to an action, then use the context manager, all the work 
> done on querying is lost since the object state is expired on rollback. 
>
> I'd appreciate any advice/input.
>
> Best,
> Alex
>
>

-- 
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.

[sqlalchemy] Re: Guidance regarding nested session usage

[Jonathan Vanasco]

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.