Início / dba / Perguntas / 308688
Accepted
cis
Asked: 2022-03-14 22:18:40 +0800 CST

Preciso de um bloqueio FOR UPDATE explícito em um CTE em UPDATE?

  • 772

No Postgres 13, tenho uma tabela que é atualizada com frequência. No entanto, a consulta de atualização é bastante complicada e usa os mesmos valores várias vezes. Então, usar um CTE parece uma coisa bastante lógica a se fazer.

Um exemplo simplificado se parece com isso:

WITH my_cte AS (
    SELECT
          my_id,
          CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END AS my_addition     
    FROM my_table      
    WHERE my_id = $1
)
UPDATE my_table
        SET my_value1 = my_table.my_value1 + my_cte.my_addition,
            my_value2 = my_table.my_value2 + my_cte.my_addition
FROM my_cte
WHERE my_table.my_id = my_cte.my_id

Agora estou me perguntando: O que aconteceria se entre o SELECTno CTE e o UPDATE, a tabela fosse atualizada por outra consulta, alterando my_value1assim, o cálculo de my_additionficasse desatualizado e errado quando UPDATEisso acontecesse. Tal situação pode ocorrer? Ou o Postgres define um bloqueio implícito automaticamente?

Se o Postgres não faz mágica aqui e eu mesmo preciso cuidar disso: seria suficiente fazer FOR UPDATEno SELECTCTE?

Desculpe se não fui claro aqui: Não é que eu queira "ver" essas modificações simultâneas, eu quero evitá-las, ou seja, uma vez que o cálculo SELECTé feito, nenhuma outra consulta pode modificar essa mesma linha até que UPDATEseja feito.

Na vida real, o que eu zombei aqui CASE WHEN my_value1 > 100 THEN 50 ELSE 10 ENDtem cerca de 20 linhas e eu preciso disso em cerca de 5 lugares no arquivo UPDATE. Como sou um grande fã de "Não se repita", acho que um CTE é o caminho a percorrer. Ou existe uma maneira melhor de evitar copiar e colar em um UPDATEsem CTE?

postgresql update

3 respostas

  1. Best Answer

    O Postgres usa um modelo multiversão ( Multiversion Concurrency Control, MVCC ).

    READ COMMITTEDNo nível de isolamento padrão , cada consulta separada efetivamente vê um instantâneo do banco de dados a partir do instante em que a consulta começa a ser executada. Consultas subsequentes - mesmo dentro da mesma transação - podem ver um instantâneo diferente se transações simultâneas forem confirmadas no meio. (Além do que foi feito na mesma transação até agora.)

    No entanto, no que diz respeito às CTEs , todas as sub-instruções em WITHsão executadas simultaneamente com a instrução externa, elas efetivamente veem o mesmo instantâneo do banco de dados. Tudo isso é considerado uma única consulta para esse fim.

    Portanto, não , você não precisa de um bloqueio explícito para permanecer consistente.

    Encapsular a lógica em uma função pode ser conveniente por vários motivos, mas isso não tem nenhum efeito sobre a concorrência. Além: um CTE com uma função volátil nunca é embutido. Ver:

    A SELECTnão bloqueia as linhas consultadas. O Postgres permite arquivos UPDATES. Mas UPDATEbloqueia as linhas de destino. As transações simultâneas que tentam gravar também precisam esperar até que a transação de bloqueio seja concluída.

    Se você quiser proibir gravações em linhas (colunas) que foram selecionadas apenas enquanto o seu UPDATEestá em andamento, você pode querer fazer bloqueios de qualquer maneira (ou usar um nível de isolamento mais estrito). Talvez FOR UPDATEfechaduras, ou talvez uma fechadura mais fraca . Isso depende dos detalhes e requisitos que você está expressamente retendo / não fornecendo em sua pergunta.

    Além disso (embora você não tenha solicitado isso), se várias transações simultâneas puderem estar gravando em linhas sobrepostas (mais de uma por vez), certifique-se de seguir a mesma ordem consistente de linhas para evitar deadlocks .

    • 6

  2. Se você deseja impedir que instruções simultâneas modifiquem as linhas que o CTE seleciona antes de serem atualizadas, você precisa usar SELECT ... FOR NO KEY UPDATEno CTE.

    • 1

  3. Com base no que a_horse_with_no_name disse:

    Eu colocaria tal condição em uma função (SQL). Outra alternativa ao bloqueio (se você espera que isso ocorra raramente) seria usar o serializablenível de isolamento e executar novamente o UPDATE se ocorrer um erro.

    Coloque a lógica de adição em uma função e chame essa função toda vez que for definir um novo valor. Isso irá ajudá-lo de duas maneiras.

    1. Isso permite que você evite duplicar a lógica de adição toda vez que a usar.
    2. Isso cria uma instrução de atualização muito simples que pode entrar rapidamente, bloquear apenas algumas linhas e sair.

    Algo assim deve funcionar.

    CREATE FUNCTION fn_my_addition(my_value int)
RETURNS INT
LANGUAGE SQL
AS
$$
  select CASE my_value1 > 100 THEN 50 ELSE 10 END;
$$;

UPDATE my_table
SET my_value1 = my_value1 + fn_my_addition(my_value1),
    my_value2 = my_value2 + fn_my_addition(my_value2)
WHERE my_id = $1;
    • 0

relate perguntas