Hi All Just incase anyone was wondering.. I found the cause of my session issues. (Hooray!)
The Application is served by a home grown python HTTP server which works just fine - however, I found that it had an implementation of threading POOL to handle requests, rather than creating a new thread for each request. As it is a dedicated and task specific HTTP server, I had added some DB work inside the code that handles POST data (i.e. before it despatched the job to the waiting processes to deal with). I had forgotten that the POST was being handled by a thread that was not torn down at the end - and therefore the next POST request ran in the same context! Hence broken transactions on the MySQL side, and then the subsequent inability to serve further requests. Changed one line of code in the HTTP server to change it back to non pooled and it all worked perfectly :-) Thanks Michael to your invaluable insights into the workings of SA, as it was only a few minutes after reading your response that the answer was obvious. :-) Cheers Warwick On 7 October 2010 00:20, Michael Bayer <mike...@zzzcomputing.com> wrote: > > On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: > > > I can't quite get my head around the "scope" of sessionmaker() yet.. I've > tried putting Session = sessionmaker() as a global to the entire codebase > (works but has same problem) > > > do you mean scoped_session here ? sessionmaker is just a constructor for a > new Session. It doesn't hold onto anything. > > scoped_session OTOH is nothing more than a thread local variable. > Background on this concept is available here: > http://docs.python.org/library/threading.html#threading.local . The > remove() call removes the current thread local's context. If your app were > single threaded, you could replace it with a single Session object, where > you just call close() instead of remove() - it wouldn't be very different. > > Within the context of a multiprocess, single-threaded application, > threading.local doesn't have any effect - there's just one thread. > > Importantly.. I can completely close all my processes and cold start my > code - and this error continues until I restart mySQL ! i.e. NO session > will work again, but I can do basic queries. What the?! > > > I've seen this happen with PG when we are testing out two-phase > transactions. You might want to ask on some MySQL forums what queries you > might do to look at current lock/transaction state. > > > BTW: It someone says "You should be using scoped_session.." Please explain > how to have either more than one session in the same context (I use three > for a possible three different binds) > > > yeah I actually have an app with a couple of scoped sessions, since there > are two different databases and operations generally proceed with one or the > other. > > or how to create one session that I can bind to more than one engine, and > not have to know in advance all possible tables I might want to use on each > engine.. > > > If the table metadata is bound to an engine, then the session doesn't need > to be bound. I.e. if tables A, B, C on metadata X are bound to engine P, > tables D, E, F on metadata Y are bound to engine Q, you just use the > Session, and it will handle the two engines as needed. > > If you really want total control, using some ruleset that's not quite as > simple as table->metadata->engine, you can subclass Session and override > get_bind(). I've never recommended that to anyone, but I put it out there > just to help de-mystify the situation. Its just one call that takes in a > mapper, returns an engine. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
