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

Question

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

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

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

在插入或更新第二表时，我没有设置事务隔离级别，而且我运行了命令DBCC USEROPTIONS，它返回read_commissed。

Answer 1

第一：
看起来，您没有使用 SERIALIZABLE，而是使用 MSSQL 2005 中引入的快照隔离。这是一篇文章来了解其中的差异：
http:// blogs.msdn.com/b/craigfr/archive/2007/05/16/serialized-vs-snapshot-isolation-level.aspx

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

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

MSSQL 上的默认事务级别是 READ COMMITTED，但如果您打开选项 READ_COMMITTED_SNAPSHOT，它会将 READ COMMITTED 转换为 SNAPSHOT 就像每次使用READ COMMITTED时的事务一样。这会导致您收到错误消息。

准确地说，正如 VladV 指出的那样，它并不是真正使用 SNAPSHOT 隔离级别，而是使用 READ COMMITTED 行版本控制 而不是锁定，但仅基于语句，其中SNAPSHOT 在事务基础上使用行版本控制。

要了解差异，请查看以下内容：
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
在这里：
默认 SQL Server 隔离级别更改

您查看快照 如果您没有指定隔离，则使用隐式事务。打开此选项后，并且您实际上没有在修改语句上指定隔离级别（实际上您没有指定），MS SQL Server 将选择他认为正确的隔离级别。详细信息如下：
http://msdn.microsoft.com/en-us /library/ms188317(SQL.90).aspx

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

解决方案：
您需要按顺序执行操作，您可以通过专门在两个操作上使用具有SERIALIZABLE隔离级别的事务来实现这一点：插入/更新第一个操作时和插入时/更新第二个。
通过这种方式，您可以阻止相应的另一个，直到完成为止。

Answer 2

我们遇到了类似的问题 - 您会很高兴知道您应该能够在不删除 FK 约束的情况下解决该问题。

具体来说，在我们的场景中，我们在 READ COMMITTED 事务中频繁更新父表。我们还经常发生并发（长时间运行）快照事务，需要将行插入到具有父表 FK 的子表中 - 因此本质上它与您的情况相同，只是我们使用 READ COMMITTED 而不是 SEREALIZABLE 事务。

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

注意：缺点是现在表上有一个看似多余的约束，在更新父表时需要由 SQL Server 维护。也就是说，这可能是您考虑不同/备用聚集键的好机会...如果幸运的话，它甚至可以取代该表上另一个索引的需要...

不幸的是我找不到网上有一个关于为什么创建唯一约束可以解决问题的很好的解释。我可以解释为什么它有效的最简单方法是因为 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

Answer 3

根据我对“SNAPSHOT”、“SERIALIZABLE”和“READ COMMITTED SNAPSHOT”隔离级别的3次实验，当我仅使用“SNAPSHOT”隔离级别时，我得到了以下相同的错误更新已由其他事务更新的行，而我在“SERIALIZABLE”和“READ COMMITTED SNAPSHOT”隔离级别下没有收到以下相同的错误：

由于更新冲突，快照隔离事务中止。你
无法使用快照隔离直接访问表“dbo.person”或
间接在数据库“test”中更新、删除或插入该行
已被另一事务修改或删除。重试
事务或更改更新/删除的隔离级别
声明。

并且，如 文档如下所述，使用“SNAPSHOT”隔离级别，我们会得到与上面相同的错误更新已被其他事务更新的行。而且，我认为我们无法解决或避免上述错误，因此我们可以做的是再次重试交易，如上面的错误所述。因此，如果我们的应用程序收到上述错误，我们将能够将上述错误作为异常处理来再次重试事务：

快照事务始终使用乐观并发控制，
保留任何会阻止其他事务的锁定
更新行。如果快照事务尝试提交更新
到事务开始后更改的行，事务
被回滚，并引发错误。

对于“SNAPSHOT”隔离级别的实验，我使用“id”和“name”创建了“person”表 “test”数据库中如下所示：

id	name
1	John
2	David

，我使用 SQL 查询执行了这些步骤，如下所示：

流程	事务 1 (T1)	事务 2 (T2)	说明
步骤 1	开始；		T1 开始。
第 2 步		BEGIN;	T2 开始。
第 3 步	UPDATE person SET name = 'Tom' WHERE id = 2;		T1 将“David”更新为“Tom”，因此该行被 T1 锁定，直到 T1 提交。
第 4 步		UPDATE person SET name = 'Lisa' WHERE id = 2;	T2 无法将“Tom”更新为“Lisa”，因为该行已被 T1 锁定，因此 T2等待 T1 通过提交更新此行来解锁此行。
第 5 步	COMMIT;	等待...< /strong>	T1 提交。
第 6 步		ROLLBACK; 快照隔离事务因更新冲突而中止。您不能使用快照隔离直接或间接访问数据库“test”中的表“dbo.person”来更新、删除或插入已被另一个事务修改或删除的行。重试事务或更改更新/删除语句的隔离级别。	现在，T2 自动回滚并获取错误。