当并发插入可能造成冲突时，如何在postgres中执行条件插入？

Question

我正在尝试编写一个实验框架，用户可以根据、位置ids、和time来安排一些实验。

我的表模式如下所示：

TABLE experiment (
    id INT NOT NULL PRIMARY KEY,
    name varchar(20) NOT NULL,
    locationIds varchar[] NOT NULL,
    timeStart timestamp NOT NULL,
    timeEnd timestamp NOT NULL,
    createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

有插入操作要执行，但条件是位置和时间不应重叠。我想知道在位置或时间重叠的情况下，当两个并发插入被占用时，如何避免数据状态的一致性，

理想情况下，我希望一个插入成功，但我很好，如果两者都失败，应用程序应该再次尝试。

很少有人走近我想：

方法：

方法-1

enable=FALSE

• Then

• 有一个enable列，该列告诉某些条目是否有效。

• i用Insert.

• IF插入实验计划条目，我检查是否还有其他已启用并与当前的Insert.

• IF重叠的条目，这样我就什么都不做了，实验也没有安排。我将条目更新为enable=TRUE.

问题:如果存在并发冲突的插入，则当两个步骤都清除步骤-3时，两者都将得到enable=TRUE。

我考虑了一下，如果我让事务隔离级别未提交，那么我也无法区分正在处理的和已经在进行中的事务隔离级别( enable=TRUE )。

然后我想，如果我标记为enum IN_PROGRESS，启用，禁用，那么方法将如下所示。

方法-2

enable=IN_PROGRESS

• Then

• 有一个enable列，该列告诉某些条目是否是IN_PROGRESS、已启用、禁用的

• I使用enable=IN_PROGRESS插入实验计划条目--我检查是否还有其他条目是enable=ENABLED或enable=IN_PROGRESS，并且与当前的Insert.

• IF重叠--然后更新enable=DISABLED，并且没有计划进行该实验。我将条目更新为enable=ENABLED.

问题:如果存在并发冲突的插入，那么当两个步骤都清除了步骤-3并得到这样的重叠条目时，两者都会得到enable=DISABLED。

如果事务隔离级别是READ-COMMITTED，那么只有当每个步骤都是事务时才能工作，而整个流程是一个事务。如果事务隔离级别是READ-UNCOMMITTED，那么这可以作为一个事务处理，禁用状态也可以作为回滚步骤。

方法-3

使用基于触发器的解决方案，就像我使用POSTGRES一样，我可以为每个insert操作添加一个触发器，在这里我检查这样的重叠条目，如果没有，我会更新该行以使enable=TRUE

CREATE OR REPLACE FUNCTION enable_if_unique() 
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
    UPDATE experiment
    SET NEW.enable=true
    WHERE (SELECT count(1)
           FROM experiment
           WHERE enable= true AND location_Ids && OLD.location_ids AND (OLD.timeStart, OLD.timeEnd) OVERLAPS (timeStart, timeEnd)
        ) = 0;
    RETURN NEW;
    END IF;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER enable_if_unique_trigger BEFORE INSERT ON experiment FOR EACH ROW EXECUTE PROCEDURE enable_if_unique();

对于方法3，我不太确定，因为我觉得每次插入操作都需要以串行方式执行触发器，以便在禁用其他重叠操作时，实际启用其中一个实验。

方法-4

从在线搜索其他可能的解决方案，我看到插入使用选择语句和WHERE子句帮助添加所需的条件。

INSERT INTO experiment(id, name, locationIds, timeStart, timeEnd) 
SELECT 1, 'exp-1', ARRAY[123,234,345], '2020-03-13 12:00:00' 
WHERE (
       SELECT count(1) 
       FROM EXPERIMENT 
       WHERE enable= true 
             AND 
             location_Ids && OLD.location_ids 
             AND 
             (OLD.timeStart, OLD.timeEnd) OVERLAPS (timeStart, timeEnd)
      ) = 0;

我觉得仍然有可能出现一致性问题，因为两个并发操作都无法在SELECT语句中读取每个操作，以检查约束。

最终方法:方法-2

我想知道以下几点：

在可伸缩性和高吞吐量方面，maintained?

• Any

• 是最好的方法？

• ，哪种方法实际上确保数据一致性是，其他我在这里可以使用和忽略的方法！！

新手到POSTGRES，将欣赏示例或链接

Answer 1

正如@a_horse_with_no_name所提到的

我们可以使用排除约束：

-- this prevents overlaps in the locationids AND the time range
alter table experiment 
  add constraint no_overlap 
  exclude using gist (locationids with &&, tsrange(timestart, timeend) with &&);