时间戳

首页 > 解决方案 > 插入并选择给出重复键违规

sql-server - 插入并选择给出重复键违规

问题描述

我有一个存储过程，它实际上是在尝试对表进行 upsert。考虑以下：

create table dbo.myTable (
   id int not null constraint PK_myTable primary key (id),
   payload varchar(100)
);

在存储过程中，我使用用户输入填充了一个与表具有相同结构的临时表，然后尝试以下操作：

insert into dbo.myTable
   (id, payload)
select source.id, source.payload
from #temp as source
left join dbo.myTable as target
   on source.id = target.id
where target.id is null;

当我单独测试它时（即上面的语句，包装在显式事务中；下面的测试工具），运行第二个实例将等待键锁。这是我所期望的。但是，我看到的是偶尔在负载下我会收到重复键错误。这怎么可能？

我知道我有几种方法可以解决它。我可以加上IGNORE_DUP_KEY主键约束。我也可以将插入语句包装在尝试中并吞下错误。而且，老实说，我将探索这些选项。但我想了解第二笔交易如何获得绿灯进行插入。

测试线束

insert into #temp 
   (id, payload) 
values 
   (1, 'test');

begin transaction
go
    insert into dbo.myTable
    (id, payload)
    select source.id, source.id
    from #temp as source
    left join dbo.myTable as target
        on source.id = target.id
    where target.id is null

标签： sql-serverconcurrency

解决方案

If you wish to understand, then you can easily get this error manually. Open SSMS. Create simple table there, for example:

CREATE TABLE test
(
 id int not null primary key
)

Now open the transaction and make an insert:

BEGIN TRANSACTION
insert into test values(1); -- do not commit

Then open new window in SSMS and repeat the same statement there:

insert into test values(1);

Now go back to your first window and commit the transaction. In the second window you'll get:

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__test__3213E83F2818DDD3'. Cannot insert duplicate key in object 'dbo.test'. The duplicate key value is (1).

I think that the most proper way to solve that issue is to use proper isolation levels.

推荐阅读