快照隔离事务由于更新冲突而中止 [英] Snapshot isolation transaction aborted due to update conflict

问题描述

以下声明:

INSERT INTO dbo.Changes([Content], [Date], [UserId], [CompanyId]) 
  VALUES (@1, @2, @3, @4);
SELECT @@identity;

给我这个SQL错误3960:

gives me this SQL error 3960:

快照隔离事务由于更新冲突而中止.你 无法使用快照隔离直接访问表"dbo.Companies" 或间接在数据库"myDatabase"中进行更新，删除或插入 被另一个事务修改或删除的行. 重试交易或更改隔离级别 更新/删除语句.

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Companies' directly or indirectly in database 'myDatabase' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

据我了解，从错误消息来看，在另一个连接正在修改dbo.Companies的过程中，我不应该更新，删除或插入表dbo.Companies.

As far as I understood, from the error message, I should not update, delete, or insert to table dbo.Companies during the time another connection is modifying dbo.Companies.

但是为什么当我在另一个表dbo.Changes中插入新行(它具有到dbo.Companies的外键)并且没有删除dbo.Companies中的引用行时却发生了，但是我只是在更新表中的行dbo.Companies而不是主键?这样应该可以，不是吗? (这是SQL Server中的错误吗?)

But why it occurs when I was inserting a new row to another table dbo.Changes (which has foreign key to dbo.Companies) and I was not deleting the referenced row in dbo.Companies, but I was just updating row in dbo.Companies and not the primary key? This should work ok, shouldn't it? (Is it a bug in SQL Server?)

更新:

表如下所示:

dbo.Changes([Id] int PK, [Content] nvarchar, 
  [Date] datetime, [UserId] int, [CompanyId] int -> dbo.Companies.[Id])
dbo.Companies([Id] int PK, [Name] nvarchar)

第二次更新正在做

UPDATE dbo.Companies WHERE [Id] = @1 SET [Name] = @2;

推荐答案

似乎SQL Server 将在必须读取的任何记录上获取更新锁，即使它没有对其进行修改也是如此. .

It appears SQL Server will acquire update locks on any record it has to read even if it doesn't modify it.

有关此 microsoft.public.sqlserver的更多信息.server线程:

在CustomerContactPerson上没有支持索引，该语句

Without a supporting index on CustomerContactPerson, the statement

从ContactPerson删除，其中ID = @ID;

DELETE FROM ContactPerson WHERE ID = @ID;

将需要一个当前" 读取CustomerContactPerson中的所有行，以确保存在 没有引用已删除内容的CustomerContactPerson行 ContactPerson行.有了索引，DELETE可以确定 在不阅读以下内容的情况下，CustomerContactPerson中没有相关的行 受其他事务影响的行.

Will require a "current" read of all the rows in CustomerContactPerson to ensure that there are no CustomerContactPerson rows that refer to the deleted ContactPerson row. With the index, the DELETE can determine that there are no related rows in CustomerContactPerson without reading the rows affected by the other transaction.

此外，在快照中 交易将要读取的数据读取模式 阅读时，周围和更新将采取UPDLOCK.这样可以确保 您是根据当前"数据进行更新，而不是 一致"(快照)数据，并且当您发布DML时， 数据不会被锁定，您也不会不经意间覆盖另一个数据 会话的更改.

Additionally, in a snapshot transaction the pattern for reading data which you are going to turn around and update is to take an UPDLOCK when you read. This ensures that you are making your update on the basis of "current" data, not "consistent" (snapshot) data, and that when you issue the DML, it the data won't be locked, and you won't unwittingly overwrite another session's change.

对我们来说，解决方法是向外键添加索引

The fix for us was adding indexes to the foreign keys

在您的示例中，我怀疑将索引添加到Changes.CompanyId将有所帮助.我不确定这是否是真正的解决方案. SQL Server优化器可以选择不使用索引吗?

In your example, I suspect adding an index to Changes.CompanyId will help. I'm not sure if this is a real solution. Can the SQL Server optimizer choose not to use the index?

这篇关于快照隔离事务由于更新冲突而中止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！