在SQL Server 2005上INSERT WHERE COUNT（*）= 0上违反UNIQUE KEY约束

时间：2021-01-01 04:17:28

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:

我正在从多个进程插入一个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.

My query looks something like:

我的查询看起来像：

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

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

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

My questions:

我的问题：

  1. Why doesn't this work?
  2. 为什么这不起作用？
  3. What modification do I need to make so there is no chance of an exception due to the constraint violation?
  4. 我需要进行哪些修改才能因为约束违规而无法出现异常？

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

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

  • Using TRY CATCH
  • 使用TRY CATCH
  • IF NOT EXIST INSERT (inside a transaction with serializable isolation)
  • 如果不是EXIST INSERT（在具有可序列化隔离的事务中）

Should I be using one of the approaches?

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

Edit 1 SQL for Creating Table:

编辑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 Decision:

编辑2决定：

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.

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

3 个解决方案

#1

39  

Why doesn't this work?

为什么这不起作用？

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.

我相信SQL Server的默认行为是在不再需要时释放共享锁。您的子查询将导致表上的短暂共享（S）锁定，该子锁定将在子查询完成后立即释放。

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

此时，没有什么可以防止并发事务插入刚刚验证过的行。

What modification do I need to make so there is no chance of an exception due to the constraint violation?

我需要进行哪些修改才能因为约束违规而无法出现异常？

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提示添加到子查询将指示SQL Server保持锁定，直到事务完成。 （在您的情况下，这是一个隐式事务。）HOLDLOCK提示等同于SERIALIZABLE提示，它本身等同于您在“其他方法”列表中引用的可序列化事务隔离级别。

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.

单独的HOLDLOCK提示足以保留S锁并防止并发事务插入您要防范的行。但是，您可能会发现您的唯一密钥违例错误被死锁替换，发生在同一频率上。

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.

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

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)

幸运的是，这个确切的场景还有另一种锁类型，称为Update（U）锁。 U锁与S锁相同，但有以下区别：虽然可以在同一资源上同时保持多个S锁，但一次只能保持一个U锁。 （换句话说，虽然S锁彼此兼容（即可以共存但没有冲突），U锁彼此不兼容，但可以与S锁共存;并且沿着频谱，独占（X）锁不是兼容S或U锁）

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

您可以使用UPDLOCK提示将子查询上的隐式S锁升级为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.

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

NULL values

NULL值

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

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.

如果启用ANSI_NULLS（默认），则即使在FieldC为NULL的情况下，相等性检查FieldC = NULL也将返回false（当ANSI_NULLS打开时，必须使用IS NULL运算符检查null）。由于FieldC可以为空，因此在插入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.)

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

SQL Server Books Online References

SQL Server联机丛书参考

#2

4  

RE: "I'm still curious as to why the original implementation doesn't work."

RE：“我仍然很好奇为什么最初的实现不起作用。”

Why would it work?

它为什么会起作用？

What is there to prevent two concurrent transactions being interleaved as follows?

有什么可以阻止两个并发事务交错如下？

Tran A                                Tran B
---------------------------------------------
SELECT COUNT(*)...
                                  SELECT COUNT(*)...
INSERT ....
                                  INSERT... (duplicate key violation).

The only time conflicting locks will be taken is at the Insert stage.

锁定锁定的唯一时间是在Insert阶段。

To see this in SQL Profiler

在SQL事件探查器中看到这一点

Create Table Script

create table MyTable
(
FieldA int NOT NULL, 
FieldB int NOT NULL, 
FieldC int NOT NULL
)
create unique nonclustered index ix on  MyTable(FieldA, FieldB, FieldC)

Then paste the below into two different SSMS windows. Take a note of the spids of the connections (x and y) and set up a SQL Profiler Trace capturing locking events and user error messages. Apply filters of spid=x or y and severity = 0 and then execute both scripts.

然后将以下内容粘贴到两个不同的SSMS窗口中。记下连接的spid（x和y）并设置SQL Profiler Trace捕获锁定事件和用户错误消息。应用spid = x或y和severity = 0的过滤器，然后执行这两个脚本。

Insert Script

DECLARE @FieldA INT, @FieldB INT, @FieldC INT
SET NOCOUNT ON
SET CONTEXT_INFO 0x696E736572742074657374

BEGIN TRY
WHILE 1=1
    BEGIN

        SET @FieldA=( (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 24 * 60 * 60 * 300)
        SET @FieldB = @FieldA
        SET @FieldC = @FieldA

        RAISERROR('beginning insert',0,1) WITH NOWAIT
        INSERT INTO MyTable (FieldA, FieldB, FieldC)
        SELECT FieldA=@FieldA, FieldB=@FieldB, FieldC=@FieldC
        WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA=@FieldA AND FieldB=@FieldB AND FieldC=@FieldC ) = 0
    END
END TRY
BEGIN CATCH
    DECLARE @message VARCHAR(500)
    SELECT @message = 'in catch block ' + ERROR_MESSAGE()
    RAISERROR(@message,0,1) WITH NOWAIT
    DECLARE @killspid VARCHAR(10) 
    SELECT @killspid = 'kill ' +CAST(SPID AS VARCHAR(4)) FROM sys.sysprocesses WHERE SPID!=@@SPID AND CONTEXT_INFO = (SELECT CONTEXT_INFO FROM sys.sysprocesses WHERE SPID=@@SPID)
    EXEC ( @killspid )
END CATCH

#3

1  

Off the top of my head, I have a feeling one or more of those columns accepts nulls. I would like to see the create statement for the table including the constraint.

在我的脑海中，我感觉这些列中的一个或多个接受空值。我想看到包含约束的表的create语句。

#1

39  

Why doesn't this work?

为什么这不起作用？

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.

我相信SQL Server的默认行为是在不再需要时释放共享锁。您的子查询将导致表上的短暂共享（S）锁定，该子锁定将在子查询完成后立即释放。

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

此时，没有什么可以防止并发事务插入刚刚验证过的行。

What modification do I need to make so there is no chance of an exception due to the constraint violation?

我需要进行哪些修改才能因为约束违规而无法出现异常？

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提示添加到子查询将指示SQL Server保持锁定，直到事务完成。 （在您的情况下，这是一个隐式事务。）HOLDLOCK提示等同于SERIALIZABLE提示，它本身等同于您在“其他方法”列表中引用的可序列化事务隔离级别。

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.

单独的HOLDLOCK提示足以保留S锁并防止并发事务插入您要防范的行。但是，您可能会发现您的唯一密钥违例错误被死锁替换，发生在同一频率上。

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.

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

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)

幸运的是，这个确切的场景还有另一种锁类型，称为Update（U）锁。 U锁与S锁相同，但有以下区别：虽然可以在同一资源上同时保持多个S锁，但一次只能保持一个U锁。 （换句话说，虽然S锁彼此兼容（即可以共存但没有冲突），U锁彼此不兼容，但可以与S锁共存;并且沿着频谱，独占（X）锁不是兼容S或U锁）

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

您可以使用UPDLOCK提示将子查询上的隐式S锁升级为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.

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

NULL values

NULL值

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

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.

如果启用ANSI_NULLS（默认），则即使在FieldC为NULL的情况下，相等性检查FieldC = NULL也将返回false（当ANSI_NULLS打开时，必须使用IS NULL运算符检查null）。由于FieldC可以为空，因此在插入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.)

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

SQL Server Books Online References

SQL Server联机丛书参考

#2

4  

RE: "I'm still curious as to why the original implementation doesn't work."

RE：“我仍然很好奇为什么最初的实现不起作用。”

Why would it work?

它为什么会起作用？

What is there to prevent two concurrent transactions being interleaved as follows?

有什么可以阻止两个并发事务交错如下？

Tran A                                Tran B
---------------------------------------------
SELECT COUNT(*)...
                                  SELECT COUNT(*)...
INSERT ....
                                  INSERT... (duplicate key violation).

The only time conflicting locks will be taken is at the Insert stage.

锁定锁定的唯一时间是在Insert阶段。

To see this in SQL Profiler

在SQL事件探查器中看到这一点

Create Table Script

create table MyTable
(
FieldA int NOT NULL, 
FieldB int NOT NULL, 
FieldC int NOT NULL
)
create unique nonclustered index ix on  MyTable(FieldA, FieldB, FieldC)

Then paste the below into two different SSMS windows. Take a note of the spids of the connections (x and y) and set up a SQL Profiler Trace capturing locking events and user error messages. Apply filters of spid=x or y and severity = 0 and then execute both scripts.

然后将以下内容粘贴到两个不同的SSMS窗口中。记下连接的spid（x和y）并设置SQL Profiler Trace捕获锁定事件和用户错误消息。应用spid = x或y和severity = 0的过滤器，然后执行这两个脚本。

Insert Script

DECLARE @FieldA INT, @FieldB INT, @FieldC INT
SET NOCOUNT ON
SET CONTEXT_INFO 0x696E736572742074657374

BEGIN TRY
WHILE 1=1
    BEGIN

        SET @FieldA=( (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 24 * 60 * 60 * 300)
        SET @FieldB = @FieldA
        SET @FieldC = @FieldA

        RAISERROR('beginning insert',0,1) WITH NOWAIT
        INSERT INTO MyTable (FieldA, FieldB, FieldC)
        SELECT FieldA=@FieldA, FieldB=@FieldB, FieldC=@FieldC
        WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA=@FieldA AND FieldB=@FieldB AND FieldC=@FieldC ) = 0
    END
END TRY
BEGIN CATCH
    DECLARE @message VARCHAR(500)
    SELECT @message = 'in catch block ' + ERROR_MESSAGE()
    RAISERROR(@message,0,1) WITH NOWAIT
    DECLARE @killspid VARCHAR(10) 
    SELECT @killspid = 'kill ' +CAST(SPID AS VARCHAR(4)) FROM sys.sysprocesses WHERE SPID!=@@SPID AND CONTEXT_INFO = (SELECT CONTEXT_INFO FROM sys.sysprocesses WHERE SPID=@@SPID)
    EXEC ( @killspid )
END CATCH

#3

1  

Off the top of my head, I have a feeling one or more of those columns accepts nulls. I would like to see the create statement for the table including the constraint.

在我的脑海中，我感觉这些列中的一个或多个接受空值。我想看到包含约束的表的create语句。

标签：

相关文章