违反SQL Server 2005上INSERT WHERE COUNT(*) =0上的唯一键约束
我从多个进程插入到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个字段的组合应该是唯一的。
我的问题：
- 为什么此操作不起作用？
- 我需要进行哪些修改才能避免因违反约束而出现异常？
请注意，我知道还有其他方法可以解决“如果不存在则插入”的原始问题，例如(总结)：
使用TRY CATCH INSERT 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, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)编辑2决定：
更新一下--我决定使用链接问题(link)中建议的"JFDI“实现。尽管我仍然很好奇为什么原来的实现不能工作。
回答 3
Stack Overflow用户
发布于 2010-09-25 06:35:37
为什么这个不起作用？
我相信SQL Server的默认行为是在不再需要共享锁时立即释放它们。您的子查询将导致表上的一个短暂的共享(S)锁，该锁将在子查询完成后立即释放。
在这一点上，没有什么可以阻止并发事务插入您刚刚验证不存在的行。
我需要做什么修改才能不会因为违反约束而出现异常？
将HOLDLOCK提示添加到子查询将指示SQL Server持有该锁，直到事务完成。(在您的示例中，这是一个隐式事务。)HOLDLOCK提示等同于SERIALIZABLE提示，它本身等同于您在“其他方法”列表中提到的可序列化事务隔离级别。
仅HOLDLOCK提示就足以保留S锁，并防止并发事务插入您要防止的行。但是，您很可能会发现唯一的键冲突错误被死锁取代，并以相同的频率发生。
如果只保留表上的S锁，请考虑两次并发尝试之间的竞争，以锁步方式进行插入同一行--两次尝试都成功地获取了表上的S锁，但都不能成功获取执行插入所需的独占(X)锁。
幸运的是，对于这种情况，还有另一种锁类型，称为Update (U)锁。U锁与S锁相同，但有以下区别:在同一资源上可以同时持有多个S锁，但一次只能持有一个U锁。(换句话说，虽然S锁彼此兼容(即可以无冲突地共存)，但U锁彼此不兼容，但可以与S锁共存；沿着频谱，排它(X)锁与S锁或U锁都不兼容)
您可以使用UPDLOCK提示将子查询上的隐式S锁升级为U锁。
在表中插入同一行的两次并发尝试现在将在初始select语句中序列化，因为这将获取(并持有)一个U锁，该锁与来自并发插入尝试的另一个U锁不兼容。
空值
由于FieldC允许空值的事实，可能会出现另一个问题。
如果ANSI_NULLS为on (默认值)，则相等检查FieldC=NULL将返回false，即使在FieldC为NULL的情况下也是如此(当ANSI_NULLS为on时，必须使用IS NULL运算符检查null )。由于FieldC可以为空，因此在插入空值时将不会执行重复检查。
要正确处理空值，您需要修改Existes子查询，以便在插入空值时使用IS NULL操作符，而不是=。(或者，您可以更改该表，以禁止所有相关列中的空值。)
SQL Server联机丛书参考
Stack Overflow用户
发布于 2010-09-25 04:32:23
RE：“我仍然很好奇为什么原来的实现不能工作。”
为什么它会起作用？
如何防止两个并发事务交错，如下所示？
Tran A Tran B
---------------------------------------------
SELECT COUNT(*)...
SELECT COUNT(*)...
INSERT ....
INSERT... (duplicate key violation).冲突锁的唯一时间是在Insert阶段。
要在SQL事件探查器中查看此信息，请执行以下操作
创建表脚本
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)然后将下面的内容粘贴到两个不同的SSMS窗口中。记下连接(x和y)的spid，并设置SQL Profiler跟踪来捕获锁定事件和用户错误消息。应用spid=x或y且严重性=0的筛选器，然后执行这两个脚本。
插入脚本
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 CATCHStack Overflow用户
发布于 2010-09-25 01:13:00
不经意间，我有一种感觉，这些列中有一个或多个接受空值。我希望看到包含约束的表的create语句。
https://stackoverflow.com/questions/3789287
复制相似问题
腾讯云开发者
