I'm having the following problem using Django with MySQL 5.5.22.
Given a table with columns id, level and a 2x2 matrix stored as a11, a12, a21, a22, I have this row:
id a11 a12 a21 a22 level
324 3 2 5 3 2
Given a queryset qs, I do the following update:
qs.update(
a11=(b12 * a21 - b11 * a22) * F('a11') + (b11 * a12 - b12 * a11) * F('a21'),
a12=(b12 * a21 - b11 * a22) * F('a12') + (b11 * a12 - b12 * a11) * F('a22'),
a21=(b22 * a21 - b21 * a22) * F('a11') + (b21 * a12 - b22 * a11) * F('a21'),
a22=(b22 * a21 - b21 * a22) * F('a12') + (b21 * a12 - b22 * a11) * F('a22'),
level=(F('level') - 1)
)
For which django generates the following query (got it from db.connection.queries, remove the where clause for brevity):
UPDATE `storage`
SET
`a21` = (3 * `storage`.`a11`) + (-1 * `storage`.`a21`),
`a22` = (3 * `storage`.`a12`) + (-1 * `storage`.`a22`),
`level` = `storage`.`level` - -1,
`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`),
`a12` = (2 * `storage`.`a12`) + (-1 * `storage`.`a22`)
And my row looks like this after that:
id a11 a12 a21 a22 level
324 2 1 4 3 1
For any row, a12*a21 - a11*a22 = 1 is supposed to be True, and according to that, the row was supposed to be:
id a11 a12 a21 a22 level
324 1 1 4 3 1
This is what I get on SQLite, with Django generating the same query, and it took me a lot of time to figure that MySQL was doing something different. From the query, it seems like when updating interdepent multiple rows, MySQL doesn't treat it as a single atomic operation, and as columns are updated, they affect the values dependent on them. I confirmed this seems to be what happens by the following code on the Python prompt:
>>> a11, a12, a2