sql-server - 由于更新冲突，快照隔离事务中止

Question

以下声明:

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

给我这个 SQL 错误 3960:

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期间。

但是为什么当我向另一个表插入新行时会出现这种情况 dbo.Changes (其中有 dbo.Companies 的外键)并且我没有删除 dbo.Companies 中引用的行，但我只是更新 dbo.Companies 中的行而不是主键？这应该可以正常工作，不是吗？ (这是 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;

Answer 1

看来 SQL Server 将在它必须读取的任何记录上获取更新锁，即使它不修改记录。

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

Without a supporting index on CustomerContactPerson, the statement

DELETE FROM ContactPerson WHERE ID = @ID;

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.

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.

我们的解决办法是向外键添加索引

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