Thanks very much for the informative reply Mike!
On Sun, Aug 6, 2017 at 9:38 PM, Mike Bayer wrote:
>
>
> On Aug 6, 2017 8:11 PM, "Jinghui Niu" wrote:
>
> When reading the official SQLAlchemy documentation, I found the example
> below:
>
> ### this is the **wrong way to do it** ###
>
> class ThingOne(object):
> def go(self):
> session = Session()
> try:
> session.query(FooBar).update({"x": 5})
> session.commit()
> except:
> session.rollback()
> raise
>
> class ThingTwo(object):
> def go(self):
> session = Session()
> try:
> session.query(Widget).update({"q": 18})
> session.commit()
> except:
> session.rollback()
> raise
>
> def run_my_program():
> ThingOne().go()
> ThingTwo().go()
>
> I don't really understand the drawbacks for this pattern. Actually I can
> think of one major ADVANTAGE to this: in a multi-threading context, this
> pattern can ensure that each session instance is a local variable to the
> function that actually uses it.
>
> Could someone enlighten me by giving some potential drawbacks for the
> example above? Thanks.
>
>
> The drawbacks aren't just potential, they are actual. I have spent
> several years extricating openstack from their use of this antipattern
> everywhere.
>
> When you hardcode the transaction logic with a small query operation, now
> you can't use that method in other context besides all by itself. If some
> other function wants to do some database work in a transaction, and wants
> to call upon your function too to also do some database work, you end up
> using two separate transactions simultaneously, as well as two database
> connections simultaneously, when you should be using just one. The two
> transactions are isolated from each other, and if one is dependent on the
> rows of the other, now you have to also use a low isolation level so that
> this works.
>
> In practice, developers know none of this, they string the functions
> together, and then you get one simple web service method that runs four
> transactions holding onto two connections. Then under load the app
> deadlocks, uses too many connections, and when an operation fails, it
> leaves garbage committed in the database. Because the application was not
> correctly designed to properly separate the concerns of running
> transactions at the start and end of a logical sequence of operations, from
> that of individual database query and DML operations such that database
> operations are composable.
>
> Also this issue has nothing to do with threading as long as you aren't
> using a Session as a global variable, which you also should never do within
> any non-trivial application. The session is passed between functions so
> would not find itself crossing thread boundaries unless you explicitly
> placed it on some kind of channel that other threads are consuming (which
> you should not do).
>
> Also, things like handling what happens during an exception, how the
> rollback and close occurs, how the exception is rethrown is *absolutely*
> something that should all be in one place and one place alone, and not
> repeated all over the place. Handling failure modes should be done as
> consistently as possible.
>
>
>
>
> --
> 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 -
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/W_Rn-EwKvZo/unsubscribe.
> To unsubscribe from this group and all its topics, 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 -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Co
