On Apr 25, 2014, at 5:40 PM, Tim Kersten <[email protected]> wrote:
>
>
> On Friday, April 25, 2014 5:05:21 PM UTC+1, Michael Bayer wrote:
>
> On Apr 25, 2014, at 4:22 AM, Tim Kersten <[email protected]> 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.
>
> I use read committed isolation and you're right, it does lock the row.
> However, once the other transaction commits it will not overwrite what the
> other one did as that's what the where clause protects against. I began 2
> transactions in 2 separate shells (mysql/InnoDB) to demonstrate:
Oh OK the query that was in the most recent email didn't make this clear, I saw
the "bar" part of it and such which threw me off from what you're doing.
This is an unusual use case because it seems like you'd like to outright ignore
the row if it doesn't match? or are you throwing an exception if you don't get
the expected count?
The version_id feature will basically throw an exception if the row count is
not what's expected. Other than using repeatable read which is the best
approach, it's the best way to prevent writing a stale record with the ORM.
> Yes. Above I combined an extra attribute 'name' with the primary key in the
> WHERE clause of the update statement to ensure that 'name's value isn't
> overwritten if it's changed since by another transaction since I've first
> read it. If I use the .all() method to get one or many instances that I can
> update, even if I lock for update, the where clause of the update contains
> _only_ the primary key, meaning that it will end up overwriting the other
> transactions value.
>
> If in one shell I do something like this:
>
> instances = Session.query(MyModel).with_lockmode('update').filter_by(id=1,
> name='foo').all()
> for instance in instances:
> instance.name = 'bar'
> Session.add(instance)
>
> And in another shell I do the same but update the instance.name to
> 'overwrite' instead of 'bar', and now commit the first shell, the second one
> will indeed set the 'name' to 'overwrite' instead of not updating any rows.
OK again this might behave differently with a different isolation level, not
sure, haven't used MySQL's transactional features too much.
>
>
>
> This is certainly a nice solution and I do use it for several tables, but
> it's also a little more course than my original update statement above. In my
> update statement I check the 'id' and 'name' columns, any other column that's
> changed I don't have to care about, since I'm not writing now values to them
> so other transactions can update those without effecting mine, where as the
> version id feature would force me to reread the row.
Not sure how version id forces you to reread the row. The UPDATE statement is
emitted as UPDATE table ... WHERE pk=<pk> AND version_id=<version>. It
doesn't use any more reads than a regular ORM operation that is updating an
in-memory row. The approach here is pretty standard and is copied from that
of Hibernate.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
