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

Reply via email to