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

2022-01-01 00:00:00 sql-server-2005 sql-server

我从多个进程插入到 SQL 数据库中.进程有时可能会尝试将重复数据插入表中.我试图以一种可以处理重复项的方式编写查询，但我仍然得到:

I'm inserting into a SQL database from multiple processes. It's likely that the processes will sometimes try to insert duplicate data into the table. I've tried to write the query in a way that will handle the duplicates but I still get:

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个字段的组合应该是唯一的.

The constraint 'UK1_MyConstraint' says that in MyTable, the combination of the 3 fields should be unique.

我的问题:

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

请注意，我知道还有其他方法可以解决如果不存在则插入"的原始问题，例如(总结):

Note that I'm aware that there are other approaches to solving the original problem of "INSERT if not exists" such as (in summary):

  • 使用 TRY CATCH
  • IF NOT EXIST INSERT(在具有可序列化隔离的事务中)

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

Should I be using one of the approaches?

编辑 1 用于创建表的 SQL:

Edit 1 SQL for Creating Table:

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)
)

编辑 2 决定:

只是为了更新这个 - 我决定使用链接问题中建议的JFDI"实现(链接).虽然我仍然很好奇为什么原始实现不起作用.

Just to update this - I've decided to use the "JFDI" implementation suggested in the linked question (link). Although I'm still curious as to why the original implementation doesn't work.

推荐答案

为什么这不起作用?

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

I believe the default behaviour of SQL Server is to release shared locks as soon as they are no longer needed. Your sub-query will result in a short-lived shared (S) lock on the table, which will be released as soon as the sub-query completes.

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

At this point there is nothing to prevent a concurrent transaction from inserting the very row you just verified was not present.

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

向您的子查询添加 HOLDLOCK 提示将指示 SQL Server 保持锁定直到事务完成.(在您的情况下，这是一个隐式事务.) HOLDLOCK 提示等效于 SERIALIZABLE 提示，它本身等效于您在中引用的可序列化事务隔离级别您的其他方法"列表.

Adding the HOLDLOCK hint to your sub-query will instruct SQL Server to hold on to the lock until the transaction is completed. (In your case, this is an implicit transaction.) The HOLDLOCK hint is equivalent to the SERIALIZABLE hint, which itself is equivalent to the serializable transaction isolation level which you refer in your list of "other approaches".

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

The HOLDLOCK hint alone would be sufficient to retain the S lock and prevent a concurrent transaction from inserting the row you are guarding against. However, you will likely find your unique key violation error replaced by deadlocks, occurring at the same frequency.

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

If you're retaining only an S lock on the table, consider a race between two concurrent attempts to insert the same row, proceeding in lockstep -- both succeed in acquiring an S lock on the table, but neither can succeed in acquiring the Exclusive (X) lock required to execute the insert.

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

Luckily there is another lock type for this exact scenario, called the Update (U) lock. The U lock is identical to an S lock with the following difference: whilst multiple S locks can be held simultaneously on the same resource, only one U lock may be held at a time. (Said another way, whilst S locks are compatible with each other (i.e. can coexist without conflict), U locks are not compatible with each other, but can coexist alongside S locks; and further along the spectrum, Exclusive (X) locks are not compatible with either S or U locks)

您可以使用 UPDLOCK 提示将子查询上的隐式 S 锁升级为 U 锁.

You can upgrade the implicit S lock on your sub-query to a U lock using the UPDLOCK hint.

在表中插入同一行的两次并发尝试现在将在初始 select 语句中被序列化，因为这获取(并持有)一个 U 锁，该 U 锁与来自并发插入尝试的另一个 U 锁不兼容.

Two concurrent attempts to insert the same row in the table will now be serialized at the initial select statement, since this acquires (and holds) a U lock, which is not compatible with another U lock from the concurrent insertion attempt.

NULL 值

一个单独的问题可能来自 FieldC 允许 NULL 值的事实.

A separate problem may arise from the fact that FieldC allows NULL values.

如果 ANSI_NULLS 开启(默认)，那么相等检查 FieldC=NULL 将返回 false，即使在 FieldC 为 NULL 的情况下(你必须使用 IS NULL 运算符在 ANSI_NULLS 开启时检查空值).由于 FieldC 可以为空，因此在插入 NULL 值时，您的重复检查将不起作用.

If ANSI_NULLS is on (default) then the equality check FieldC=NULL would return false, even in the case where FieldC is NULL (you must use the IS NULL operator to check for null when ANSI_NULLS is on). Since FieldC is nullable, your duplicate check will not work when inserting a NULL value.

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

To correctly deal with nulls you will need to modify your EXISTS sub-query to use the IS NULL operator rather than = when a value of NULL is being inserted. (Or you can change the table to disallow NULLs in all the concerned columns.)

SQL Server 联机丛书参考

  • 锁定提示
  • 锁兼容性矩阵
  • ANSI_NULLS

相关文章