在 SQL Server 2005 上违反 INSERT WHERE COUNT(*) = 0 上的 UNIQUE KEY 约束

我正在从多个进程插入 SQL 数据库。这些进程有时可能会尝试将重复数据插入表中。我尝试以处理重复项的方式编写查询，但我仍然得到：

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.

我的查询看起来像这样：

INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0

约束“UK1_MyConstraint”表示在 MyTable 中，3 个字段的组合应该是唯一的。

我的问题：

1. 为什么这不起作用？
2. 我需要进行哪些修改才能避免因违反约束而出现异常？

请注意，我知道还有其他方法可以解决“如果不存在则插入”的原始问题，例如（总而言之）：

• 使用 TRY CATCH
• IF NOT EXIST INSERT（在具有可序列化隔离的事务内）

我应该使用其中一种方法吗？

Edit 1创建表的SQL：

CREATE TABLE [dbo].[MyTable](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FieldA] [bigint] NOT NULL,
  [FieldB] [int] NOT NULL,
  [FieldC] [char](3) NULL,
  [FieldD] [float] NULL,
  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
  (
    [Id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON),
  CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED 
  (
    [FieldA] ASC,
    [FieldB] ASC,
    [FieldC] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

Edit 2决定：

只是为了更新这一点 - 我决定使用链接问题中建议的“JFDI”实施（link https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196）。尽管我仍然很好奇为什么最初的实现不起作用。

为什么这不起作用？

我相信 SQL Server 的默认行为是在不再需要共享锁时立即释放它们。您的子查询将导致表上出现短期共享 (S) 锁，该锁将在子查询完成后立即释放。

此时，没有什么可以阻止并发事务插入您刚刚验证的不存在的行。

我需要进行哪些修改才能避免因违反约束而出现异常？

添加HOLDLOCK对子查询的提示将指示 SQL Server 保留锁定，直到事务完成。 （在您的情况下，这是隐式事务。）HOLDLOCK提示相当于SERIALIZABLE提示，它本身相当于您在“其他方法”列表中引用的可序列化事务隔离级别。

The HOLDLOCK仅提示就足以保留 S 锁并防止并发事务插入您要防范的行。但是，您可能会发现唯一的密钥冲突错误被死锁取代，并且以相同的频率发生。

如果您只保留表上的 S 锁，请考虑插入同一行的两次并发尝试之间的竞争，以锁步方式进行 - 两者都成功获取表上的 S 锁，但都无法成功获取独占锁(X) 执行插入所需的锁。

幸运的是，还有另一种锁类型适合这种情况，称为更新 (U) 锁。 U 锁与 S 锁相同，但有以下区别：同一资源上可以同时持有多个 S 锁，但一次只能持有一个 U 锁。 （换句话说，虽然 S 锁彼此兼容（即可以在没有冲突的情况下共存），但 U 锁彼此不兼容，但可以与 S 锁共存；更进一步，排他 (X) 锁则不可以与 S 或 U 锁兼容）

您可以使用以下命令将子查询上的隐式 S 锁升级为 U 锁：UPDLOCK hint.

现在，在表中插入同一行的两次并发尝试将在初始 select 语句处序列化，因为这会获取（并持有）一个 U 锁，而该 U 锁与并发插入尝试中的另一个 U 锁不兼容。

空值

FieldC 允许 NULL 值这一事实可能会引起一个单独的问题。

If ANSI_NULLS开启（默认）则相等性检查FieldC=NULL即使在 FieldC 为 NULL 的情况下也会返回 false（您必须使用IS NULL运算符检查 null 时ANSI_NULLS已开启）。由于 FieldC 可为空，因此在插入 NULL 值时，重复检查将不起作用。

要正确处理空值，您需要修改 EXISTS 子查询以使用IS NULL运算符而不是=当插入 NULL 值时。 （或者您可以更改表以禁止所有相关列中出现 NULL。）

SQL Server 联机书籍参考

• 锁定提示 http://msdn.microsoft.com/en-us/library/ms187373.aspx
• 锁兼容性矩阵 http://msdn.microsoft.com/en-us/library/ms186396.aspx
• ANSI_NULLS http://msdn.microsoft.com/en-us/library/ms188048.aspx