On Nov 28, 2012, at 6:34 AM, Torsten Landschoff wrote: > Let me get that straight: You are saying that a single Session can have > multiple concurrent connections if configured with autocommit=True?
First, I'm assuming that you're familiar with the recommendations regarding the autocommit=True option, which is that *it is a legacy option not for general use* (http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#autocommit-mode). The are only two reasons this flag should ever be used: 1. you're running a SQLA app that was built on some very ancient version, that is, 0.3 or 0.4, and can't change it. In this scenario, you're actually executing queries without the Session being aware of the transaction scope. 2. you're a framework integrator, and you're using autocommit=True so that you can call begin() on the Session explicitly. In this case, the Session is *not* executing queries outside of transactions, because the integration makes sure that begin() is called before the Session is used to communicate with a database. So here is how the Session works: it procures a Connection from the connection pool for each transaction, then when the transaction is complete, it returns it to the pool. When there is no transaction, the effect is that the Session.execute() call used to emit queries is pretty much the same as calling Engine.execute(). It obtains a connection, runs the SQL, returns it back to the pool. In the case where the SQL has returned a result set, the underlying connection stays checked out until the result set is exhausted, then the result set closes itself and the connection associated with it (that is, returned to the pool). This is called "implicit execution" and is described here: http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html#basic-usage. So in general, there is no "concurrency" implied here, it uses the connection for a single execute() then returns it. The connection stays open until the underlying result set is exhausted. When the Query uses Session.execute(), it typically (unless yield_per() is used) calls "fetchall()" on the result, at which point the cursor is closed and the connection returned to the pool. > > Basically, the original Query for a user with a given country spain (which is > expired) causes a connection checkout conn1 for the original query. > However, before the first select on that connection is run, another > connection is checked out to refresh the Country instance "spain". I've reproduced a test case from this, in the future just sending that along would be helpful because when you said "subquery", I assumed you were using subqueryloading and went through all the trouble to test that. In this case, the Query procures the Connection from the Session, then goes to execute the query, but in the case of a comparison like filter_by(some_many_to_one=someobject), a special mechanism is used that fetches the "id" from "someobject" as late as possible, which is required for some relatively common expiration cases I'd have to dig up to illustrate. So in that one case, probably no others besides yield_per(), you get a refresh operation of the mapped object within the scope of the select() construct being compiled which will check out another connection if the Session is being run within autocommit. > > > I would think that this is not the desired behaviour, especially given an > earlier message from you on this list: > On 11/09/2012 11:36 PM, Michael Bayer wrote: >> On Nov 8, 2012, at 5:01 PM, Torsten Landschoff wrote: >>> My first tests with the SQLAlchemy core where promising, but when using >>> the ORM I get a bunch of deadlocks where it seems like the session opens >>> two connections A and B where A locks B out. >> The Session never does this, assuming just one Engine associated with it. >> It acquires one Connection from the Engine, holds onto it and uses just that >> connection, until commit() at which point the connection is released to the >> pool. Above, I was trying to simplify matters assuming the recommended patterns were used. Here's the full sentence: "The Session never does this, assuming just one Engine associated with it, and assuming it is used in its recommended configuration of autocommit=False". -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
