On Nov 13, 2011, at 11:52 AM, Vlad K. wrote: > > > > Hi, thanks for your reply. > > > > On 11/13/2011 05:15 PM, Michael Bayer wrote: >>> From what I've read in the docs, I am supposed to do session.refresh(), >> No that's not required at all. All the objects that are still referenced >> outside the session, stay present in the session and will reload their data >> when accessed. > > > But they're not, I'm getting Instance XY is not present in this Session, for > readonly_model when method_two is called, if there was a rollback in > method_one.
That would indicate you add()-ed it during the transaction. Any data that was created during the transaction is gone - that has to be regenerated. You should really run the whole series of steps completely when a transaction fails. > Now, what I forgot to say is that I'm doing this within a Pyramid application > which uses ZODB Transactions, so I can't directly access session.commit() > .rollback() or .savepoint(). I am going to ask that you take this message to the Pyramid list, and tell them I sent you. I just came back from Ploneconf and met with Chris, Lawrence, and everyone else who uses zope.transaction heavily. I learned about it and gained new appreciation for it. But my skepticism was all about, "what about this use case ?". So here's another example where SQLAlhcemy by itself doesn't have an issue, but zope.transaction either has a different way of working here, or doesn't yet handle this case. But assuming my suggestion below regarding SAVEPOINT isn't doable, it's a zope.transaction issue. > I am not quite sure what else does Transaction do when it encounters a failed > state, I've already asked on the Pyramid mailing list about having multiple > transactions within the same request, waiting to resolve that issue. I'm going to get on there right now and fast track it >> What you will lose is whatever pending changes were present in method one, >> whatever data was flushed but not committed. >> >> But when you say method one "proceeds within a new transaction" - doesn't >> that imply that it re-tried its full series of steps, so that the required >> state is apparent at the end of method one ? if method one is where the >> data is being set up for method two, it of course would have to re-establish >> that state for which method two is dependent. > > The issue is basically this. At the end of a HTTP request, after all "main" > data has been read, changed and flushed to db, I have to make certain > statistical entries for several relations. Now, these statistical entries > have current date as one part of the primary key, meaning each row is one DAY. > So the stats recording method (two calls beacuse there are two statistical > models/tables) tries to insert date as new row, and if it fails (has already > been set by another process) with IntegrityError, then it tries to update > existing rows. I can't merge, because between merge's select and decision > whether to insert or update another process may have created the entry, so I > would STILL have the IntegrityError and have to retry the steps. I'd use begin_nested() here so that the occasional IntegrityError can be rolled back within the context of a larger transaction. SAVEPOINT here is most useful for the single INSERT that has this kind of issue. You catch the exception, then rollback - the surrounding transaction remains intact. You can then try again, within the context of a loop just around that one INSERT statement. > > Anyways, even if I catch IntegrityError, the transaction fails with > "TransactionFailedError: An operation previously failed, with traceback". > Meanwhile (since posting this original question) I've learned that the error > belongs to Zope and not SQLAlchemy per se, and this whole problem may be > because of that. > > I've also tried with savepoints so that the integrity error above would > rollback only THAT attempt, but I still encounter the > TransactionFailedError... you get the integrity error, then emit a rollback, but the SQLAlchemy session knows to only rollback to that SAVEPOINT. If zope.transaction is not letting you do that then either you'd need to use some API specific to zope.transaction here, they'd need to add one, or they need to enhance zope.transaction to interpret when a SQLAlchemy rollback is against a SAVEPOINT and not the outermost transaction. > > So I'm guessing that the method one and two can simply use savepoints and > isolate their own failures from the "outside" transaction? That way I > preserve logical isolation of the code, keeping in mind only that all data > will be flushed with savepoint. Which then makes this problem coming from the > Pyramid framework implementation with ZODB transaction. I'm assuming you're mixing the terms "ZODB transaction" and "zope.transaction" here, as its the latter which integrates with SQLAlchemy, from my understanding. The ZODB is just one of many kinds of data sources that can participate in a zope.transaction. > >> if it were me, I'd never be even trying to "redo" an operation that rolled >> back. 99% of the time if my app has to rollback a transaction in the >> middle of an operation, that's a bug in the application no different from a >> crash. > > But with IntegrityErrors there is no other way? Relying on this error keeps > the atomicity within the MVCC of the database (PostgreSQL in my case) and not > in the application (via merge, or select for update -- which btw can't be > done for inserts). this is absolutely the SAVEPOINT use case and zope.transaction should be able to support this. -- 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.
