为什么 CTE 对丢失的更新开放？

我想我可能打算在先前的答案中添加该评论，关于两个单独的陈述。那是一年多以前的事了，所以我不再完全确定了。

基于 wCTE 的查询并没有真正解决它应该解决的问题，但是在一年多之后再次审查它时，我看不到 wCTE 版本中丢失更新的可能性。

（请注意，所有这些解决方案只有在您尝试对每个事务只更改一行时才能正常工作。一旦您尝试在一个事务中进行多项更改，由于需要在回滚时进行重试循环，事情就会变得一团糟。至少您需要在每次更改之间使用保存点。）

两语句版本可能会丢失更新。

使用两个单独语句的版本UPDATE可能会丢失更新，除非应用程序从语句和语句中检查受影响的行数，INSERT如果两者都为零，则重试。

想象一下如果你有两个READ COMMITTED隔离的事务会发生什么。

• TX1 运行UPDATE（无效果）
• TX1 运行INSERT（插入一行）
• TX2 运行UPDATE（无效，TX1 插入的行还不可见）
• TX1COMMIT秒。
• TX2 运行INSERT, * 得到一个新的快照，可以看到 TX1 提交的行。该EXISTS子句返回 true，因为 TX2 现在可以看到 TX1 插入的行。

所以TX2没有效果。除非应用程序检查更新和插入的行数并在两者都报告零行时重试，否则它不会知道事务没有效果并且会愉快地继续。

它可以检查受影响的行数的唯一方法是将其作为两个单独的语句而不是多语句运行，或者使用一个过程。

您可以使用SERIALIZABLE隔离，但您仍然需要一个重试循环来处理序列化失败。

wCTE 版本可以防止丢失更新问题，因为INSERT它取决于是否UPDATE影响任何行，而不是单独的查询。

wCTE 并未消除独特的违规行为

可写 CTE 版本仍然不是可靠的 upsert。

考虑两个同时运行的事务。

• 两者都执行 VALUES 子句。

• 现在他们两个都执行该UPDATE部分。由于没有与UPDATEs where 子句匹配的行，因此两者都从更新中返回一个空结果集并且不进行任何更改。

• 现在两者都运行该INSERT部分。由于UPDATE两个查询都返回了零行，因此都尝试了INSERT该行。

一个成功。一个抛出一个独特的违规并中止。

只要应用程序检查其查询（即任何写得体面的应用程序）的错误结果并重试，这不会引起对数据丢失的担忧，但它使解决方案并不比现有的双语句版本更好。它并没有消除对重试循环的需要。

wCTE 相对于现有的双语句版本的优势在于它使用 的输出UPDATE来决定是否要INSERT，而不是对表使用单独的查询。这部分是一种优化，但它部分地防止了导致更新丢失的双语句版本的问题；见下文。

您可以单独运行 wCTE SERIALIZABLE，但您只会遇到序列化失败而不是唯一违规。它不会改变对重试循环的需求。

wCTE 似乎不易受到丢失更新的影响

我的评论表明此解决方案可能会导致更新丢失，但在审查后我认为我可能弄错了。

一年多以前，我不记得确切的情况，但我想我可能错过了这样一个事实，即唯一索引在事务可见性规则中有部分例外，以便允许一个插入事务等待另一个插入或滚动在继续之前返回。

或者我可能错过了这样一个事实，即INSERTwCTE 中的 取决于是否UPDATE受影响的任何行，而不是表中是否存在候选行。

唯一索引上的冲突INSERT等待提交/回滚

假设运行一个查询副本，插入一行。更改尚未提交。新元组存在于堆和唯一索引中，但它对其他事务尚不可见，无论隔离级别如何。

现在运行另一个查询副本。由于第一个副本尚未提交，因此插入的行尚不可见，因此更新不匹配任何内容。查询将继续尝试插入，这将看到另一个正在进行的事务正在插入相同的键，并将阻止等待该事务提交或回滚。

如果第一个事务提交，第二个事务将失败并出现唯一的违规，如上所述。如果第一个事务回滚，则第二个事务将继续执行其插入操作。

INSERT依赖于行数UPDATE可以防止丢失更新

与两个语句的情况不同，我不认为 wCTE 容易受到丢失更新的影响。

如果UPDATE没有效果，INSERT则将始终运行，因为它严格取决于是否UPDATE做了任何事情，而不是外部表状态。因此，它仍然可能因独特的违规而失败，但它不能默默地失败并完全失去更新。