[sqlalchemy] Dangers of setting a session's expire_on_commit=False?
Hi all - so I'm thinking of disabling the expire_on_commit property for my default sessionmaker object, and I was wondering what the potential issues with this were. Is it simply that the next access of the data on it could be using out-of-date information? Don't objects potentially have this problem anyway, in the sense that if they are accessed TWICE after a commit, the second access will use the data cached from the first, and could again be out of date? To give some background - we're in the middle of converting an existing codebase to use sqlalchemy, and there are number of classes that act both as database wrappers, AND data structures. That is, when first constructed, they are populated with data from the database; but from then on out, they just keep the cached data. So they would behave similarly to ORM-mapped objects, if expire_on_commit is False. The thinking here is that for most of these classes, the data changes fairly infrequently, and it's not catastrophic if it's somewhat out of date. Also we don't want to keep hitting the database more than necessary... and, finally, we might need to have access to the cached data for a long time (ie, as long as the user has a ui window open). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Dangers of setting a session's expire_on_commit=False?
On Jul 25, 2014, at 6:22 PM, Paul Molodowitch elron...@gmail.com wrote: Hi all - so I'm thinking of disabling the expire_on_commit property for my default sessionmaker object, and I was wondering what the potential issues with this were. Is it simply that the next access of the data on it could be using out-of-date information? pretty much, yup Don't objects potentially have this problem anyway, in the sense that if they are accessed TWICE after a commit, the second access will use the data cached from the first, and could again be out of date? only if you have a low transaction isolation level set up. The Session tries to make a choice as to the most reasonable place that concurrent changes should be anticipated. Transaction demarcations are the best place. If you are expecting to code your app to specifically expect READ COMMITTED or READ UNCOMMITTED behavior where your transaction relies upon seeing a row change value from a concurrent transaction, that's a special use case, in which case you can use expire() for those object that have this requirement. The ORM Session can obviously not guess when such an expiration is to be detected otherwise. To give some background - we're in the middle of converting an existing codebase to use sqlalchemy, and there are number of classes that act both as database wrappers, AND data structures. That is, when first constructed, they are populated with data from the database; but from then on out, they just keep the cached data. So they would behave similarly to ORM-mapped objects, if expire_on_commit is False. The thinking here is that for most of these classes, the data changes fairly infrequently, and it's not catastrophic if it's somewhat out of date. Also we don't want to keep hitting the database more than necessary... and, finally, we might need to have access to the cached data for a long time (ie, as long as the user has a ui window open). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Dangers of setting a session's expire_on_commit=False?
On Fri, Jul 25, 2014 at 7:55 PM, Michael Bayer mike...@zzzcomputing.com wrote: Don't objects potentially have this problem anyway, in the sense that if they are accessed TWICE after a commit, the second access will use the data cached from the first, and could again be out of date? only if you have a low transaction isolation level set up. The Session tries to make a choice as to the most reasonable place that concurrent changes should be anticipated. Transaction demarcations are the best place. If you are expecting to code your app to specifically expect READ COMMITTED or READ UNCOMMITTED behavior where your transaction relies upon seeing a row change value from a concurrent transaction, that’s a special use case, in which case you can use expire() for those object that have this requirement. The ORM Session can obviously not guess when such an expiration is to be detected otherwise. I don't see how transaction isolation levels relate to this. The effect of disabling expire_on_commit is that of not seeing subsequent commits. It would be a fictious DO NOT READ COMMITTED level. Having it on, somewhat caters to possible SERIALIZED settings, where strict ordering is to be expected, since without serialized transactions there's no way expiring helps correctness in any way. None of those seem overly common to me, so I don't see how one can ignore the serialization level in effect or possible concurrent updates that are happening at the same time, with or without expire_on_commit. IMHO, expire_on_commit is something that really has no sensible default. You pick your own, the library authors pick one default because, well, why not? For the record, I'm using expire_on_commit=off, because I also use model instances outside the scope of their originating transaction. I've had no problems with it, but I did have to be very careful with the semantics and lifetime of those objects, and of expiring manually anything I put on concurrently-accessed structures, lest someone modify it before the session's scope is over and it gets (or tries to be) committed to the DB. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Dangers of setting a session's expire_on_commit=False?
On Jul 25, 2014, at 7:16 PM, Claudio Freire klaussfre...@gmail.com wrote: On Fri, Jul 25, 2014 at 7:55 PM, Michael Bayer mike...@zzzcomputing.com wrote: Don't objects potentially have this problem anyway, in the sense that if they are accessed TWICE after a commit, the second access will use the data cached from the first, and could again be out of date? only if you have a low transaction isolation level set up. The Session tries to make a choice as to the most reasonable place that concurrent changes should be anticipated. Transaction demarcations are the best place. If you are expecting to code your app to specifically expect READ COMMITTED or READ UNCOMMITTED behavior where your transaction relies upon seeing a row change value from a concurrent transaction, that's a special use case, in which case you can use expire() for those object that have this requirement. The ORM Session can obviously not guess when such an expiration is to be detected otherwise. I don't see how transaction isolation levels relate to this. The effect of disabling expire_on_commit is that of not seeing subsequent commits. It would be a fictious DO NOT READ COMMITTED level. Having it on, somewhat caters to possible SERIALIZED settings, where strict ordering is to be expected, since without serialized transactions there's no way expiring helps correctness in any way. None of those seem overly common to me, so I don't see how one can ignore the serialization level in effect or possible concurrent updates that are happening at the same time, with or without expire_on_commit. yes, it caters the most to SERIALIZED settings, a little bit less so to REPEATABLE READ (works for individual objects but not collections), then still less to READ COMMITTED (works only to the extent that you're worried about other transactions in progress), etc. IMHO, expire_on_commit is something that really has no sensible default. You pick your own, the library authors pick one default because, well, why not? So, for a long time, all through 0.4, the default was, never, and not even possible. There was no expire on commit, at that time I thought it was insane to throw away all that great data that you've loaded unless you absolutely want to. As it turns out the current defaults are not by accident! We had a pretty steady parade of users who complained that their data was stale, and for years I scratched my head, how are we do to this? just blow away all objects all the time on every query? that seemed so wrong, so wasteful, and of course so complicated since we would want pending changes to remain around. As I've written many times, it was the Storm ORM that introduced me to the concept of expire on commit. The linkage to the transaction is also kind of where Hibernate and JSR 220 is coming from, though not necessarily wiping out the object on commit...the spec doesn't make that aspect very clear. Overall the expire on commit idea is very strict and assumes entities are row proxies only. For the record, I'm using expire_on_commit=off, because I also use model instances outside the scope of their originating transaction. I've had no problems with it, but I did have to be very careful with the semantics and lifetime of those objects, and of expiring manually anything I put on concurrently-accessed structures, lest someone modify it before the session's scope is over and it gets (or tries to be) committed to the DB. Maybe expire on begin might be useful. So your data is still there after commit, but if you start a new transaction, then things refresh. I'm up for it in a 1.0 release, if you think it's useful. Though explaining to everyone another option...what a PITA -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Dangers of setting a session's expire_on_commit=False?
On Fri, Jul 25, 2014 at 10:09 PM, Michael Bayer mike...@zzzcomputing.com wrote: For the record, I'm using expire_on_commit=off, because I also use model instances outside the scope of their originating transaction. I've had no problems with it, but I did have to be very careful with the semantics and lifetime of those objects, and of expiring manually anything I put on concurrently-accessed structures, lest someone modify it before the session's scope is over and it gets (or tries to be) committed to the DB. Maybe expire on begin might be useful. So your data is still there after commit, but if you start a new transaction, then things refresh. I'm up for it in a 1.0 release, if you think it's useful. Though explaining to everyone another option...what a PITA I don't see how that'd work. On session.add? When set as dependent of an object of another (active) session? Seems very error prone to make it too automatic. All in all, the current state isn't bad. It's understandable, and controllable. I'm just saying there's no one-size-fits-all. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
