Início / dba / Perguntas / 212580
Accepted
Elliot Blackburn
Asked: 2018-07-19 08:03:16 +0800 CST

Transações simultâneas resultam em condição de corrida com restrição exclusiva na inserção

  • 772

Eu tenho um serviço da web (http api) que permite que um usuário crie um recurso tranquilamente. Após a autenticação e validação eu passo os dados para uma função do Postgres e permito que ela verifique a autorização e crie os registros no banco de dados.

Encontrei um bug hoje quando duas solicitações http foram feitas no mesmo segundo, o que fez com que essa função fosse chamada com dados idênticos duas vezes. Existe uma cláusula dentro da função que faz um select em uma tabela para ver se existe um valor, se existe eu pego o ID e uso isso na minha próxima operação, se não existir eu insiro os dados, obtenho de volta o ID e, em seguida, use-o na próxima operação. Abaixo está um exemplo simples.

select id into articleId from articles where title = 'my new blog';
if articleId is null then
    insert into articles (title, content) values (_title, _content)
    returning id into articleId;
end if;
-- Continue, using articleId to represent the article for next operations...

Como você provavelmente pode adivinhar, obtive uma leitura fantasma nos dados em que ambas as transações entraram no if articleId is null thenbloco e tentaram inserir na tabela. Um teve sucesso e o outro explodiu por causa de uma restrição única em um campo.

Eu dei uma olhada em como me defender contra isso e encontrei algumas opções diferentes, mas nenhuma parece atender às nossas necessidades por alguns motivos e estou lutando para encontrar alternativas.

  1. insert ... on conflict do nothing/update...Eu olhei primeiro para a on conflictopção que parecia boa, no entanto, a única opção é para do nothingqual não retorna o ID do registro que causou a colisão e do updatenão funcionará, pois fará com que os gatilhos sejam disparados quando, na realidade, os dados não mudou. Em alguns casos, isso não é um problema, mas em muitos casos isso pode invalidar as sessões do usuário, o que não é algo que podemos fazer.
  2. set transaction isolation level serializable;esta parece ser a resposta mais atraente, no entanto, mesmo nosso conjunto de testes pode causar dependências de leitura/gravação onde, como acima, queremos inserir se algo não existir e devolvê-lo se existir e continuar com outras operações. Se tivermos várias transações pendentes que executam o código acima, isso causará um erro de dependência de leitura/gravação, conforme descrito na transação-iso dos documentos do Postgres .

Como esse tipo de transação de leitura/gravação simultânea deve ser tratada?

Nem eu nem minha equipe afirmamos ser especialistas em banco de dados, muito menos especialistas em Postgres, mas sentimos que isso deve ser um problema resolvido, ou uma pessoa que se deparou no passado. Estamos abertos a quaisquer sugestões. Se as informações fornecidas acima não forem suficientes, por favor, comente e adicionarei mais informações conforme necessário.

postgresql transaction

3 respostas

  1. A raiz do problema é que, com o READ COMMITTEDnível de isolamento padrão, cada UPSERT simultâneo (ou qualquer consulta) pode ver apenas as linhas que estavam visíveis no início da consulta. O manual:

    Quando uma transação usa esse nível de isolamento, uma SELECTconsulta (sem uma cláusula FOR UPDATE/ SHARE) vê apenas os dados confirmados antes do início da consulta; ele nunca vê dados não confirmados ou alterações confirmadas durante a execução da consulta por transações simultâneas.

    Mas um UNIQUEíndice é absoluto e ainda deve considerar as linhas inseridas simultaneamente - mesmo as linhas invisíveis. Assim, você pode obter uma exceção para uma violação exclusiva, mas ainda não pode ver a linha conflitante na mesma consulta . O manual:

    INSERTcom uma ON CONFLICT DO NOTHINGcláusula pode ter a inserção não prosseguir para uma linha devido ao resultado de outra transação cujos efeitos não são visíveis para o INSERTinstantâneo. Novamente, este é apenas o caso no modo Read Committed.

    A "solução" de força bruta para esse problema é substituir linhas conflitantes por ON CONFLICT ... DO UPDATE. A nova versão de linha fica visível na mesma consulta. Mas existem vários efeitos colaterais e eu aconselho contra isso. Um deles é que os UPDATEgatilhos são disparados - o que você deseja evitar expressamente. Resposta intimamente relacionada no SO:

    A opção restante é iniciar um novo comando (na mesma transação), que pode ver essas linhas conflitantes da consulta anterior. Ambas as respostas existentes sugerem isso. O manual novamente:

    No entanto, SELECTvê os efeitos de atualizações anteriores executadas em sua própria transação, mesmo que ainda não tenham sido confirmadas. Observe também que dois SELECTcomandos sucessivos podem ver dados diferentes, mesmo que estejam em uma única transação, se outras transações confirmarem alterações após o início do primeiro SELECTe antes do início do segundo SELECT.

    Mas você quer mais :

    -- Continuar, usando articleId para representar o artigo para as próximas operações...

    Se as operações de gravação simultâneas puderem alterar ou excluir a linha, para ter certeza absoluta, você também precisará bloquear a linha selecionada . (A linha inserida está bloqueada de qualquer maneira.)

    E como você parece ter transações muito competitivas, para garantir o sucesso, faça um loop até o sucesso. Embrulhado em uma função plpgsql:

    CREATE OR REPLACE FUNCTION f_articleid(_title text, _content text, OUT _articleid int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      SELECT articleid
      FROM   articles
      WHERE  title = _title
      FOR    UPDATE          -- or maybe a weaker lock 
      INTO   _articleid;

      EXIT WHEN FOUND;

      INSERT INTO articles AS a (title, content)
      VALUES (_title, _content)
      ON     CONFLICT (title) DO NOTHING  -- (new?) _content is discarded
      RETURNING a.articleid
      INTO   _articleid;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;

    Explicação detalhada:

    • 11
  2. Best Answer

    Tente o insertprimeiro, com on conflict ... do nothinge returning id. Se o valor já existir, você não obterá nenhum resultado dessa instrução, portanto, será necessário executar a selectpara obter o ID.

    Se duas transações tentarem fazer isso ao mesmo tempo, uma delas será bloqueada insert(porque o banco de dados ainda não sabe se a outra transação será confirmada ou revertida) e continuará somente após a conclusão da outra transação.

    • 10

  3. Eu acho que a melhor solução é apenas fazer a inserção, pegar o erro e tratá-lo corretamente. Se você estiver preparado para lidar com erros, o nível de isolamento serializável é (aparentemente) desnecessário para o seu caso. Se você não estiver preparado para lidar com erros, o nível de isolamento serializável não ajudará - apenas criará ainda mais erros que você não está preparado para lidar.

    Outra opção seria fazer o ON CONFLICT DO NOTHING e então, se nada acontecer, continue fazendo a consulta que você já está fazendo para obter o valor must-be-there-now. Em outras palavras, passe select id into articleId from articles where title = 'my new blog';de uma etapa preventiva para uma etapa executada apenas se ON CONFLICT DO NOTHING de fato não fizer nada. Se for possível que um registro seja inserido e excluído novamente, você deve fazer isso em um loop de repetição.

    • 3

relate perguntas