Início / dba / Perguntas / 26905
Accepted
ocharles
Asked: 2012-10-14 15:36:51 +0800 CST

Como implemento insert-if-not-found para transações no nível de isolamento serializável?

  • 772

Estou tendo dificuldade em descobrir como implementar exatamente uma função 'inserir se não for encontrado'. Considere o seguinte.

Temos uma tabela chamada artistcom 2 colunas, (name, id)onde nameé o único e idé uma chave primária serial. É um exemplo artificial, mas ilustra o meu problema:

    SESSION A                     SESSION B
1.                                SELECT id FROM artist
                                    WHERE name = 'Bob';
2.  INSERT INTO artist (name)
      VALUES ('Bob')
3.                                INSERT INTO artist (name)
                                    VALUES ('Bob')
4.   code that users 'Bob'
     (e.g., a FK to Bob's ID)
5.                                ??? Bob already exists, but we
                                  can't find it
4.  COMMIT

A sessão B começa tentando encontrar um artistBob chamado, que falha. No entanto, a Sessão A cria Bob. A sessão B tenta inserir um artista chamado Bob, mas falha porque viola a chave primária. Mas aqui está a parte que não entendo - se eu alterar a operação 3 para ser um select na artisttabela ainda está vazia! Isso ocorre porque estou usando o nível de isolamento serializável, mas como posso lidar com esse caso?

Parece que a única opção que tenho é abortar toda a transação e tentar novamente. Se for esse o caso, devo lançar minha própria exceção 'não foi possível serializar', indicando que o aplicativo deve tentar novamente? Eu já queria esse 'find-or-insert' em uma função plpgsql, onde eu faria INSERT, e se isso falhasse SELECT, mas parece impossível encontrar a linha conflitante ...

postgresql transaction

2 respostas

  1. Best Answer

    Este é um pouco de um FAQ. Você encontraria mais informações se procurasse por ON DUPLICATE KEY UPDATE(a sintaxe do MySQL), MERGE(a sintaxe do padrão SQL) ou UPSERT. É surpreendentemente difícil.

    O melhor artigo que já vi sobre isso é o "por que upsert é tão complicado" de Depesz . Há também a questão SO Insert, na atualização duplicada (postgresql) que tem sugestões, mas carece de explicação e discussão dos problemas.

    A resposta curta é que sim:

    Parece que a única opção que tenho é abortar toda a transação e tentar novamente.

    Ao usar SERIALIZABLEtransações, você só precisa reemiti-las quando elas falharem. O que eles farão. Por design - e com muito mais frequência na página 9.1 e superior devido à detecção de conflitos bastante aprimorada. As operações do tipo upsert são muito conflitantes, então você pode acabar tentando novamente. Se você puder fazer seus upserts em READ COMMITTEDtransações, isso ajudará, mas você ainda deve estar preparado para tentar novamente porque há algumas condições de corrida inevitáveis.

    Deixe a transação falhar com uma violação exclusiva ao inserir a linha conflitante. Se você obtiver uma falha SQLSTATE 23505 unique_violationda transação e souber que estava tentando um upsert, tente novamente. Se você obtiver um SQLSTATE 40001 serialization_failure, também deverá tentar novamente.

    Você basicamente não pode fazer essa nova tentativa dentro de uma função PL/PgSQL (sem hacks sujos como dblink), deve ser do lado do aplicativo. Se o PostgreSQL tivesse procedimentos armazenados com transações autônomas, seria possível, mas não. No READ COMMITTEDmodo, você pode verificar as inserções conflitantes feitas desde o início da transação, mas não após a instrução que chama a função PL/PgSQL iniciada , portanto, mesmo em READ COMMITTEDsua abordagem "detectar conflito com select" simplesmente não funcionará.

    Leia o artigo de depesz para uma explicação muito melhor e mais detalhada.

    • 6

  2. Na maioria dos aplicativos com os quais trabalhei, isso é possível, mas raramente ocorre com um bom gerenciamento de transações. Aconselho vivamente que as transações não sejam abertas durante as comunicações com o utilizador. Na maioria dos casos, isso resulta em tempos de transação abaixo do segundo. O bloqueio otimista é seu amigo aqui.

    Suas transações agora se tornam:

    • Os usuários A e B procuram por Bob e não o encontram.
    • Ambos A e B tentam adicionar Bob.
    • O add de B chega primeiro e é confirmado.
    • O add de A chega depois e é tratado apropriadamente (decisão de projeto).
    • Ambos A e B podem encontrar Bob.

    Há uma chance de uma condição de corrida se A e B enviarem seus acréscimos ao mesmo tempo, mas na prática isso é altamente improvável. Dependendo do fluxo de trabalho, é mais provável que as atualizações encontrem esse problema. Nesse caso, o último usuário a enviar geralmente obtém uma atualização de dados por outro erro de tipo de usuário. Se eles recuperarem os dados atualizados, eles poderão tentar novamente a atualização, se apropriado. Nos casos em que a segunda atualização não entra em conflito, ela pode ser ignorada silenciosamente ou suas alterações aplicadas conforme apropriado.

    Transações de execução longa podem causar inconsistências de dados.

    • 0

relate perguntas