On Jan 17, 2009, at 2:10 AM, Saju Pillai wrote:
> The sequence of events I mentioned were incorrect. The following > happens > > session.begin_nested() > session.delete(some_obj) > session.commit() > > if some_obj in some_other_obj.foreign_key_based_collection: > ---> returns true > > where some_other_obj_table & some_obj are in a 1 to many fk > relationship and some_other_obj.foreign_key_based_collection is a > sqlachemy instrumented list generated by sqlalchemy. > > Turning on the logs made it clear that accessing the collection was a > pure python-memory operation. The delete(some_obj) in the nested > transaction actually did not update all SA collections that some_obj > was a member of (whereas a top level commit - does update such > collections). For now, I am explicitly expiring some_other_obj after > the nested transaction is committed - the next access of > some_other_obj is rebuilding the collection from the DB. this issue is usually dealt with via a cascading delete from the parent object. this is probably our most common gotcha and is mentioned here: http://www.sqlalchemy.org/trac/wiki/FAQ#Imcallingdeletemyobjectanditisntremovedfromtheparentcollection > > This a stateful session aware webapp responding to HTTP requests - > concurrent access is expected including sessions modifying data that > other sessions may be reading/modifying. In such a scenario would you > recommend many small transactions as compared to fewer larger > transactions per HTTP session ? Our HTTP sessions are expected to live > long. the pattern for HTTP applications is clear - there is a Session/ transaction per individual HTTP request. You should never hold a real database transaction over multiple HTTP requests. database transactions are very expensive in terms of concurrency and should be very short lived. Approaches towards creating a "long running transaction" that doesn't actually keep the database locked open include using an HTTP session object as a holding zone for pending objects, or if you'd prefer to store pending items in database tables (which is often recommended vs. the "bag of objects" model of a typical HTTP session object), you'd have to structure your tables to have some awareness of state - either using a separate set of tables for "pending" rows, or by using a status column. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---