> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of Matthew Williams > Sent: 26 March 2010 12:10 > To: sqlalchemy@googlegroups.com; twisted-pyt...@twistedmatrix.com > Subject: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync > > > On Mar 26, 2010, at 3:20 AM, Chris Withers wrote: > > > Matthew Williams wrote: > >> From previous posts to this and other lists, it seems that ORMs > >> and threads don't get along too well... > > > > What makes you think that? > > First of all, most of my impressions about ORMs come from > SQLAlchemy. > This quote from this list > (http://twistedmatrix.com/pipermail/twisted-python/2009-March/ 019359.html > ) sums up what I have found as well: > > "It's much trickier if you want to use the ORM, unless you are very > careful to fully eager load every thing in any possible database > operation if you have need of the information subsequently in your > twisted code. Otherwise you may block unexpectedly simply when > accessing your objects, and end up with database operations from the > main twisted thread." > > So perhaps I should have said "SQL Alchemy's ORM and threads > don't get > along too well"... that's not to say it's impossible, you > just have to > be exceedingly careful how you use it. >
I think that point should be clarified, so that people don't later come across this post and just accept it without understanding. I imagine that SQLALchemy is used in a lot of threaded applications. For example, it is the recommended ORM in web frameworks such as Pylons and TurboGears, which work fine in threaded environments. However, typically in these libraries a web request is handled by a single thread, and all the SQLAlchemy operations occur within the scope of that request. As long as you don't share a Session instance between the threads, you won't have any problems. SQLAlchemy provides a ScopedSession class which helps in these situations, as you can call the constructor many times on a single thread and always get the session instance back for that thread. Sessions themselves aren't thread-safe. When an instance is loaded from the database, it is linked to the session that loaded it. This means that when you have lazy-loading properties on that instance (such as related classes, or deferred column properties), they will be automatically loaded when they are accessed, in the same session. This will cause a problem if you load an instance in thread A, hand the object off to thread B, and then thread B accesses one of these lazy-loading properties. The load will occur in thread A's session, which might be in the middle of doing something else. The solution to this is either to eager-load all the attributes you think you are going to need before handing the instance off to another thread (difficult), or (probably better) to detach (expunge) the instance from thread A's session. Thread B should then merge the object into its own session (using the load=False flag so that it doesn't needlessly requery the database). The Session docs at http://www.sqlalchemy.org/docs/session.html explain the lifecycle of loaded instances. I haven't actually done any of this - I've only ever used SA from TG and command-line scripts, but I think the principles are about right. I hope that helps, Simon -- 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.
