Por que o CTE está aberto a atualizações perdidas?

Acho que provavelmente pretendia adicionar esse comentário à resposta anterior, sobre duas declarações separadas. Foi há mais de um ano, então não tenho mais certeza.

A consulta baseada em wCTE realmente não resolve o problema que deveria, mas ao analisá-la novamente mais de um ano depois, não vejo a possibilidade de atualizações perdidas na versão wCTE.

(Observe que todas essas soluções só funcionarão bem se você tentar alterar exatamente uma linha com cada transação. Assim que você tentar fazer várias alterações em uma transação, as coisas ficam confusas devido à necessidade de repetir loops em reversões. No mínimo você precisaria usar um ponto de salvamento entre cada mudança.)

Versão de duas instruções sujeita a atualizações perdidas.

A versão que usa duas instruções separadas está sujeita a atualizações perdidas, a menos que o aplicativo verifique a contagem de linhas afetadas da UPDATEinstrução e da INSERTinstrução e tente novamente se ambas forem zero.

Imagine o que acontece se você tiver duas transações READ COMMITTEDisoladas.

• TX1 executa o UPDATE(sem efeito)
• TX1 executa o INSERT(insere uma linha)
• TX2 executa o UPDATE(sem efeito, a linha inserida por TX1 ainda não está visível)
• TX1s COMMIT.
• TX2 executa o INSERT, *que obtém um novo instantâneo que pode ver a linha confirmada por TX1. A EXISTScláusula retorna true, porque TX2 agora pode ver a linha inserida por TX1.

Então TX2 não tem efeito. A menos que o aplicativo verifique a contagem de linhas da atualização e da inserção e tente novamente se ambos relatarem zero linhas, ele não saberá que a transação não teve efeito e continuará alegremente.

A única maneira de verificar as contagens de linhas afetadas é executá-las como duas instruções separadas em vez de várias instruções ou usar um procedimento.

Você pode usar SERIALIZABLEo isolamento, mas ainda precisará de um loop de repetição para lidar com falhas de serialização.

A versão wCTE protege contra o problema de atualizações perdidas porque INSERTestá condicionada ao fato de UPDATEafetar alguma linha, em vez de uma consulta separada.

O wCTE não elimina violações únicas

A versão CTE gravável ainda não é um upsert confiável.

Considere duas transações que executam isso simultaneamente.

• Ambos executam a cláusula VALUES.

• Agora ambos executam a UPDATEparte. Como não há linhas correspondentes à UPDATEcláusula s where, ambos retornam um conjunto de resultados vazio da atualização e não fazem alterações.

• Agora ambos executam a INSERTporção. Como as UPDATElinhas zero retornadas para ambas as consultas, ambas tentam INSERTa linha.

Um consegue. Um lança uma violação única e aborta.

Isso não é motivo de preocupação com a perda de dados, desde que o aplicativo verifique os resultados de erro de suas consultas (ou seja, qualquer aplicativo escrito decentemente) e tente novamente, mas torna a solução não melhor do que as versões existentes de duas instruções. Isso não elimina a necessidade de um loop de repetição.

A vantagem que o wCTE oferece sobre a versão existente de duas instruções é que ele usa a saída do UPDATEpara decidir se deve INSERT, em vez de usar uma consulta separada na tabela. Isso é parcialmente uma otimização, mas protege parcialmente contra um problema com a versão de duas instruções que causa atualizações perdidas; Veja abaixo.

Você pode executar o wCTE SERIALIZABLEisoladamente, mas obterá apenas falhas de serialização em vez de violações exclusivas. Isso não mudará a necessidade de um loop de repetição.

O wCTE não parece ser vulnerável a atualizações perdidas

Meu comentário sugeriu que essa solução poderia resultar em atualizações perdidas, mas, ao analisar isso, acho que posso ter me enganado.

Já faz mais de um ano e não consigo me lembrar das circunstâncias exatas, mas acho que provavelmente perdi o fato de que os índices únicos têm uma exceção parcial das regras de visibilidade da transação para permitir que uma transação de inserção espere que outra insira ou role de volta antes de prosseguir.

Ou talvez eu tenha perdido o fato de que o INSERTno wCTE é condicional se o UPDATEafetado alguma linha, não se a linha candidata existe na tabela.

S conflitantes INSERTem um índice exclusivo aguardam confirmação/reversão

Digamos que uma cópia da consulta seja executada, inserindo uma linha. A alteração ainda não foi confirmada. A nova tupla existe no heap e no índice exclusivo, mas ainda não está visível para outras transações, independentemente dos níveis de isolamento.

Agora outra cópia da consulta é executada. A linha inserida ainda não está visível, pois a primeira cópia não foi confirmada, portanto, a atualização não corresponde a nada. A consulta continuará tentando uma inserção, que verá que outra transação em andamento está inserindo a mesma chave e bloqueará a espera pela confirmação ou reversão dessa transação .

Se a primeira transação for confirmada, a segunda falhará com uma violação exclusiva, conforme descrito acima. Se a primeira transação reverter, a segunda prosseguirá com sua inserção.

A INSERTdependência do UPDATErowcount protege contra atualizações perdidas

Ao contrário do caso de duas instruções, não acho que o wCTE seja vulnerável a atualizações perdidas.

Se o UPDATEnão tiver efeito, o INSERTsempre será executado, porque é estritamente condicional se o UPDATEfez alguma coisa, não no estado da tabela externa. Portanto, ainda pode falhar com uma violação exclusiva, mas não pode falhar silenciosamente em ter qualquer efeito e perder totalmente a atualização.