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个字段的组合应该是唯一的。
我的问题：
为什么这不工作？
>我需要做什么修改，因为违反约束是没有异常的机会？
请注意，我知道还有其他方法来解决原始问题“INSERT(如果不存在”)，如(总结)：
>使用TRY CATCH
> IF NOT EXIST INSERT(在具有可序列化隔离的事务中)
我应该使用其中一种方法吗？
编辑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,ALLOW_PAGE_LOCKS = ON)
)
编辑2决定：
只是为了更新这个 – 我决定使用链接的问题(link)中建议的“JFDI”实现。虽然我仍然很好奇为什么原来的执行不起作用。
解决方法
我相信sql Server的默认行为是在不再需要时释放共享锁。您的子查询将导致表上短暂的共享(S)锁定，一旦子查询完成，它将被释放。
在这一点上，没有什么可以阻止并发事务插入刚刚验证的行不存在。
我需要做什么修改，所以没有机会由于约束违规的异常？
将HOLDLOCK提示添加到子查询将指示sql Server保持锁定，直到事务完成。 (在你的情况下，这是一个隐式事务)。HOLDLOCK提示等同于SERIALIZABLE提示，它本身等同于您在“其他方法”列表中引用的可序列化事务隔离级别。
HOLDLOCK单独提示将足以保留S锁并防止并发事务插入您正在防范的行。但是，您可能会发现您的唯一键违规错误被死锁所代替，发生在相同的频率。
如果您仅在表上保留S锁，请考虑在两次同时尝试插入同一行(在锁步骤中进行)之间的竞争 – 两者都能成功获取表上的S锁，但两者都不能成功获取Exclusive (X)锁需要执行插入。
幸运的是，这个确切情况有另一种锁类型，称为Update(U)锁。 U锁与S锁相同，具有以下区别：当同一资源同时保持多个S锁时，一次只能保持一个U锁。 (换句话说，虽然S锁相互兼容(即可以共存而没有冲突)，U锁不能相互兼容，但可以与S锁共存;进一步沿着频谱，Exclusive(X)锁不是兼容S或U锁)
您可以使用UPDLOCK提示将子查询中的隐式S锁升级到U锁。
现在将在初始select语句中插入同一行的两个并发尝试序列化，因为它获取(并保存)与并发插入尝试的另一个U锁不兼容的U锁。
NULL值
FieldC允许NULL值的事实可能会产生一个单独的问题。
如果ANSI_NULLS是(默认)，则等于’FieldC = NULL’将返回false，即使FieldC为NULL(在ANSI_NULLS打开时必须使用IS NULL运算符来检查null)。由于FieldC为空，因此当您插入NULL值时，您的重复检查将无法正常工作。
要正确处理null，您将需要修改EXISTS子查询以使用IS NULL运算符，而不是=当插入值为NULL时。 (或者您可以更改表以禁止所有相关列中的NULL)。
sql Server联机丛书参考
