如何修复“由于更新冲突而中止的快照隔离事务”？

Question

我看到一条与事务隔离级别相关的错误消息。涉及两个表，第一个表被频繁更新，事务隔离级别设置为SERIALIZABLE，第二个表在第一个表上有一个外键。

执行第二个表的插入或更新时会出现问题。每隔几个小时我就会收到以下错误消息：

由于更新冲突，快照隔离事务中止。不能使用快照隔离直接或间接地访问数据库“DB”中的表“dbo.first”，以更新、删除或插入已被其他事务修改或删除的行。重新尝试事务或更改update/delete语句的隔离级别。

在插入或更新第二个表时，我不设置事务隔离级别，我还运行命令DBCC 并返回read_committed。

Answer 1

第一：

看起来，您不是在使用SERIALIZABLE，而是使用MSSQL 2005引入的快照隔离。这里有一篇文章来理解两者之间的区别：

http://blogs.msdn.com/b/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx

=> --这是基于错误、消息，但是正如您在注释中再次解释的那样，错误出现在编辑第二个表时。

第二：

对于修改，MSSQL总是尝试获取锁，因为第一个表上有锁(通过使用事务)，由于(外键)，第二个表的锁升级为锁，操作失败。因此，每一次修改实际上都会导致一次小型交易。

MSSQL上的默认事务级别是READ COMMITTED，但是如果您打开选项READ_COMMITTED_SNAPSHOT，它将在每次使用READ COMMITTED时将READ COMMITTED转换为类似于SNAPSHOT的事务。这将导致您得到的错误消息。

准确地说，正如VladV所指出的，它实际上不是使用SNAPSHOT隔离级别，而是使用带有行版本控制的READ COMMITTED ，而不是锁定，而是仅在语句基础上使用行版本控制，其中SNAPSHOT在E 116事务E 217基础上使用行版本控制。

要理解两者之间的差异，请查看以下内容：

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

要了解更多关于READ_COMMITTED_SNAPSHOT的信息，请在这里详细解释：

http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

在这里：默认Server更改

如果您没有指定SNAPSHOT隔离，那么另一个原因是使用隐式事务。在图灵这个选项打开之后，并且您实际上没有在修改语句上指定隔离级别(您没有这样做)，MS SQL server将选择他认为正确的隔离级别。以下是详细信息：

http://msdn.microsoft.com/en-us/library/ms188317(SQL.90).aspx

但是，对于所有这些场景，解决方案是相同的。

解决方案：

您需要按顺序执行这些操作，并且可以通过使用具有SERIALIZABLE 隔离级别的的事务来执行这一操作:当插入/更新第一个操作和插入/更新第二个操作时。

这样，您将阻塞相应的另一个，直到完成为止。

Answer 2

我们有一个类似的问题--而且您很高兴知道您应该能够解决这个问题，而不需要删除FK约束。

具体来说，在我们的场景中，我们经常更新已提交的读事务中的父表。我们还经常发生并发(长时间运行)快照事务，需要将行插入到带有FK到父表的子表中--因此本质上是与您的场景相同，但我们使用的是READ提交的事务，而不是SEREALIZABLE事务。

要解决这个问题，请在FK列上的主表上创建一个新的非NONCLUSTERED约束。此外，您还必须在创建唯一约束之后重新创建FK，因为这将确保FK现在引用约束(而不是集群键)。

注意:缺点是，在对父表进行更新时，表上有一个看似多余的约束，需要由SQL server维护。尽管如此，这可能是一个很好的机会，您可以考虑一个不同的/替代集群key...and，如果您幸运，它甚至可以取代需要在这个表上的另一个索引。

不幸的是，我在网上找不到一个很好的解释为什么创建一个唯一的约束来解决这个问题。我能解释的最简单的方法是，因为FK现在只引用唯一约束，而对父表(即非FK引用列)的修改不会导致快照事务中的更新冲突，因为FK现在引用的是不变的唯一约束项。与集群键相比，父表中任何列的更改都会影响表中的行版本，而且由于FK看到更新的版本号，快照事务需要中止。

此外，如果在非快照事务中删除父行，则集群和唯一约束都将受到影响，并且，正如预期的那样，快照事务将回滚(因此保持FK完整性)。

我已经能够使用我从这个博客条目中改编的上面的示例代码来重现这个问题

---------------------- SETUP Test database
-- Creating Customers table without unique constraint
USE master;
go

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SnapshotTest')
BEGIN;
DROP DATABASE SnapshotTest;
END;
go

CREATE DATABASE SnapshotTest;
go

ALTER DATABASE SnapshotTest
SET ALLOW_SNAPSHOT_ISOLATION ON;
go

USE SnapshotTest;
go

CREATE TABLE Customers
   (CustID int NOT NULL PRIMARY KEY,CustName varchar(40) NOT NULL);

CREATE TABLE Orders
  (OrderID char(7) NOT NULL PRIMARY KEY,
   OrderType char(1) CHECK (OrderType IN ('A', 'B')),
   CustID int NOT NULL REFERENCES Customers (CustID)
  );

INSERT INTO Customers (CustID, CustName) VALUES (1, 'First test customer');

INSERT INTO Customers (CustID, CustName) VALUES (2, 'Second test customer');
GO

---------------------- TEST 1: Run this test before test 2
USE SnapshotTest;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

-- Check to see that the customer has no orders
SELECT * FROM Orders WHERE  CustID = 1;

-- Update the customer
UPDATE Customers SET CustName='Updated customer' WHERE  CustID = 1;
-- Twiddle thumbs for 10 seconds before commiting
WAITFOR DELAY '0:00:10';

COMMIT TRANSACTION;
go

-- Check results
SELECT * FROM Customers (NOLOCK);
SELECT * FROM Orders (NOLOCK);
GO

---------------------- TEST 2: Run this test in a new session shortly after test 1
USE SnapshotTest;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT * FROM   Customers WHERE  CustID = 1;
INSERT INTO Orders (OrderID, OrderType, CustID) VALUES ('Order01', 'A', 1);

-- Twiddle thumbs for 10 seconds before commiting
WAITFOR DELAY '0:00:10';

COMMIT TRANSACTION;
go

-- Check results
SELECT * FROM Customers (NOLOCK);
SELECT * FROM Orders (NOLOCK);
go

要修复上述场景，请重新设置测试数据库。然后在运行测试1和2之前运行以下脚本。

ALTER TABLE Customers 
ADD CONSTRAINT UX_CustID_ForSnapshotFkUpdates UNIQUE NONCLUSTERED (CustID)

-- re-create the existing FK so it now references the constraint instead of clustered index (the existing FK probably has a different name in your DB)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK__Orders__CustID__1367E606]

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD FOREIGN KEY([CustID])
REFERENCES [dbo].[Customers] ([CustID])
GO