Thank you, Simon, for clarifying this and pointing out that part of the SQLAlchemy docs... somehow I missed that part :-). On Mar 26, 2010, at 7:30 AM, King Simon-NFHD78 wrote:

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