sql-server - 为什么此查询会生成 PK 违规错误？

问题描述

因此，我尝试在单个查询中仅插入不存在的行。

我的查询如下：

INSERT INTO [dbo].[users_roles] ([user_id], [role_id]) 
SELECT 29851, 1 WHERE NOT EXISTS (
  SELECT 1 FROM [dbo].[users_roles] WHERE user_id = 29851 AND role_id = 1)

有时（很少，但仍然），它会产生以下错误：

违反 PRIMARY KEY 约束“PK_USERS_ROLES”。无法在对象“dbo.users_roles”中插入重复键。重复键值为 (29851, 1)。

PK_USERS_ROLES是[user_id], [role_id]。这是表模式的完整 SQL：

create table users_roles
(
    user_id int not null
        constraint FK_USERS_ROLES_USER
        references user,
    role_id int not null
        constraint FK_USERS_ROLES_USER_ROLE
        references user_role,
    constraint PK_USERS_ROLES
    primary key (user_id, role_id)
)

上下文：

这是由托管在 Apache 服务器上的 PHP 脚本执行的，并且“随机”发生数百次（很可能与并发相关）。

更多信息：

SELECT @@VERSION给出：

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack)

SQL Server 版本：SQL Server 2008 R2
事务隔离级别：ReadCommitted
这是在显式事务中执行的（通过 PHP 语句，但我认为最终结果是相同的）

问题：

有人可以解释为什么/如何发生这种情况吗？
什么是一次性安全插入（换句话说，在单个查询中）的有效方法？我见过其他答案，例如这个答案，但这些解决方案适用于存储过程。

谢谢。

标签： sql-serversql-server-2008

明确这一点可能会有所帮助。下面在显式事务中运行它，显式锁定行。

DECLARE @user_id INT; SET @user_id=29851;
DECLARE @role_id INT; SET @role_id=1;

BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @exists INT;
    SELECT @exists=1 
    FROM [dbo].[users_roles] WITH(ROWLOCK,HOLDLOCK,XLOCK)
    WHERE user_id=@user_id AND role_id=@role_id;

    IF @exists IS NULL
    BEGIN
        INSERT INTO [dbo].[users_roles] ([user_id], [role_id])
        VALUES(@user_id,@role_id);
    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

sql-server - 为什么此查询会生成 PK 违规错误？

问题描述

解决方案

推荐阅读