DBSession in Threads/remote processes

[Sean DiZazzo]

Hi,I have a Turbogears 2.1.2 app that kicks off a long running
parallel task. The task is run in many threads and on several
machines,and they all need to use the model to update the database.
What are the best practices for running DBSessions in threads? I
found somewhere that I should create 1 scoped_session per thread, and
I am generally doing that with the following initialization code:

engine = create_engine('mysql://user:pass@localhost:3306/
foo',echo=False)
DeclarativeBase = declarative_base()
maker = orm.sessionmaker(autoflush=True, autocommit=False)
DBSession = orm.scoped_session(maker)
maker.configure(bind=engine)

Is that right?

Since I'm creating a new session and not using the one from the model,
do I need to commit()? I assume it is not safe to pass around
sqlalchemy results between threads. Do I need to explicitly close()
the session when I am done in each thread? Will I run in to problems
if 20 different threads with 20 different sessions are accessing the
database at the same time? I thought I saw some kind of config
regarding connection_pool...is this related?

Would it be better to create a singleton that holds the session and
then instantiate that wherever I need it? I have done that with
SQLObject before and it worked great.

Sorry for all of the questions. I have been trying all kinds of
things and I can't seem to get it quite right. Thanks.

~Sean

[Sean DiZazzo]

I found this sqlalchemy doc which answers alot of those questions:
http://www.sqlalchemy.org/docs/core/pooling.html

[Sean DiZazzo]

and this: http://www.sqlalchemy.org/docs/core/connections.html

I should have RTFM.

[Alessandro Molina]

On Wed, Oct 5, 2011 at 5:36 AM, Sean DiZazzo <half.i...@gmail.com> wrote:
> Hi,I have a Turbogears 2.1.2 app that kicks off a long running
> parallel task.  The task is run in many threads and on several
> machines,and they all need to use the model to update the database.
> What are the best practices for running DBSessions in threads?  I

You can try tgext.asyncjob it might do most of the work for you.
It will manage the session for you letting you just use the
model.DBSession inside the asynchronous threads.

About the session, by default TurboGears already provides a
sessionmaker which will create one scoped_session for each thread that
will try to use the DBSession object. The main issue is that TG
patches the session to use a different transactions manager which runs
inside the TG middleware stack by committing and reverting session
whenever is necessary. When you use the session outside of a request
anyway this middleware is not in place and so you will have to manage
the session yourself. By default tgext.asyncjob implements a worker
that behaves like TG, committing the session at the end of the async
function and reverting it in case of an exception.

Also pay attention that when you query an object from a different
thread it might not be available even thought you are sure that it
should be, to avoid this issue tgext.asyncjob provides a
asyncjob_timed_query that tries to retrieve an object from the
database multiple times until it excedes timeout.

[Sean DiZazzo]

On Oct 5, 4:08 pm, Alessandro Molina <alessandro.mol...@gmail.com>
wrote:

> On Wed, Oct 5, 2011 at 5:36 AM, Sean DiZazzo <half.ital...@gmail.com> wrote:
> > Hi,I have a Turbogears 2.1.2 app that kicks off a long running
> > parallel task.  The task is run in many threads and on several
> > machines,and they all need to use the model to update the database.
> > What are the best practices for running DBSessions in threads?  I
>
> You can try tgext.asyncjob it might do most of the work for you.
> It will manage the session for you letting you just use the
> model.DBSession inside the asynchronous threads.
>
> About the session, by default TurboGears already provides a
> sessionmaker which will create one scoped_session for each thread that
> will try to use the DBSession object. The main issue is that TG
> patches the session to use a different transactions manager which runs
> inside the TG middleware stack by committing and reverting session
> whenever is necessary. When you use the session outside of a request
> anyway this middleware is not in place and so you will have to manage
> the session yourself. By default tgext.asyncjob implements a worker
> that behaves like TG, committing the session at the end of the async
> function and reverting it in case of an exception.

I have seen that code before and it's very nice. It won't quite work
on my project, but I will try to steal the relevant transaction
parts. Parts of my code are not run in the typical Turbogears
controller and run as daemons on other boxes. I have tried to create
the same environment on those client boxes, but when I switched the
code to use the DBSession from tg, I started getting errors like:

UnboundExecutionError: Could not locate a bind configured on mapper
Mapper|Job|jobs, SQL expression or this Session

I think some part of the environment is not set correctly. What is
the minimum I need to do to be able to use the DBSession without these
errors? I see lots of posts regarding this issue. Would following
this solve the issue? http://permalink.gmane.org/gmane.comp.web.turbogears/43261
or is there an alternative suggested way? Thanks!

~Sean

[Julien Tayon]

I have worked with antoine :)

I have similar scripts to his for user/group setup I will send you
samples tomorrow with the argparse stuff for selecting the ini file.

It is no rocket science you might find the solution before tomorrow.

Le 6 oct. 2011 à 18:56, Sean DiZazzo <half.i...@gmail.com> a
écrit :

> --
> You received this message because you are subscribed to the Google
> Groups "TurboGears" group.
> To post to this group, send email to turbo...@googlegroups.com.
> To unsubscribe from this group, send email to turbogears+...@googlegroups.com
> .
> For more options, visit this group at http://groups.google.com/group/turbogears?hl=en
> .
>

[Sean DiZazzo]

> About the session, by default TurboGears already provides a
> sessionmaker which will create one scoped_session for each thread that
> will try to use the DBSession object. The main issue is that TG
> patches the session to use a different transactions manager which runs
> inside the TG middleware stack by committing and reverting session
> whenever is necessary. When you use the session outside of a request
> anyway this middleware is not in place and so you will have to manage
> the session yourself. By default tgext.asyncjob implements a worker
> that behaves like TG, committing the session at the end of the async
> function and reverting it in case of an exception.

Your code has got me most of the way there. Thank you. I got caught
by the timed_querys and another unexpected gotcha. I didn't realize
that you need to query your object within the transaction, or else it
will exist in a different session. I would update the attributes and
commit, but the changes never showed up in the database. It took me a
few hours at least to finally figure it out. It was a good, but
painful learning experience.

I now have a working prototype with everything I need. Now to go back
through and clean it up. ugghhh

Thanks for your help.

~Sean

[Sean DiZazzo]

On Oct 6, 12:18 pm, Julien Tayon <jta...@gmail.com> wrote:

> It is no rocket science you might find the solution before tomorrow.

Thanks Julien. It looks like the minimum sqlalchemy config needed to
configure DBSession is:

engine = create_engine(url)
model.DBSession.configure(bind=engine)

The rest is just for config.

~Sean

[Julien Tayon]

Tsss as I was being harassed at my job I was hoping you may fail
miserably so that I could sadisticly pop out the solution and pop out
an hidden man page.

There is no little pleasure

A thanks though is bigger pleasure. :) thanks
Hf gl

Le 7 oct. 2011 à 01:02, Sean DiZazzo <half.i...@gmail.com> a
écrit :

> On Oct 6, 12:18 pm, Julien Tayon <jta...@gmail.com> wrote: