Yeah, sounds very reasonable, my first thought was to isolate a session to each subprocess. The only reason I thought about working with detached objects is because I'm having trouble reproducing the problem, and I thought if I worked clean sessions, these problems shouldn't occur.
Thanks for your time Mike. //J On Tuesday, 28 August 2012 16:25:47 UTC+2, Michael Bayer wrote: > > > On Aug 28, 2012, at 2:54 AM, Jakob D. wrote: > > > Shouldn't open transactions within the session close when I issue a > session.remove() > > yes. > > > > > > Does an open transaction mean I cannot issue any queries at all before > closing them? > > You can issue as many queries as you want at any time. the transaction > accumulates state as these queries proceed. The isolation level of the > transaction, which is usually configurable, determines how this transaction > will interact with other concurrent transactions going on, to what degree > concurrent data will be exposed, etc. A good overview is at: > http://en.wikipedia.org/wiki/Isolation_%28database_systems%29 > > > The service is basically a set of subprocesses doing the same thing. > > After each iteration of the subprocess, the session is removed, and any > exceptions will issue a rollback. > > OK, this is something you need to be careful with, if your program is > using fork() or some variant (subprocess, multiprocessing, etc). A DBAPI > connection generally will not travel along process boundaries. A Session > also, when passed across a process boundary, naturally creates a second > Session, since that's how child processes work. If you are calling > close()/remove() only in the parent/child, you'd still have a second > Session where this has not been done. > > Inside of a new fork(), you need to be creating a brand new engine using > create_engine(), which establishes connection state local to that fork. > Then you need to establish a brand new Session to go along with it. If > you're using scoped_session(), making sure the registry has no Session when > the fork starts, by calling scoped_session.remove(), is a good idea. > > Objects that are sent along the process boundary should be added to the > subprocess Session using either merge() or add(). > > > > Besides remove and commit, what can I do to close open transactions? A > commit should issue a flush? > > close() the session, or remove(), it's done. > > > > > I thought I should explicitly create a new session, in case the remove > fails and as you said, to make sure I'm using the session that's expected. > > I also thought about doing this for each query and then removing the > session right after, maybe working with detached objects. > > > > I thought this way, there should be no problems with the session being > in a undesirable state from a previous iteration since I'm always working > on a fresh session. > > working with detached objects and many new sessions, such that you are > leaving the objects detached as their normal state and creating sessions > just for ad-hoc queries, is not a good way to approach the problem, and > works against the design of SQLAlchemy as well as the database itself. > > When an object is detached, it pretty much shouldn't be used at all except > to store it in a cache, or to place it into another Session. So if your > detached object pattern is just so that you can transfer objects over > subprocess boundaries, then its OK. But the parent process and child > process should have just a single Session at a time (per thread, but I > assume you're not using threads). When objects are in use, they should be > associated with a Session. The lifespan of the Session should cover a > regular series of operations against a field of objects. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Vmpab6oYo-cJ. To post to this group, send email to sqlalchemy@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.