On Friday, April 25, 2014 5:05:21 PM UTC+1, Michael Bayer wrote:
>
>
> On Apr 25, 2014, at 4:22 AM, Tim Kersten <t...@io41.com <javascript:>>
> 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:
1mysql> create table `test` ( `id` int(11) not null auto_increment primary
key, `name` varchar(10)) engine=InnoDB;
1mysql> begin;
1mysql> insert into test set name='foo';
1mysql> commit;
1mysql> begin;
Query OK, 0 rows affected (0.00 sec)
1mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | foo |
+----+------+
1 row in set (0.00 sec)
2mysql> begin;
2mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | foo |
+----+------+
1 row in set (0.00 sec)
1mysql> update test set name='bar' where id=1 and name='foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2mysql> update test set name='overwrite' where id=1 and name='foo';
1mysql> commit;
2mysql> commit;
1mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | bar |
+----+------+
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.
>
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.
> 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.
>
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.
> 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+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.
