On Apr 25, 2014, at 4:22 AM, Tim Kersten <t...@io41.com> wrote:
> Session.query(MyModel).filter_by(foo=old_foo_value).update({'foo':
> new_foo_value})
>
> This generates something like this: UPDATE mymodel SET foo=%s WHERE
> mymodel.foo=%s
>
> If I read that correctly it means that the update won't set any rows if the
> value foo has changed in some other transaction since I last read it, so I
> won't end up overwriting anything that's been changed by someone else.
well that depends highly on the transactional capabilities/settings of your
database, but if you have read committed isolation those rows will be locked,
which means your UPDATE statement would then wait until the other transaction
commits. At that point, your UPDATE will proceed and overwrite whatever the
other transaction did. So it wouldn't really work for the purpose of
"preventing overwriting anything that's been changed by someone else". If OTOH
you have repeatable read set up, it should actually raise an exception when a
conflict is detected. Which also might not be what you want, that is, your
operation will fail.
>
> If I use .all() and make my changes on the instances, the generated sql
> issues will update where the primary key matches, rather than 'foo': UPDATE
> mymodel SET foo=%s WHERE mymodel.uuid=%s
the UPDATE statement is the only way a row in the relational database gets
updated. There is no difference in transaction isolation behavior between
using an UPDATE for many rows versus an UPDATE for a single row.
To prevent stale write without using repeatable read you can also use the
version id feature:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#configuring-a-version-counter
. however this feature only takes effect for individual objects, not the
query.update() feature as it works based on rows that have been loaded into
memory.
>
>
> On Thursday, April 24, 2014 2:03:19 AM UTC+1, Michael Bayer wrote:
> perhaps I'm missing something but wouldn't you just change the update() here
> to all(), so that you SELECT only those rows you care about into memory, then
> change each "foo" as needed and flush? I'm not seeing what the issue is.
> The row isn't locked if you aren't using SELECT..FOR UPDATE.
>
>
> On Apr 23, 2014, at 8:27 PM, Tim Kersten <t...@io41.com> wrote:
>
>> I'd like to run a data migration on a live server, but only update rows if
>> the data hasn't changed since I've read it, and would like to do so
>> optimistically, so without locking the row. Doing so like below works and
>> prevents me updating rows that have changed since I last read the row.
>>
>> Session.query(MyModel).filter_by(foo=old_foo_value).filter_by(bar=old_bar_value).update({'foo':
>> new_foo_value})
>>
>> While the approach works, it doesn't use the ORM. (I use an after_flush hook
>> to inspect & log changes from dirty instances in the session).
>>
>> Is there a way to update an ORM instance conditionally like above?
>>
>> Kindest Regards,
>> Tim
>>
>> --
>> 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+...@googlegroups.com.
>> To post to this group, send email to sqlal...@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.
--
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.
