On Friday, April 25, 2014 11:19:40 PM UTC+1, Michael Bayer wrote:
>
>
> On Apr 25, 2014, at 5:40 PM, Tim Kersten <[email protected] <javascript:>>
> 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.
>
Sorry :)
>
> 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?
>
Yes, I'm ignoring the row if it doesn't match. One use case for this: A
misbehaving piece of code set a "bad" value in a column in many rows. The
code was fixed, and thus when customers used that code in future the values
would be updated to "good" values. A data migration to automatically adjust
the "bad" values would have the potential to overwrite one that's just been
altered by a customer, and this is what I'm trying to avoid, with as
minimal an impact on the running system. I'd only like to avoid doing so
while still using the ORM and was wondering if there's a way to do this
without having a version column?
>
> 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.
>
Indeed. If I'd have it on the table in question I wouldn't have the problem
I'm facing now :)
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.
>
No doubt. I'm stuck on my current isolation level though, with no chance of
changing it.
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.
>
Sorry, I didn't communicate that very well. If another transaction updates
the row, but not the column I'm interested in, then the version_id gets
updated and the update from my own transaction fails. I then need to reread
the row to update my stale view of it's contents and try to commit again.
This is what I mean by needing to do an additional read (compared to my
"update where" approach) if the row had other columns changed in another
transaction. It's not much of an issue though - I'd need a system that has
an insane amount of updates to a single row for this to become a major
issue, at which point I'll likely have very different concerns :)
--
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.
