On Jul 22, 2011, at 5:08 PM, Moch Ramis wrote: > 2011/7/22 Michael Bayer <mike...@zzzcomputing.com> > > > > Here comes the problem: when an Integrity errors occurs, a rollback is done > > and every sqlalchemy records are expired, thus, the insert takes 3.42 > > seconds... and 3.41 seconds are spent on the "restore snapshot" function ... > > well the reason for that is twofold: one is because some other concurrent > operation might have modified the data in between when your transaction is > removed and when the new one starts. Another is that the flush() process may > have modified the state in memory based on database state, that is now > invalid - that is, newly generated primary key identifiers have been assigned > to objects, foreign key attributes have been synchronized, column level > defaults. When a ROLLBACK occurs, all of those values are invalid. Hence > they're expired. Objects that you add()'ed are expunged - these may have > DB-generated defaults and identifiers that are now invalid. This is all so > that proceeding after the ROLLBACK, the Session is in a consistent state. > > The point is that i don't need some up to date objects : I could copy them > into my own structures (if I had such) and delete them, the algorithm would > still work. What I need is a "snapshot" of a group of objects at a given > instant. If it was possible to have the session forget about the objects I > retrieved, I think it would do the job.
expunge() will remove any object from the Session > > > its unlikely that flag will ever be removed but it does open you up to the > consistency problems mentioned above. The flag is underscored so that users > aren't confused by the question if they should be using it or not. (i.e. if > they are not sure, the answer is no). > > My main problem would be that if i use this flag, i would need multiple kind > of sessions that may complicate the code. not sure what that means or what you'd be looking for here. There's a flag, can turn the behavior off. What more are you requesting ? > > I approach the "SELECT...THEN INSERT" issue by SELECTING the full expanse of > data I'm to work on in one big statement first, placing the results in a > dictionary or similar using keys tailored towards the operation in progress. > I then use the dictionary to consult for rows that already exist. > > Here comes the problems: I'm not the only user of the database so its state > may have been updated inbetween. And I can't lock that much rows during the > whole process either. not if you're using isolated transactions. this is the purpose of transaction isolation. A high level of isolation will lock out concurrent changes. > > Well each time I get an integrity error, i call the rollback method and i > don't plan to continue the transaction or keep being in an unstable state. > The only problem is that I need some minimum performances and the current > rollback (or even commit if I had not set expire_on_commit to true) durations > are not acceptable. I'm not sure what can be done besides leaving the transaction accounting flag turned off. If you don't want a ROLLBACK at all, the ORM Session should probably be skipped here and you'd use insert() constructs directly. Otherwise the flush() does not support partial execution. If it runs, then fails in the middle, it has to roll back. It cannot pick up where it left off midway, as it does not complete its finalized accounting until the end of the successful flush() operation. -- 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.
