sql-server – 在SQL Server 2005上违反INSERT WHERE COUNT(*)=

发布时间：2020-12-12 06:57:33 所属栏目：MsSql教程来源：网络整理

导读：我从多个进程插入SQL数据库。有时这些进程有时会尝试将重复的数据插入到表中。我试图以一种处理重复的方式编写查询，但是我仍然得到： System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate 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个字段的组合应该是唯一的。

我的问题：

为什么这不工作？
>我需要做什么修改，因为违反约束是没有异常的机会？

请注意，我知道还有其他方法来解决原始问题“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联机丛书参考

> Locking Hints
> Lock Compatibility Matrix
> ANSI_NULLS

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!