在竞争条件中不存在的地方，在插入-选择上发布任何重复的机会

Question

所以，如果我用下面的查询插入，是否有可能同时运行两个查询，都没有看到id为123的员工，并且都插入了行？

insert into employee (id, name)
select 123,'joe' where not exists (select 1 from employee where id=123)

现在我不能对这个表进行约束。或者我可以用where来做一个可能的约束，但我宁愿避免这样做，因为我认为这可能会影响性能。如果这能保证在比赛条件下我不会得到两排，我很乐意这样做。我也不想锁定整个表，因为那里会经常插入和更新。

Answer 1

你猜对了，有一个竞赛条件。

• 创建一个约束=在将记录提交到表之前，DB服务器评估id=123不存在。(1台服务器=>无竞争条件)
• 使用WHERE≈每个客户端，独立，在将记录插入表之前检查id=123是否不存在。(多个客户端同时执行，请记住，客户端只能在语句开始=>race条件之前看到记录)

此外，请记住，在事务的上下文中，根据隔离级别的不同，客户端可能需要等待很长时间才能看到其他人所做的更改。

测试它没有你想象的那么复杂。尝试从同一台计算机连接两个客户端。

第一个客户端：

create table ForceWait (val text);
with LetsWait as (
insert into ForceWait select null from pg_sleep(10)
) /*We want to make sure waiting and inserting are done in separate steps*/
insert into employee (id, name)
select 123,'joe' where not exists (select 1 from employee where id=123)

第二个客户端，立即执行：

insert into employee (id, name)
select 123,'jack' where not exists (select 1 from employee where id=123);
select * from Employee where id = 123

结果：第一个查询首先开始，但最后结束
在一段时间内，您将在表中看到jack，但仍将插入joe。这是你的比赛情况。

如果像您所说的那样，您不断在表中插入/更新，那么WHERE子句将很快成为与我上面的pg_sleep(...)等价的子句。

---

PS:你可能认为第一个客户端本可以执行更简单的东西：

insert into employee (id, name)
select 123,'joe' from pg_sleep(10) where not exists (select 1 from employee where id=123)

然而，postgresql(至少版本11)能够在相关的情况下优化整个过程(尝试查询，当id=123已经存在时，您会发现它不会等待)
这意味着优化器会产生副作用，出于测试目的，我们希望避免这种副作用。

PPS：不要忘记删除表ForceWait