[sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
By design, when a query() fetches an existing object, it doesn't refresh the values unless populate_existing() is included with the query. The documentation for populate_existing() states it isn't meant for general purpose. Occasionally, however, objects need to be selected FOR UPDATE, with_lockmode('update'), to guarantee against timing problems with concurrent database users, particularly when the record fetched is used as a base for the update. For example, if I need to update a record's quantity field by a delta of +5, it is extremely important that I have the most recent Object.quantity datum; truly, that was the reason I used with_lockmode('update') in the first place. I get uneasy to think that the user needs to remember to invoke populate_existing() when selecting a record FOR UPDATE to guard against the possibility that there is a stale version of the instance in the session. I will likely add that to our framework's Query subclass, but thought you might consider the implications here. Generally, sqla is extremely optimistic regarding locks, but in the event when the user is specifying with_lockmode('update'), we've left the realm of optimistic locking and entered pessimistic, so it seems reasonable to consider automatically enforcing populate_existing() when with_lockmode('update') is used? Something to consider; I'd be interested in your thoughts. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
populate_existing() blows away any pending changes on the object so turning it on by default would be a surprise in a lot of cases. typically if someone is working with FOR UPDATE they're already programming a very specific section in a very careful manner, it's not something that's done casually. I would think that it would be used to select a row right at the start of a transaction, that is definitely not already in the Session. in my own experience, any time I've actually tried to work with pessimistic locking my application ends up deadlocking at 2 AM, so I can't say I have the background to really say how this method should be used. At this point, a documentation note is certainly something doable (because I would hope very much that anyone using this method has read the documentation carefully). On Mar 5, 2013, at 10:00 AM, Kent jkentbo...@gmail.com wrote: By design, when a query() fetches an existing object, it doesn't refresh the values unless populate_existing() is included with the query. The documentation for populate_existing() states it isn't meant for general purpose. Occasionally, however, objects need to be selected FOR UPDATE, with_lockmode('update'), to guarantee against timing problems with concurrent database users, particularly when the record fetched is used as a base for the update. For example, if I need to update a record's quantity field by a delta of +5, it is extremely important that I have the most recent Object.quantity datum; truly, that was the reason I used with_lockmode('update') in the first place. I get uneasy to think that the user needs to remember to invoke populate_existing() when selecting a record FOR UPDATE to guard against the possibility that there is a stale version of the instance in the session. I will likely add that to our framework's Query subclass, but thought you might consider the implications here. Generally, sqla is extremely optimistic regarding locks, but in the event when the user is specifying with_lockmode('update'), we've left the realm of optimistic locking and entered pessimistic, so it seems reasonable to consider automatically enforcing populate_existing() when with_lockmode('update') is used? Something to consider; I'd be interested in your thoughts. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
I had forgotten the danger of populate_existing() also because we make it automatically issue a flush() for these purposes. Documentation note sounds good. On 3/5/2013 10:45 AM, Michael Bayer wrote: populate_existing() blows away any pending changes on the object so turning it on by default would be a surprise in a lot of cases. typically if someone is working with FOR UPDATE they're already programming a very specific section in a very careful manner, it's not something that's done casually. I would think that it would be used to select a row right at the start of a transaction, that is definitely not already in the Session. in my own experience, any time I've actually tried to work with pessimistic locking my application ends up deadlocking at 2 AM, so I can't say I have the background to really say how this method should be used. At this point, a documentation note is certainly something doable (because I would hope very much that anyone using this method has read the documentation carefully). On Mar 5, 2013, at 10:00 AM, Kent jkentbo...@gmail.com mailto:jkentbo...@gmail.com wrote: By design, when a query() fetches an existing object, it doesn't refresh the values unless populate_existing() is included with the query. The documentation for populate_existing() states it isn't meant for general purpose. Occasionally, however, objects need to be selected FOR UPDATE, with_lockmode('update'), to guarantee against timing problems with concurrent database users, particularly when the record fetched is used as a base for the update. For example, if I need to update a record's quantity field by a delta of +5, it is extremely important that I have the most recent Object.quantity datum; truly, that was the reason I used with_lockmode('update') in the first place. I get uneasy to think that the user needs to remember to invoke populate_existing() when selecting a record FOR UPDATE to guard against the possibility that there is a stale version of the instance in the session. I will likely add that to our framework's Query subclass, but thought you might consider the implications here. Generally, sqla is extremely optimistic regarding locks, but in the event when the user is specifying with_lockmode('update'), we've left the realm of optimistic locking and entered pessimistic, so it seems reasonable to consider automatically enforcing populate_existing() when with_lockmode('update') is used? Something to consider; I'd be interested in your thoughts. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/I2ftUVJcAuo/unsubscribe?hl=en. To unsubscribe from this group and all its topics, 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.