On Jan 17, 2013, at 7:24 PM, Ken Lareau wrote: > > > On Thu, Jan 17, 2013 at 4:13 PM, Michael Bayer <mike...@zzzcomputing.com> > wrote: > > On Jan 17, 2013, at 7:01 PM, Ken Lareau wrote: > >> On Thu, Jan 17, 2013 at 3:54 PM, Michael Bayer <mike...@zzzcomputing.com> >> wrote: >> >> On Jan 17, 2013, at 6:45 PM, Ken Lareau wrote: >> >>> 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. >> >> well an object can only be "attached" to one Session at a time. so the >> tricky thing is that it's kind of easy to get objects in the Session >> sometimes when you don't want them to. like if you have A and B, and >> there's a relationship between both like A.bs an B.a, putting an "A" into a >> Session will pull in the "B" and vice versa. >> >> So if you're putting some kind of object into this other Session to be >> committed immediately, you'd have to make sure it isn't being pulled into >> your primary session. if you call object_session(someobj) on any object it >> will show you what Session it belongs to. >> >> The other option is to merge() the state of the object into another Session. >> merge() makes a copy of an object from one Session to another. Though here >> you're looking to commit those objects in the second session and not at all >> in the first so you probably should jsut make sure those objects are only in >> that one Session. >> >> That would explain things, since I have things like: >> >> with isolated_transaction(): >> host_dep.status = 'ok' >> Session.commit() >> >> Obviously the 'host_dep' is from the original session, so this isn't going >> to work. I know about merge(), >> though in this case I'm not fully certain how to use it, or if it can even >> solve issues like this. :( > > OK you could merge in a situation like that: > > with isolated_transaction(): > merge_host_dep = Session.merge(host_dep) > merge_host_dep.status = "ok" > Session.commit() > > but now your "host_dep" is in a different state as "merge_host_dep", and that > row has a different value in the "isolated" transaction than the one locally. > if you updated it locally, then you'd see that same UPDATE statement happen > again and it might even hit upon a conflict when you try to commit the main > transaction. > > the "using two transactions" pattern is sort of best if you can isolate rows > that are intended for one side or the other. > > Sadly in this case... I can't isolate, at least not very easily. The code is > involved enough > that extracting the necessary parts to isolate a given object for change > would be very, > very difficult, if not impossible. Unfortunately this may require me to > completely rewrite > large parts of my application. :( > > I do thank you for all the help you've given so far, though. It's been very > much appre- > ciated.
since you're refactoring, perhaps there's a way to break out the things that need to be visible into new rows of their own ? > > - Ken > >> >> - Ken >> >> >> >> >>> >>> - 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. >> >> >> -- >> 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 > > > -- > 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.