On Mon, May 12, 2014 at 6:02 PM, Sylvester Steele <sylvesterste...@gmail.com > wrote:
> > > > On Mon, May 12, 2014 at 4:53 PM, Michael Bayer > <mike...@zzzcomputing.com>wrote: > >> >> On May 12, 2014, at 2:30 PM, Sylvester Steele <sylvesterste...@gmail.com> >> wrote: >> >> >>> the code here isn’t really showing me the nature of the two separate >>> connections. They will be different unless you’re using “threadlocal” on >>> the engine or pool and both are in the same thread. A simple comparison of >>> the “raw” DBAPI connections to see if they are different objects will >>> suffice (make sure you’re on the ultimate DBAPI connection though, not the >>> “fairy” object). However note that Microsoft ODBC drivers also feature >>> connection pooling, so you’d need to turn it off at the ODBC configuration >>> level as well. >>> >> >> Hopefully the following details will help. Here is the sanitized code and >> logs: >> >> >> >> well the create_engine() statement isn’t here which is really what would >> illustrate if you’re doing some kind of thread local thing up there. It >> looks a lot like your ODBC driver is pooling connections, as we see lots of >> pyodbc.connection objects with different identifiers, but yet it says the >> connection is busy with other results, additionally we see that you have a >> cursor local to select() yet some kind of error is being raised lots which >> illustrates continuous “connection closed” issues. >> >> so same answer, look to see that you don’t have anything thread local on >> your engine and look that you don’t have pooling in your ODBC driver turned >> on. >> >> > I will look again. The engine creation logic is quite separate from the > query execution logic. The code that creates engines is threadsafe to make > sure that only one engine is created per session per DB. So, all these > threads would be using the exact same engine (because the query that they > are all executing goes to the same DB). The first thread which reaches the > select function will be the only thread to actually create an engine. This > engine is then cached and reused by all other threads. > Looks like I finally figured it out. I enabled sqlalchemy pool logging and found that as soon as the object returned by eng.connect() goes out of scope the connection is returned to the pool. In the above code- that happens near instantly. So, the sqlalchemy pool thinks that this connection is checked in the pool and available for use. But, in reality the code is still using it. Keeping the object returned by eng.connect() until the result set is fetched seems to have solved the problem. ODBC connection pooling setting did not matter. After the above change, code is running in both cases (ODBC connection pooling on or pooling off). Let me know if this is an issue and you need more info. Thanks -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
