wow, thanks for the replies. I really appreciate the quick responses. I've been "using" sqlalchemy for a few years now, and I have been very impressed by the turnaround on questions and bugs when dealing with this group. Again, thank you.
I have just a few more (inline) comments below On Apr 15, 10:32 pm, Michael Bayer <[email protected]> wrote: > On Apr 15, 2009, at 8:57 PM, dykang wrote: > > > > > ah, but since the ORM actually is forced to execute the query anyway, > > why not update the object in the identity map with the correct data, > > and raise an exception if the current object is dirty? It seems bad > > procedure > > to be loading an object for update when it's already been modified. > > I tried an experiment with postgres using serializable isolation, and > as it turns out if you've already read the row into the current > transaction, then a concurrent transaction modifies the row, then you > request select...for update of that same row a second time in the > first transaction, it throws a concurrent modification error. so > with serializable, the primary isolation mode we have in mind, the use > case never comes up. > indeed, humorously enough, I had never really considered the now obvious fact and serializable would be the primary isolation mode in mind for sqlalchemy. > if you're in read committed, it re-reads the latest data from the row > from the outside regardless of the usage of FOR UPDATE or not. the PG > docs don't say anything about FOR UPDATE changing the isolation > characteristics of the SELECT...only the locking (and as you can tell > we're using PG, not MySQL, as the baseline for "best" behavior). So > to really work with non-serializable isolation and have the ORM return > data similar to what the database does, data should always be > refreshed with every SELECT, not just those with FOR UPDATE. the This is true. And for some reason I took for granted that this is how it would actually work when I read that the identity map was not a cache. It makes sense that it does not work this way by default, especially given your isolation target. > autoflush feature, which is generally turned on, prevents the issue of > any pending data being overwritten - its always flushed out before the > SELECT occurs. > > This is a lot simpler to implement, that of "populate_existing" on at > all times, and would possibly be a flag that folks could use if they > decided they are expliclty using non-serializable isolation, would > like to have the details of that behavior available to them (i.e. they > really want the same row to change its value throughout the > transaction), and they're willing to take the performance penalty of > re-populating all attributes every time. > > my reasons for not enabling this by default are that its a performance > hit and would be disastrous to use without autoflush. If it were to > work in theory without autoflush, it would have to verify attributes > as having no pending changes before populating, else raise an error Are objects not marked as dirty on changes? Do you check each attribute of each object in the session on every flush? If this process has poor performance, doesn't autoflush suffer the same consequences? > The performance and complexity overhead of that would be infeasable, > not to mention that it's solving a problem that is better solved by > choosing a stricter isolation level. Maybe I'm confused, but I'm pretty confident that there are valid reasons to allow for not choosing a stricter isolation level. It may be the case that I have to accept that I'm using a system that is not the SQLA target, but I don't believe a stricter isolation level is always a good thing. In fact the postgresql documentation even states: "Since the cost of redoing complex transactions may be significant, this mode is recommended only when updating transactions contain logic sufficiently complex that they may give wrong answers in Read Committed mode." > Keeping the feature specific to > just FOR UPDATE doesn't seem to address the full need of "i want to > work in non-serializable isolation", since any SELECT returns fresh I agree completely. > data. Maybe the FOR UPDATE case more strongly suggests the feature > than the non FOR UPDATE case, but I can't make that decision across Again, this makes a lot of sense. But my experience has always been that this IS the case. It is completely foreign to me that a load FOR UPDATE would even possibly return stale data, and I was completely shocked when I discovered the behavior. However, my experience has been limited to oracle read committed and mysql (repeatable read and read committed). I have never worked in a serializable isolation environment, so I'm not used to this case not coming up. One situation where the FOR UPDATE case could be different would be if the row were already loaded FOR UPDATE previously in this transaction. If this has already happened, we wouldn't even want to check if the row was dirty, we'd probably want to accept what what was in the identity map, since we already hold a lock on the row. I guess it's more different than I thought it would be. > the board for all users without a broader discussion - in particular > we support many different databases, and who knows what each one does > with each kind of isolation they offer. MSSQL can always be counted > on to blow up any assumptions you've made. > > there is an ancient flag on mapper() called "always_refresh" which is > the equivalent of "populate_existing" always turned on, but in modern > SQLA this would be better suited as a Session flag. The behavior can > be achieved in any current SQLA release by using a Query subclass that > turns on its populate_existing() flag at construction time. I see. Again, I appreciate the responses. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
