Thanks very much for the informative reply Mike! On Sun, Aug 6, 2017 at 9:38 PM, Mike Bayer <clas...@zzzcomputing.com> wrote:
> > > On Aug 6, 2017 8:11 PM, "Jinghui Niu" <niujing...@gmail.com> 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, 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.