Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
Fernando Takai wrote: > Hi!, > > I'm using SQLAlchemy on a heavily threaded env - something like 30~40 > threads working with SQLAlchemy objects. > What you need to watchout is: > > * Eager load objects - getting nasty lazyload exceptions is not funny > * Take off the objects from the session and, if you need to use them > later, merge to the current thread session - i did this because i had > some "object-is-already-on-session-foobar" exception > * Don't forget to use scoped_session > > Beside those two points, working with threaded apps is quite easy with > SQLAlchemy. :) even the "eager" advice above has nothing to do with threads. that has to do with using your objects after your session has been closed. if you think of your objects as "agents" of your current database transaction, and as a "detached" object as a something that really needs to be re-attached to a transaction somewhere so that it can become an "agent" once again, no such issues occur. > > On Fri, Mar 26, 2010 at 10:12 AM, Matthew Williams > wrote: >> 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 >> >> -- >> 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. >> >> > > > > -- > Fernando Takai > > -- > 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. > > -- 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.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
King Simon-NFHD78 wrote: 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. Are there any recommended code examples around for doing this? Once detatched, are these objects (I'm guessing for most people they'll be instances of declaratively mapped models) pickleable? 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). Good code examples of this around too? cheers Chris -- Simplistix - Content Management, Batch Processing & Python Consulting - http://www.simplistix.co.uk -- 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.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
Hi!, I'm using SQLAlchemy on a heavily threaded env - something like 30~40 threads working with SQLAlchemy objects. What you need to watchout is: * Eager load objects - getting nasty lazyload exceptions is not funny * Take off the objects from the session and, if you need to use them later, merge to the current thread session - i did this because i had some "object-is-already-on-session-foobar" exception * Don't forget to use scoped_session Beside those two points, working with threaded apps is quite easy with SQLAlchemy. :) On Fri, Mar 26, 2010 at 10:12 AM, Matthew Williams wrote: > 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 > > -- > 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. > > -- Fernando Takai -- 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.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
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 -- 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.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
On Mar 26, 2010, at 7:16 AM, Chris Withers wrote: Cool. What is it you're doing that needs to mix Twisted and SQLAlchemy? The project (an internal project) doesn't really *need* to mix them... I could just use mysqldb. Heh, wrong end of the stick again; my question was why you needed to use Twisted ;-) I'm using txjsonrpc to build a JSON-RPC server. If you have suggestions for alternatives to txjsonrpc, I'm certainly open to them, as this project is just getting started. Matthew -- 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.
RE: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
> -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.
Re: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync
Matthew Williams wrote: "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 you have the wrong end of the stick. SQLAlchemy and threads play fine, SQLAlchemy and Twisted's asynchronous model, which refuses to use threads on principle, do not, for the reasons you describe. It took me quite some time to piece together everything I could find related to sAsync (which seems to be a dead but functional project), so I threw up a quick Trac page for it at http://sasync.org. Cool. What is it you're doing that needs to mix Twisted and SQLAlchemy? The project (an internal project) doesn't really *need* to mix them... I could just use mysqldb. Heh, wrong end of the stick again; my question was why you needed to use Twisted ;-) cheers, Chris -- Simplistix - Content Management, Batch Processing & Python Consulting - http://www.simplistix.co.uk -- 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.
[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. and, as far as I can tell, there's no way to get away from threads if you don't want longish queries to block your entire application. Right, SQLAlchemy doesn't play nicely with *non-threaded* environments, from my understanding, which may well be wrong ;-) It took me quite some time to piece together everything I could find related to sAsync (which seems to be a dead but functional project), so I threw up a quick Trac page for it at http://sasync.org . Cool. What is it you're doing that needs to mix Twisted and SQLAlchemy? The project (an internal project) doesn't really *need* to mix them... I could just use mysqldb. Matthew -- 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.
