为什么此查询会产生PK违规错误？

Question

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

我的疑问如下：

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)

有时（非常罕见，但仍然如此），它会生成以下错误：

违反主键约束“主键用户角色”。无法在对象“dbo”中插入重复键。用户的角色。重复的键值为（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提供：

微软SQL Server 2008 R2（SP2）-10.50。4000.0（X64）2012年6月28日08:36:30版权所有（c）Windows NT 6.1上的Microsoft Corporation企业版（64位）（构建7601:Service Pack）

>

SQL Server版本：SQL Server 2008 R2

事务隔离级别：ReadCommitted

这是在显式事务中执行的（通过PHP语句，但我认为最终结果是相同的）

问题：

>

有人能解释为什么/怎么会这样吗？

什么是一次安全插入（换句话说，在单个查询中）的有效方法？我见过其他类似的答案，但这些解决方案是针对存储过程的。

谢谢

Answer 1

这个表是被截断了还是行被删除了？多久一次？对我来说，在某一时刻不应该找到行是有意义的，因为您正在运行“如果不存在则插入”，此时两个或多个查询可能会命中数据库以插入相同的数据...只有一个会...另一个应该如果行是在其"不存在"查找之前插入的，则不执行任何操作；如果行是在查找之后插入的，则不执行任何操作。

我现在只有一个Oracle数据库来做一些测试，我可以重现这个问题。我的提交模式是显式的：

>

创建空表，唯一约束和授予选择，插入到另一个用户：

CREATE TABLE just_a_test (val NUMBER(3,0));

ALTER TABLE just_a_test ADD CONSTRAINT foobar UNIQUE (val);

GRANT SELECT, INSERT ON just_a_test TO user2;

user1上的DB会话：

INSERT INTO just_a_test 
SELECT 10 
FROM DUAL 
WHERE NOT EXISTS 
(
  SELECT 1 
  FROM just_a_test 
  WHERE val = 10
)
;

-- no commit yet...

user2上的DB会话：

INSERT INTO user1.just_a_test 
SELECT 10 
FROM DUAL 
WHERE NOT EXISTS 
(
  SELECT 1 
  FROM user1.just_a_test 
  WHERE val = 10
)
;


-- no commit yet, the db just hangs til the other session commit...

因此，我提交第一个事务，插入行，然后在user2会话上出现以下错误：

"unique constraint violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.

现在我回滚第二个事务，并在user2上再次运行相同的插入，现在我得到以下输出：

0 rows inserted.

可能你的情况就是这样。希望能有帮助。

编辑

我很抱歉。你问了两个问题，我只回答了，有人能解释为什么会发生这种情况吗 一。所以我错过了什么是一次性安全插入的有效方法（换句话说，在单个查询中） 。

对你来说，“安全”到底意味着什么？假设您正在运行一个包含大量行的INSERT/SELECT，与存储的行相比，其中只有一行是重复的。为了您的“安全”级别，您应该忽略所有插入的行，还是只忽略重复的行，存储其他行？

再说一次，我现在还没有SQL Server可以尝试一下，但是看起来您可以告诉SQL Server，在任何dup的情况下，是拒绝插入的所有行，还是只拒绝dup，保留其他行。这同样适用于插入单行。。。如果是dup，抛出一个错误。。。或者干脆从另一方面忽略它。

语法应如下所示，以便仅忽略dup行而不抛出错误：

    ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON);

默认情况下，此选项处于禁用状态，这意味着SQL Server会抛出错误并丢弃正在插入的非dup行。

通过这种方式，您可以保留INSERT/SELECT语法，这在我看来很好。

希望能有帮助。

资料来源：

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-index-option-transact-sql?view=sql-server-2008

https://stackoverflow.com/a/11207687/1977836

Answer 2

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

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