Вопрос по sql, mysql, sqlite – Обновление MySQL, изменяющее несколько столбцов, не является атомарным?

8

У меня следующая проблема с использованием Django с MySQL 5.5.22.

Учитывая таблицу со столбцами 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):

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)

Это действительно страшное поведение, так как это библиотека, предназначенная для кроссплатформенного использования. Мои вопросы:

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

edit

Проблема ясна, но я все еще ищу решение. Извлечение всех значений и их возврат не является приемлемым решением для данного конкретного приложения.

Это интересный вопрос. Я играл с ним наsqlfiddle и похоже, что MySQL - единственный, кто ведет себя таким образом. Chad
Смотрите мой обновленный ответ ниже. eggyal
Связанный / дубликат:stackoverflow.com/questions/2203202/… pilcrow

Ваш Ответ

2   ответа
12

Oracle и SQL Server рассматривают это как элементарную операцию.Посмотрите следующую SQL Fiddle и переключите сервер, чтобы увидеть поведение следующего SQL:

CREATE TABLE Swap (
  a CHAR(1),
  b CHAR(1)
);

INSERT INTO Swap (a, b) VALUES ('a', 'b');

UPDATE Swap SET a = b, b = a;

SELECT * FROM Swap;

MySQL была единственной RBDMS, которая реализует это с обоими столбцами, содержащими одно и то же значение после обновления.

Что касается того, как вы могли бы решить эту проблему, я бы вместо этого вытащил значения из базы данных, сделал вычисления внутри вашего приложения (вместо вашего оператора update), а затем обновил бы базу данных с вычисленными значениями. Таким образом, вы можете гарантировать, что расчет будет выполняться согласованным образом.

Error: User Rate Limit Exceeded Pedro Werneck
10

Руководство по MySQL:

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;

Следовательно, в вашем случае значение используется дляa21 при оценке выражения`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`) это новое, обновленное значение 4, а не исходное значение 5. Как сказано в руководстве,this behaviour differs from standard 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).

Error: User Rate Limit Exceeded Pedro Werneck
Error: User Rate Limit ExceededinsertedError: User Rate Limit ExceededdeletedError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded

Похожие вопросы