On Apr 25, 2014, at 6:54 PM, Tim Kersten <t...@io41.com> wrote:
>
>
> 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?
I can think of some potentially very exotic ways of injecting this behavior
with cursor_execute() events, but it would be quite awkward. The ORM still
might complain when it sees zero rows updated.
But the real problem is if you want the ORM to update a row, then have it
ignored, now you've totally got the wrong data in memory. I'm not sure how
your application is constructed that A. you want to use the ORM but B. you
don't care if it has the wrong data loaded after it thinks it's synchronized.
If you just need some simplistic CRUD interface (e.g. object.save()) you can
build those on top of Core pretty easily. The ORM has a specific usage model
and this goes counter to that, hence there's a Core.
Keep in mind you can use a timestamp as a versioning column. Seems like your
use case is straightforward, only update rows that haven't been changed before
timestamp X. if your rows have a trigger or similar that does an "updated_at"
column then you'd be able to target those rows.
>
>
> 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 tosqlalchemy+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.
