在删除SQL Server之前正在执行Insert语句

Question

在我的存储过程中有一个如下语句：

IF (  
SELECT COUNT(1)  
FROM dbo.t_user_suggested
WHERE ListId = @pListId  
) > 0  
BEGIN  
DELETE  
FROM dbo.t_user_suggested
WHERE ListId = @pListId  
END
INSERT INTO dbo.t_user_suggested (  
Id  
,InId  
,InName  
,UserId  
,ListId
)  
SELECT DISTINCT Id  
,InId  
,InName  
,@pUserId  
,@pListId
FROM CTE123  

我收到违反PRIMARY KEY约束"PK_t_user_suggested"的错误。无法在对象"dbo.t_user_suggested"中插入重复的密钥。在插入之前，我有一个明确的检查，要删除所有记录，而此错误是随机出现的。我手动执行了10-15次存储过程，没有出现任何错误。如何确保在控件移动到存储过程中的Insert之前删除所有记录。

Answer 1

如果两个事务同时运行此代码，它们都可以尝试插入相同的键。在默认的锁定/隔离级别下，没有什么可以阻止它们，而且您似乎无论如何都不会使用事务。

您最好的选择是使用具有正确提示的交易。

顺便说一句，不需要IF (SELECT COUNT(1)...，因为DELETE只会删除现有的行。此外，如果你真的需要这个，那么你应该使用IF(EXISTS而不是

SET XACT_ABORT, NOCOUNT ON;
BEGIN TRAN;
DELETE
FROM dbo.t_user_suggested WITH (HOLDLOCK)
WHERE ListId = @pListId;

INSERT INTO dbo.t_user_suggested (  
Id  
,InId  
,InName  
,UserId  
,ListId
)  
SELECT DISTINCT Id  
,InId  
,InName  
,@pUserId  
,@pListId
FROM CTE123;
COMMIT TRAN;

或者，如果@pListId实际上是主键，那么只发布UPDATE要好得多

UPDATE 
SET Id = CTE123.InId
,InId  = CTE123.InId  
,InName = CTE123.InName 
,UserId = CTE123.UserId 
FROM dbo.t_user_suggested t
JOIN (
SELECT DISTINCT Id  
,InId  
,InName  
,@pUserId AS pUserId
,@pListId AS pListId
FROM CTE123
) CTE123 ON CTE123.pListId = t.pListId
WHERE t.ListId = @pListId;

Answer 2

我怀疑下面的查询返回了多行。所以每一行都得到相同的ListID。

SELECT DISTINCT Id  
,InId  
,InName  
,@pUserId  
,@pListId
FROM CTE123  

请尝试执行以下查询：

IF (  
SELECT COUNT(1)  
FROM dbo.t_user_suggested
WHERE ListId = @pListId  
) > 0  
BEGIN  
DELETE  
FROM dbo.t_user_suggested
WHERE ListId = @pListId  
END
INSERT INTO dbo.t_user_suggested (  
Id  
,InId  
,InName  
,UserId  
,ListId
)  
SELECT top 1 DISTINCT Id  
,InId  
,InName  
,@pUserId  
,@pListId
FROM CTE123  

我已经尝试创建您的情况，您的查询运行良好。您可以查看下面的示例。ListId不是主键列，或者选择查询返回多行。

DB Fiddle:

表定义和插入语句：

create table t_user_suggested (Id  int ,InId  int,InName  varchar(50),UserId  INT,ListId INT);
INSERT INTO t_user_suggested values(1,1,'A',2,3);

选择查询：

select* from t_user_suggested

表中的电流输出：

>>

Id	InId	Init	UserId	ListId
1

我不确定到底是什么问题，但似乎是一个竞争条件或事务锁定/隔离级别的问题。我暂时使用了MERGE语句而不是插入和删除来解决这个问题。下面是我的问题。

MERGE t_user_suggested AS tar
USING CTE123  AS cte
ON tar.Id = cte.Id 
AND tar.InId  = cte.InId  
AND tar.ListId = @pListId
WHEN NOT MATCHED BY Target
THEN
INSERT (
Id 
,InId  
,InName
,UserId
,ListId
)
VALUES (
cte.Id
,cte.IngrId
,cte.InName
,@pUserId
,@pListId
)
-- For Updates
WHEN MATCHED THEN UPDATE SET
tar.IName       = cte.InName,
tar.datetimelastseen = null,
tar.seenlast24hours=null
-- For Deletes
WHEN NOT MATCHED BY SOURCE THEN
DELETE;