Sadly, it looks like when I try the code in my full application, it is failing miserably. I'm seeing errors like this:
sqlalchemy.orm.exc.DetachedInstanceError: Instance <Deployments at 0x3367910> is not bound to a Session; attribute refresh operation cannot proceed and sqlalchemy.exc.InvalidRequestError: Object '<HostDeployments at 0x21cd050>' is already attached to session '1' (this is '3') Sadly it's not immediately obvious as to what's going on... not even certain how to start debugging this problem. - Ken On Tue, Jan 8, 2013 at 2:59 PM, Ken Lareau <klar...@tagged.com> wrote: > On Tue, Jan 8, 2013 at 2:16 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > >> >> On Jan 8, 2013, at 4:00 PM, Ken Lareau wrote: >> >> >> Given this and your previous comments, and after some conversations with >> a coworker, I decided to expose the 'engine' variable from the database >> library and create a context manager as so: >> >> @contextlib.contextmanager >> def isolated_transaction(NewSession=None): >> """Manage a new transaction (session) within an existing session""" >> >> needed_session = True >> existing = Session.registry() >> >> if NewSession is None: >> NewSession = scoped_session(sessionmaker(bind=engine)) >> else: >> needed_session = False >> >> Session.registry.set(NewSession) >> >> try: >> yield >> finally: >> Session.registry.set(existing) >> >> if needed_session: >> NewSession.close() >> >> >> (This code hasn't quite been tested yet and may need some tweaking) >> With this, whenever I need an 'isolated' transaction I can simply do >> from my program: >> >> with isolated_transaction(): >> <do stuff with Session> >> >> And things should 'just work'... at least that's my hope! I can also pass >> an existing new session if I want to use one in multiple places (not >> sure I'll need that just yet, but it was easy enough to write into the >> method, so...) >> >> Hopefully this seems like a sane solution to my problem. :) >> >> >> its good, though you can save yourself all the trouble with >> scoped_session/sessionmaker by just saying this: >> >> from sqlalchemy.orm import Session >> new_session = Session(bind=engine) >> >> or which might be even more portable, use the sessionmaker from your >> existing registry: >> >> new_session = Session.session_factory() >> >> session_factory is the sessionmaker() you stuck onto scoped_session in >> the first place. In that case you don't need to refer to the Engine >> explicitly. >> > > Nice, the second one definitely works and I no longer need to export > 'engine' > and only need to import Session from my library where I have the context > manager place. :) > > Thanks again! > > - Ken > > >>>> Depending on the database in use, using low isolation levels can have >>>> the effect that other transactions can view "dirty reads" as the >>>> transaction proceeds, but this is obviously an all-or-nothing thing. >>>> When I need certain resources exposed during a long running transaction, I >>>> transfer that data to a different Session and commit() those changes >>>> distinctly. >>>> >>>> My current needs would tend to use the short transactions for things >>> that are mostly >>> isolated from anything going on in the longer running (main) >>> transaction, though I do >>> suspect I might need what you mention in your last sentence, but might >>> you be able >>> to refer me to an example of how it would work, perchance? >>> >>> >>> I'd advise against going this route, you'd pretty much need to use MySQL >>> MyISAM tables to get guaranteed "dirty reads", that is, there's no >>> transaction at all, and it's not really how transactions were meant to be >>> used. Lowering the isolation level is usually just a means to get more >>> transaction throughput. >>> >> >> Okay, I suspect I misunderstood what was being mentioned here, which is >> no problem; I'll avoid it. :) Thanks once again for all the help. >> >> -Ken >> >> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "sqlalchemy" group. >>> 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. >>> >> >> >> >> -- >> - Ken Lareau >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> 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. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> 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. >> > > > > -- > - Ken Lareau > > -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. 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.