MySQL更新更改多个列是非原子的吗？

Question

我在使用MySQL 5.5.22的Django时遇到以下问题。

给定一个具有列ID，级别和存储为a11，a12，a21，a22的2x2矩阵的表，我具有以下行：

id   a11   a12   a21   a22   level
324  3     2     5     3     2

给定一个查询集qs，我进行以下更新：

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)
    )

django为此生成以下查询（从db.connection.queries中获取该查询，为简洁起见，删除where子句）：

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`)

然后，我的行如下所示：

id   a11   a12   a21   a22   level
324  2     1     4     3     1

对于任何一行，a12*a21 - a11*a22 = 1都应该为True，并且据此，该行应该为：

id   a11   a12   a21   a22   level
324  1     1     4     3     1

这就是我在SQLite上得到的，Django生成了相同的查询，并且花了我很多时间才能确定MySQL正在做一些不同的事情。从查询看来，当更新跨行的多行时，MySQL不会将其视为单个原子操作，并且随着列的更新，它们会影响依赖于它们的值。我确认这似乎是Python提示符下的以下代码发生的情况：

>>> a11, a12, a21, a22 = (3, 2, 5, 3)
>>> (2 * a11) + (-1 * a21),\
... (2 * a12) + (-1 * a22),\
... (3 * a11) + (-1 * a21),\
... (3 * a12) + (-1 * a22)
(1, 1, 4, 3)

如果列一次更新一次，则查询的顺序相同：

>>> a11, a12, a21, a22 = (3, 2, 5, 3)
>>> a21 = (3*a11) + (-1*a21)
>>> a22 = (3*a12) + (-1*a22)
>>> a11 = (2*a11) + (-1*a21)
>>> a12 = (2*a12) + (-1*a22)
>>> (a11, a12, a21, a22)
(2, 1, 4, 3)

这确实是一种可怕的行为，因为这是一个旨在跨平台使用的库。我的问题是：

1. 哪一个做错了，MySQL或SQLite？可以将其视为错误吗？
2. 我对其他主要数据库（Oracle，PostgreSQL和SQLServer）有什么期望？
3. 我该如何使用Django ORM（无原始查询）来规范此行为？

编辑

问题很明显，但我仍在寻找解决方案。对于此特定应用程序，将所有值拉回并推回它们不是可接受的解决方案。

Answer 1

如MySQL手册所述：

以下语句中的第二个赋值设置col2为当前（更新）col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为不同于标准SQL。

更新t1 SET col1 = col1 + 1，col2 = col1;

因此，在您的情况下，用于a21评估表达式的值`a11 = (2 * storage.a11) + (-1 * storage.a21)`是新的，更新的值4，而不是原始值5。正如手册所述， 此行为不同于标准SQL 。

您可以改为使用带有多表UPDATE语法的自联接，但是我不知道是否可以使用Django ORM来实现类似的功能：

UPDATE storage AS old
  JOIN storage AS new USING (id)
SET
  new.a21   = (3 * old.a11) + (-1 * old.a21),
  new.a22   = (3 * old.a12) + (-1 * old.a22),
  new.level = old.level - -1,
  new.a11   = (2 * old.a11) + (-1 * old.a21),
  new.a12   = (2 * old.a12) + (-1 * old.a22);

在sqlfiddle上看到它。

我唯一的另一种想法（肯定应该在Django中可以实现）是将更新拆分为单独的部分，相对于早先已更新的那些字段的新值（而不是旧值），定义了在后来的部分中更新的字段部分：

UPDATE storage
SET    a21   = (3 * a11) + (-1 * a21),
       a22   = (3 * a12) + (-1 * a22),
       level = level - -1;

UPDATE storage
SET    a11   = (2 * a11) + (-1 * (3*a11 - a21)),
       a12   = (2 * a12) + (-1 * (3*a12 - a22));

为了防止并发问题，您应该在事务中执行这两个更新（如果RDBMS支持）。