Hi Michael, first, thank you for the extensive explanation, it is much appreciated.
On 11/28/2012 04:59 PM, Michael Bayer wrote: > > 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. We are using it for a third reason: If highly concurrent reads are desired against the SQLite database, it is advised that the autoflush feature be disabled, and potentially even that autocommit be re-enabled, which has the effect of each SQL statement and flush committing changes immediately. http://docs.sqlalchemy.org/en/rel_0_8/dialects/sqlite.html?highlight=sqlite#database-locking-behavior-concurrency > 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. Sorry, I thought it was clear that I was referring to the test program written by Daniel which started this thread. > 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. Okay. >>> 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". Thanks for the clarification. So basically, we hit a dead end by using autocommit=True. I guess we will finally convert to autocommit=False and see if we can bypass the locking issues inherent to SQLite. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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.
