Pergunta sobre mysql, sql, django, sqlite – A atualização do MySQL que altera várias colunas não é atômica?

8

Eu estou tendo o seguinte problema usando o Django com o MySQL 5.5.22.

Dada uma tabela com colunas id, level e uma matriz 2x2 armazenada como a11, a12, a21, a22, tenho esta linha:

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

Dado um qs queryset, eu faço a seguinte atualização:

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

Para o qual django gera a seguinte consulta (obtida de db.connection.queries, remova a cláusula where para brevidade):

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

E minha linha fica assim depois disso:

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

Para qualquer fila,a12*a21 - a11*a22 = 1 é suposto ser verdadeiro, e de acordo com isso, a linha deveria ser:

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

Isso é o que eu obtenho no SQLite, com o Django gerando a mesma consulta, e levei muito tempo para descobrir que o MySQL estava fazendo algo diferente. A partir da consulta, parece que ao atualizar várias linhas interdependentes, o MySQL não a trata como uma única operação atômica e, como as colunas são atualizadas, elas afetam os valores dependentes delas. Confirmei que isso parece ser o que acontece com o seguinte código no prompt do 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)

Se as colunas forem atualizadas uma por vez, na mesma ordem dada pela consulta:

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

Esse é um comportamento realmente assustador, já que essa é uma biblioteca que deve ser usada em várias plataformas. Minhas perguntas são:

Qual deles está fazendo errado, MySQL ou SQLite? Isso pode ser considerado um bug?O que posso esperar de outros grandes bancos de dados (Oracle, PostgreSQL e SQLServer)?O que eu posso fazer com o Django ORM (sem consultas cruas) para normalizar esse comportamento?

editar

O problema é claro, mas ainda estou procurando uma solução. Puxar todos os valores e empurrá-los de volta não é uma solução aceitável para esse aplicativo específico.

Esta é uma questão interessante. Eu joguei com elesqlfiddle e parece que o MySQL é o único que se comporta dessa maneira. Chad
Veja minha resposta atualizada abaixo. eggyal
Relacionado / duplicado:stackoverflow.com/questions/2203202/… pilcrow

Sua resposta

2   a resposta
10

Manual do MySQL:

A segunda atribuição nos conjuntos de instruções a seguircol2 para o atual (atualizado)col1 valor, não o originalcol1 valor. O resultado é quecol1 ecol2 tem o mesmo valor. Esse comportamento difere do SQL padrão.

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

Portanto, no seu caso, o valor usado paraa21 ao avaliar a expressão`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`) é o valor novo e atualizado de 4, em vez do valor original de 5. Como diz o manual,esse comportamento difere do SQL padrão.

Você poderia, em vez disso, usar uma auto-associação com a tabela múltiplaUPDATE sintaxe, no entanto eu não sei se algo como isto pode ser implementado usando o 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);

Ver emsqlfiddle.

Meu único outro pensamento (que definitivamente deve ser implementável no Django) é dividir a atualização em partes separadas, definindo os campos atualizados em partes posteriores em relação aos novos (em vez dos antigos) valores dos campos que foram atualizados anteriormente. partes:

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

Para evitar problemas de simultaneidade, você deve executar essas duas atualizações dentro de uma transação (se suportado pelo RDBMS).

Solução bem vista! O SQL Server, por exemplo, funciona da mesma forma com oinserted edeleted pseudo-tabelas. Gerardo Lima
Obrigado pela referência. Isso deixa claro. Eu estava esperando que talvez houvesse um cenário para mudar esse comportamento. Pedro Werneck
@PedroWerneck: esta resposta atualizada não ajuda em nada? eggyal
12

Oracle e SQL Server tratam isso como uma operação atômica.Veja o seguinte SQL Fiddle, e mude o servidor para ver o comportamento do seguinte 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;

O MySQL era o único RBDMS que implementa isso com ambas as colunas contendo o mesmo valor após a atualização.

Quanto a como você resolveria isso, em vez disso, puxaria os valores do banco de dados, faria os cálculos dentro de seu aplicativo (em vez de sua instrução de atualização) e atualizaria o banco de dados com os valores calculados. Desta forma, você pode garantir que o cálculo será realizado de forma consistente.

Obrigado. O SQL Fiddle é novo para mim e muito útil. Infelizmente, puxar todos os dados do banco de dados e empurrá-lo de volta derrota todo o propósito desta biblioteca. Existem maneiras melhores de fazer o que faz se estou disposto a fazer isso. Se não há outro jeito, tudo bem, mas espero que haja uma solução melhor, mesmo que um pouco hackeada. Pedro Werneck

Perguntas relacionadas