[Twisted-Python] [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync

Matthew Williams mgwilliams at gmail.com
Fri Mar 26 09:12:01 EDT 2010


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



More information about the Twisted-Python mailing list