logo标签列表

为什么我会收到“由于更新冲突，快照隔离事务中止”的错误消息？

sql-serversnapshot-isolation
我们有两个表 1. Parent（Id int identity，Date datetime，Name nvarchar） 2. Child（Id int identity，ParentId int，Date datetime，Name nvarchar） Child表与Parent表建立了外键关系。 我们已经启用了数据库级别的读取提交快照隔离。 我们只会对Parent和Child表进行插入和删除操作（不会有更新操作）。 我们有一个进程（事务），该进程会先删除Child表中的旧数据，然后再删除Parent表中的旧数据。 我们还有多个其他进程（事务），用于向Parent表插入新数据，然后再向Child表插入新数据。 尽管插入进程不会在Child表中插入与删除进程要删除的Parent行相关联的新Child行，而是仅创建与新Parent相关联的一个或多个新Child行和新Parent行，但删除进程经常（但不总是）被回滚。 删除Parent行时出现的错误为： 由于更新冲突，快照隔离事务中止。您无法使用快照隔离来直接或间接地访问数据库'Test'中的表'dbo.Child'，以更新、删除或插入已被另一个事务修改或删除的行。请重试该事务或更改更新/删除语句的隔离级别。 我知道有人建议在外键列上创建索引-理想情况下（出于空间/性能原因），我们不太愿意这样做-除非这是确保正常运行的唯一可靠方法。 注意到这个：https://stackoverflow.com/questions/10718668/snapshot-isolation-transaction-aborted-due-to-update-conflict 还有一篇很好的文章：https://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level 但这两者都没有给我带来我想要的理解 :)
  1. 你有任何触发器吗？
  2. 父/子关系是否由外键强制执行？
 - Remus Rusanu
没有触发器。是的，外键关系对于这个问题至关重要 - 没有它就不会发生。如果在外键列上存在非唯一索引，也不会发生这种情况。 - Mark
5个回答
在从父表中删除数据时，SQL Server必须检查是否存在任何引用该行的外键子行。当没有适当的子索引时，此检查将对子表进行全面扫描。

Full child scan

如果扫描遇到自删除命令的快照事务开始后被修改过的行，则会因为更新冲突而失败（这是定义）。全面扫描显然会触及表中的每一行。 通过适当的索引，SQL Server可以定位并只测试与待删除父项可能匹配的子表中的行。当这些特定行没有被修改时，不会发生更新冲突。

Child seek

请注意，在行版本隔离级别下，外键检查会获取共享锁（为了正确性）并检测更新冲突。例如，上述子表访问的内部提示如下： 
PhyOp_Range TBL: [dbo].[Child]
    Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )
很遗憾，目前执行计划中没有显示这一点。 我的相关文章：
我在一个类似问题的讨论串上看到了这个微软员工的回复，我觉得他的见解非常有深度。 没有在CustomerContactPerson上建立支持的索引，那么执行DELETE FROM ContactPerson WHERE ID = @ID;语句将需要对所有的CustomerContactPerson行进行“当前”读取，以确保没有任何CustomerContactPerson行引用了被删除的ContactPerson行。而有了索引，DELETE操作可以确定在CustomerContactPerson中没有相关的行，而无需读取其他事务影响的行。 此外，在快照事务中，读取数据并打算更新时的模式是在读取时使用UPDLOCK。这样可以确保您基于“当前”数据而不是“一致”（快照）数据进行更新，并且在发出DML时，数据不会被锁定，您也不会无意中覆盖其他会话的更改。 我已经收到我们开发团队的更新。看起来我的理解是正确的。 以下是他们的解释。快照隔离级别保证您将看到数据库的单个、一致的版本。当您在事务开始时读取CustomerContactPerson行时，您将永远无法读取到该行的较新版本。在ContactPerson上执行DELETE操作将要求您读取比事务快照更晚的CustomerContactPerson行版本，因此会产生更新冲突。即使您实际上不会更新CustomerContactPerson行，仅仅读取它以验证外键也会被视为相同情况。 此外，当表扫描遇到受其他事务影响的记录时，我们可以通过在读取时锁定您打算更新的行来避免冲突。 另一方面，快照隔离级别是真正乐观的，因为要修改的数据实际上并没有提前锁定，而是在选择进行修改时才会被锁定。当数据行满足更新条件时，快照事务会验证该数据在快照事务开始后是否被其他事务修改。如果数据没有被其他事务修改，快照事务会锁定数据、更新数据、释放锁并继续执行。如果数据已被其他事务修改，则会发生更新冲突，快照事务将回滚。 http://msdn.microsoft.com/en-us/library/ms345124.aspx
您必须使用快照隔离级别，而不是快照读取已提交。只有在使用快照隔离级别时才会发生快照更新冲突，并且不会在使用快照读取已提交时发生。 如果您能够使用快照读取已提交，那么这个问题将非常容易解决。 快照读取已提交隔离级别使用锁定（并在每个语句之前获取行版本信息），从而使快照更新冲突变得不可能。 快照更新冲突仅在快照隔离级别下发生（而不是快照读取已提交），这是因为当您的事务试图提交更改时，它试图提交的数据版本与事务开始时的版本不同。根据您描述的情况，很难确切理解您为何遇到此问题，也许与表扫描相关，而如果您在外键上拥有适当的索引，则应该是索引搜索。 主要问题是，您必须使用SNAPSHOT隔离级别，而不是您所述的SNAPSHOT READ COMMITTED隔离级别，您可以通过使用SNAPSHOT READ COMMITTED来解决这个问题。 你唯一能够获得SNAPSHOT的方法是在事务开始时将隔离级别设置为快照。要使用SNAPSHOT READ COMMITTED，你必须在数据库中启用它，并且在查询或存储过程中不设置任何隔离级别。
你为什么认为其他所有答案都是错误的，而且没有一个假设OP在数据库配置方面出错？尤其是考虑到其中一个答案已被OP接受为正确答案？ - RDFozz
我并没有说其他答案是错误的。它们很有可能是正确的。我的观点是，在使用读提交快照隔离级别时，快照更新冲突是不会发生的，而问题提问者声称他正在使用这个隔离级别。他一定是在使用快照隔离级别，但错误地声称他正在使用读提交快照隔离级别。 - user3444696
我还应该指出，如果发帖者能够使用快照读取提交（Snapshot Read Committed），那么这将完全是他/她想要的解决方案，因为它可以在不需要索引的情况下解决问题，而且发帖者表示他们不愿意创建索引。 - user3444696
OP的口头禅是“理想情况下我们宁愿不这样做（出于空间/性能原因）- 除非这是唯一可靠的方法来使其工作。”但当查询由于不同的性能问题而失败时，这种说法立即崩溃了。如果对于外键索引来说空间确实是一个问题，那么你绝对不希望进行完整的聚集索引扫描。不为索引视图创建索引是另一回事 - 它们有各种性能影响，不是立即显而易见的，但我从未担心过简单的外键索引。 - Simon_Weaver

错误信息提供了一般修复方法，SqlWorldWide在评论中建议了一个解决方案（"使用可串行化隔离级别"）。问题出在您的事务隔离级别上。解决方法是更改该事务的隔离级别。Microsoft在一篇名为教训1：理解可用的事务隔离级别的文章中对此进行了详细说明。

在该文章中，Microsoft在一个名为更新冲突的部分中声明：

还有一个并发问题尚未提及，因为它只适用于快照隔离级别。如果在快照隔离中读取了特定行（或行的版本），SQL Server保证在事务后期再次执行查询时会得到相同的行。但是，如果后续的查询是UPDATE或DELETE语句，并且该行自第一次读取以来已经发生了更改，会发生什么情况呢？SQL Server不能使用当前版本的行作为更新的基础，因为这将违反快照事务活动期间行不变的承诺。而且它也不能使用快照事务使用的行版本作为基础，因为更新或删除该行的其他事务将遇到丢失更新（这在SQL Server中是不允许或支持的）。相反，快照事务将被回滚，并收到以下错误消息：

Msg 3960, Level 16, State 4, Line 1 由于更新冲突，快照隔离事务被中止。您不能直接或间接使用快照隔离访问数据库'TestDatabase'中的表'Test.TestTran'来更新、删除或插入已被其他事务修改或删除的行。请重试事务或更改更新/删除语句的隔离级别。

这与您收到的错误类似。

谢谢。是的，我明白这一点……但我不明白的是为什么在另一个事务中，我可以为完全不同的父级添加一个完全不同的子级，而这仍然会使第一个事务的父级无效，尽管它与之无关（然而，如果我只是在子级的外键列上添加一个非唯一索引，那么这个问题就完全解决了！） - Mark
1有趣。你应该将这一点添加到原始问题中，因为这将有助于提供更好的答案。在外键关系中，你有任何级联删除规则吗？ - Antoine Hernandez
谢谢 - 看起来级联规则无论如何都没有任何影响。 - Mark

我在推测，但我认为这就是正在发生的事情。当你删除父行时，引擎需要执行外键中定义的任何ON DELETE规则——无论你是否已经知道自己已经删除了所有子行，引擎没有办法知道这一点。由于你所说的，在子表中外键列上没有索引（出于性能原因），引擎会使用聚集索引扫描（我假设你在子表中有一个主键），一旦它遇到第一行过期的行，就会中止事务，因为它无法了解插入在它查看的快照之外的外键值。

如果你在子表的外键列上建立了索引，服务器将能够有选择地访问可能受影响的行，也就是没有行（因为你那时已经删除了它们），从而避免快照冲突和聚集索引扫描。