MySQL更新更改多个列是非原子的？ [英] MySQL update changing multiple columns is non-atomic?

问题描述

给定一个具有列id，level和2x2矩阵的表，存储为a11，a12 ，a21，a22，我有这一行：

  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子句简洁）：

 更新`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级
 324 1 1 4 3 1 
  

这是我在SQLite上得到的， Django生成相同的查询，花了我很多时间来确定MySQL正在做不同的事情。从查询中，似乎更新interdepent多行时，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（没有原始查询）来规范这种行为？

编辑

，但我仍在寻找解决方案。拉取所有值并推回它们对于该特定应用程序来说不是可以接受的解决方案。

解决方案

如 MySQL手册：

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

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

因此，在您的情况下，在评估表达式`a11 <$ c时，用于 a21 的值$ c> =（2 * storage 。 a11 ）+（-1 * 。 a21`）``是新的，更新的值4，而不是原始值5.正如手册所说，这种行为与标准SQL不同

您可以使用多表 UPDATE 语法的自联接我不知道是否可以使用Django ORM来实现这样的功能：

 更新存储为旧
 JOIN存储AS新的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中绝对可以实现）是将更新分解为部分，定义在较早部分更新的那些字段的新（而不是旧）值的更新部分中更新的字段：

 更新存储
 SET a21 =（3 * a11）+（-1 * a21），
 a22 =（3 * a12）+（-1 * a22），
 level = level  -  -1; 
 
更新存储
 SET a11 =（2 * a11）+（-1 *（3 * a11  -  a21）），
 a12 =（2 * a12）+（ - 1 *（3 * a12  -  a22））; 
  

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

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

If columns are updated one at a time, in the same order given by the query:

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

This is really scary behavior, since this is a library meant to be used cross-platform. My questions are:

1. Which one is doing it wrong, MySQL or SQLite? Can this be considered a bug?
2. What can I expect from other major databases (Oracle, PostgreSQL and SQLServer)?
3. What can I do with the Django ORM (no raw queries) to normalize this behavior?

edit

The problem is clear, but I'm still looking for a solution. Pulling all values and pushing them back is not an acceptable solution for this particular application.

解决方案

As stated in the MySQL manual:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Therefore, in your case, the value being used for a21 when evaluating the expression ```a11= (2 *storage.a11) + (-1 *storage.a21`)`` is the new, updated, value of 4 rather than the original value of 5. As the manual says, this behaviour differs from standard SQL.

You could instead use a self-join with the multiple-table UPDATE syntax, however I don't know whether something like this can be implemented using the 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);

See it on sqlfiddle.

My only other thought (which should definitely be implementable within Django) is to split the update into separate parts, defining the fields updated in later parts in relation to the new (rather than the old) values of those fields which have been updated in earlier parts:

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

To prevent concurrency issues, you ought to perform these two updates within a transaction (if supported by the RDBMS).

这篇关于MySQL更新更改多个列是非原子的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！