Thanks, that does clear up some of the components for me. On Tuesday, 25 March 2014 16:05:26 UTC-4, Simon King wrote: > > On 25 Mar 2014, at 19:05, Tim Tisdall <tis...@gmail.com <javascript:>> > wrote: > > > I've been reading through the SQLAlchemy docs for a while now and am > still fairly confused about using SQLAlchemy with threaded processes. > Hopefully my specific question helps me understand things a little more... > > > > I'm using the Pyramid framework with SQLAlchemy. The default scaffold > for this setup has the following line: > > > > DBSession = > scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > > > I understand this essentially sets things up so in my web application I > can just do `DBSession.query(...)` and it automatically manages sessions > for me based on each web request. There's also some sort of interaction > with this and the `pyramid_tm` transaction manager. This is all fine for > my regular web application. > > > > My problem comes up when I'm trying to create a portion of my > application which spawns several threads to listen for other tcp > connections and then save results in the database. Right now that portion > of the application maintains a 20-thread pool to handle incoming > connections. I'm passing the DBSession into the function that spawns the > threads and then it's used as a global variable similar to how the rest of > the web application uses it. I wanted to find out if this is the proper > way of handling this. > > > > I think since the sessions are thread-local there isn't any session > sharing across threads. However, I'm not sure how the "scoped_session()" > works in these long-running threads. I'm a little concerned that it's > opening a transaction and then sitting on it without ever committing... > I'm really not sure. > > > > Should I change it so my threads instead use something like: > > > > with transaction.manager: > > DBSession.add(SomeORMObject(id=4)) > > > > or: > > > > with DBSession.begin(): > > DBSession.add(SomeORMObject(id=4)) > > > > Will that commit the transaction and then release the session connection > at the end of the context? > > In your pyramid app, there are a few different packages interacting. First > is the “transaction” package, which provides an abstract idea of a > “transaction” (not necessarily a database transaction) which can be > committed or rolled back. Other packages register to be part of the > transaction. > > Second, the zope.sqlalchemy package is the bridge between SQLAlchemy and > the transaction. By using the ZopeTransactionExtension in your DBSession, > you are ensuring that the transaction package will manage SQLAlchemy > transactions. > > Finally, the pyramid_tm package is the bridge between pyramid and the > transaction package. It ensures that a transaction is started at the > beginning of a request and committed or rolled back at the end of the > request. The important part of pyramid_tm is pretty short and is probably > worth looking at: > > > https://github.com/Pylons/pyramid_tm/blob/master/pyramid_tm/__init__.py#L55 > > To use SQLAlchemy transactions in your thread pool, you can either use the > SQLAlchemy APIs directly, or you can use the ones from the transaction > package. If you are using anything else that integrates with the > transaction package (eg. pyramid_mailer), you’ll probably want the second > option. Otherwise, it probably doesn’t matter much. > > In your thread pool, you’ll want to make sure that each piece of work > starts a new transaction, and either commits or rolls back the transaction > at the end, then finally closes it. Closing it will return its connection > back to the connection pool. > > It is recommended to keep the session mechanics separate from the main > body of your code. So you should probably try to structure your thread pool > such that it sets up the session, calls a separate function to do the > actual work, then commits or rolls back the transaction as appropriate. > This is described at: > > > http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it > > > The SA APIs you need are basically Session.begin, Session.commit, > Session.rollback and Session.close. The transaction ones are > transaction.manager.begin, transaction.manager.commit and > transaction.manager.abort. (Note that zope.sqlalchemy will close the > session after a commit or abort) > > Hope that helps, > > Simon
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
