Re: [sqlalchemy] How to handle 'sub-commits'?
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.comwrote: 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
Re: [sqlalchemy] How to handle 'sub-commits'?
On Thu, Jan 17, 2013 at 4:13 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 17, 2013, at 7:01 PM, Ken Lareau wrote: On Thu, Jan 17, 2013 at 3:54 PM, Michael Bayer mike...@zzzcomputing.comwrote: 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. - 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.comwrote: 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
Re: [sqlalchemy] How to handle 'sub-commits'?
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
Re: [sqlalchemy] How to handle 'sub-commits'?
On Thu, Jan 17, 2013 at 4:26 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 17, 2013, at 7:24 PM, Ken Lareau wrote: On Thu, Jan 17, 2013 at 4:13 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 17, 2013, at 7:01 PM, Ken Lareau wrote: On Thu, Jan 17, 2013 at 3:54 PM, Michael Bayer mike...@zzzcomputing.comwrote: 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 ? With the current base design, that may not be possible. I could get into the gory details about what the application is doing and how it's interacting with the database, but I fear it may be more than the rest of the people on the mailing list would like to hear. - Ken - 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
Re: [sqlalchemy] How to handle 'sub-commits'?
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. 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.
Re: [sqlalchemy] How to handle 'sub-commits'?
On Tue, Jan 8, 2013 at 2:16 PM, Michael Bayer mike...@zzzcomputing.comwrote: 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 -- 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.
Re: [sqlalchemy] How to handle 'sub-commits'?
On Jan 7, 2013, at 1:11 AM, Ken Lareau wrote: Okay, this is what I suspected and feared. :) Creating new sessions isn't much of an issue, and I came up with a class to manage this for me before realizing my problem is going to end up being much deeper... My current library that uses SQLAlchemy was based off a single session... so in a file called meta.py I had the following line: Session = scoped_session(sessionmaker()) (swiped from an old Pylons example). In my __init__.py file, I did: from tagopsdb.database.meta import Session and then in when initializing my connection to the database, simply did: Session.configure(bind=engine) From all other areas of the library, I simple re-used the aforementioned import, then actually directly used Session, such as: Session.add(obj) or Session.commit() Now... this may be very poor usage of it, though I'm trying to improve my under- standing and utilize SQLAlchemy better. that's pretty much the usage we've encouraged for a long time, the Session is basically the main Session, I think its OK. Of course, with the sudden need for more than one session, I'm finding that I'm running into an issue. Specifically: 1) To make it easy to find the correct session, I'm using a dictionary which I pass around instead of Session... but referencing a given session is a bit clunky - 'sessions.current[name]' is quite a bit less succinct than the use of just 'Session', and while I could just assign a given entry to a shorter name, that just seems to add to the mistake. :) Depending on the usage pattern here, if the need for the extra transaction is localized, then I'd be using an explicit passing pattern for this second Session - that is, not using a global registry. If the case is more like large amounts of code are split 50/50 between these two Sessions, then I'd possibly use a second Session registry. Since this Session is more intended as an ad-hoc commit Session though I might stick to keeping it as a non-global object. 2) All the methods in my library currently expect the session to be 'Session'; that changes with the need for multiple sessions, and it means either I will now need to explicitly pass the session into every method, or find a way to have the session automatically determined... which may not be possible or reasonable. OK, well there's another path here, which is that you can affix a second Session object to your registry temporarily. existing = Session.registry() # current Session Session.registry.set(my_temporary_session) # set a different Session ... call functions ... # restore the original Session.registry.set(existing) 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. -- 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.
Re: [sqlalchemy] How to handle 'sub-commits'?
Michael, Thanks for the response, see further questions/issues below... On Fri, Jan 4, 2013 at 8:41 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 3, 2013, at 10:18 PM, Ken Lareau wrote: I recently (today) ran into an issue that has me perplexed as to how to resolve it, so I'm asking here to see if anyone can shed some insight. Hopefully I can ex- plain it clearly enough to make me not sound completely incompetent... I currently have an application that during it's run starts a session via SQLAlchemy to one of our databases and keeps it available until the program exits. During this time it does multiple changes (primarily inserts and updates) to the database, but of course nothing is actually written to the database until a commit() is done. The problem is that there are times when I have a single change that must be available in the database immediately due to external resources needing to access to that updated/new information. for this use case you use a distinct transaction, which means a different Session object. You commit() that Session when you need this short-term data to be exposed. Okay, this is what I suspected and feared. :) Creating new sessions isn't much of an issue, and I came up with a class to manage this for me before realizing my problem is going to end up being much deeper... My current library that uses SQLAlchemy was based off a single session... so in a file called meta.py I had the following line: Session = scoped_session(sessionmaker()) (swiped from an old Pylons example). In my __init__.py file, I did: from tagopsdb.database.meta import Session and then in when initializing my connection to the database, simply did: Session.configure(bind=engine) From all other areas of the library, I simple re-used the aforementioned import, then actually directly used Session, such as: Session.add(obj) or Session.commit() Now... this may be very poor usage of it, though I'm trying to improve my under- standing and utilize SQLAlchemy better. Of course, with the sudden need for more than one session, I'm finding that I'm running into an issue. Specifically: 1) To make it easy to find the correct session, I'm using a dictionary which I pass around instead of Session... but referencing a given session is a bit clunky - 'sessions.current[name]' is quite a bit less succinct than the use of just 'Session', and while I could just assign a given entry to a shorter name, that just seems to add to the mistake. :) 2) All the methods in my library currently expect the session to be 'Session'; that changes with the need for multiple sessions, and it means either I will now need to explicitly pass the session into every method, or find a way to have the session automatically determined... which may not be possible or reasonable. Given the above, I am open to alternative suggestions, especially if someone has solved this problem already. :) It looks like I will need the multiple session solution sooner rather than later, which means I have to find some way to solve this quandary of mine. So this leads to the question: is there any way to do an 'isolated' commit from within a session and if so, how is it done? As an alternative, is there a way to use temporary new sessions to accomplish the same thing? My current use in my application is I have a 'Session = scoped_session(sessionmaker())' line in a module which I import wherever I need it (essentially as a singleton) to be able to access the same session throughout the code. This would of course need to change, at least with an application requiring such 'sub commits'. 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? Once again, thanks for 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,
Re: [sqlalchemy] How to handle 'sub-commits'?
On Jan 3, 2013, at 10:18 PM, Ken Lareau wrote: I recently (today) ran into an issue that has me perplexed as to how to resolve it, so I'm asking here to see if anyone can shed some insight. Hopefully I can ex- plain it clearly enough to make me not sound completely incompetent... I currently have an application that during it's run starts a session via SQLAlchemy to one of our databases and keeps it available until the program exits. During this time it does multiple changes (primarily inserts and updates) to the database, but of course nothing is actually written to the database until a commit() is done. The problem is that there are times when I have a single change that must be available in the database immediately due to external resources needing to access to that updated/new information. for this use case you use a distinct transaction, which means a different Session object. You commit() that Session when you need this short-term data to be exposed. So this leads to the question: is there any way to do an 'isolated' commit from within a session and if so, how is it done? As an alternative, is there a way to use temporary new sessions to accomplish the same thing? My current use in my application is I have a 'Session = scoped_session(sessionmaker())' line in a module which I import wherever I need it (essentially as a singleton) to be able to access the same session throughout the code. This would of course need to change, at least with an application requiring such 'sub commits'. 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. -- 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.
