sql - INSERT ON CONFLICT DO NOTHING 和 SELECT 之间的竞争条件

Question

SELECT 查询在 INSERT … ON CONFLICT DO NOTHING 语句之后是否总能找到一行，给定默认事务隔离(已提交读取)？

我想在一个表中INSERT-or-SELECT 一行，然后在第二个表中插入行时引用它。自 RETURNING doesn't work well with ON CONFLICT ，到目前为止我已经使用了a simple CTE即使该行已经存在，它也应该始终给我标识列值:

$id = query(
  `WITH ins AS (
    INSERT INTO object (scope, name)
    VALUES ($1, $2)
    ON CONFLICT (scope, name) DO NOTHING
    RETURNING id
  )
  SELECT id FROM ins
  UNION  ALL
  SELECT id FROM object WHERE scope = $1 AND name = $2
  LIMIT 1;`,
  [$scope, $name]
)
query(
  `INSERT INTO object_member (object_id, key, value)
  SELECT $1, UNNEST($2::text[]), UNNEST($3::int[]);`
  [$id, $keys, $values]
)

但是，我了解到 this CTE is not entirely safe under concurrent write load ，当不同的事务确实插入同一行时，upsert 和 select 都可能出现空的情况。

在那里的答案(以及 here)建议使用另一个查询来执行 SELECT:

start a new command (in the same transaction), which then can see these conflicting rows from the previous query.

如果我没理解错的话，应该是做

$id = query(
  `INSERT INTO object (scope, name)
  VALUES ($1, $2)
  ON CONFLICT (scope, name) DO NOTHING
  RETURNING id;`,
  [$scope, $name]
)
if not $id:
  $id = query(
    `SELECT id FROM object WHERE scope = $1 AND name = $2;`
    [$scope, $name]
  )
query(
  `INSERT INTO object_member (object_id, key, value)
  SELECT $1, UNNEST($2::text[]), UNNEST($3::int[]);`
  [$id, $keys, $values]
)

甚至缩短为

query(
  `INSERT INTO object (scope, name)
  VALUES ($1, $2)
  ON CONFLICT (scope, name) DO NOTHING;`,
  [$scope, $name]
)
query(
  `INSERT INTO object_member (object_id, key, value)
  SELECT (SELECT id FROM object WHERE scope = $1 AND name = $2), UNNEST($3::text[]), UNNEST($3::int[]);`
  [$scope, $name, $keys, $values]
)

我相信这足以防止特定的竞争条件(在 this answer 中称为“并发问题 1”)——但我不能 100% 确定没有遗漏任何东西。

“并发问题 2”又如何呢？如果我理解正确，这是关于另一个事务删除或更新现有行，在 INSERT 和 SELECT 语句之间 - 使用多个查询而不是使用多个查询时更有可能发生CTE 方法。我应该如何处理呢？我假设在第二个代码片段中需要使用 FOR KEY SHARE 锁定 SELECT - 但我是否也需要在 id 的第三个片段中使用它> 在同一查询中使用？如果有助于简化答案，我们假设一个 object 只能插入或删除，但永远不会更新。

Answer 1

要绝对确保第一个表中的单行存在并返回它的 ID，您可以创建如下所示的函数:

• Is SELECT or INSERT in a function prone to race conditions?

要确保该行在交易期间也保持在那里，只需确保它已锁定。如果您 INSERT 该行，它无论如何都会被锁定。如果您SELECT 一个现有的id，您必须明确地锁定它——就像您建议的那样。 FOR KEY SHARE只要在 (scope, name) 上有一个(非部分的、非功能性的)UNIQUE 索引就足以满足我们的目的，可以安全地假设给定你的 ON CONFLICT 子句。

CREATE OR REPLACE FUNCTION f_object_id(_scope text, _name text, OUT _object_id int)
  LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
   SELECT id FROM object
   WHERE  scope = $1
   AND    name  = $2
   -- lock to prevent deletion in the tiny time frame before the next INSERT
   FOR    KEY SHARE
   INTO   _object_id;

   EXIT WHEN FOUND;

   INSERT INTO object AS o (scope, name)
   VALUES ($1, $2)
   ON     CONFLICT (scope, name) DO NOTHING
   RETURNING o.id
   INTO   _object_id;

   EXIT WHEN FOUND;
END LOOP;
END
$func$;

如果可以想象并发事务可能会在DELETE它(你不需要UPDATE)在SELECT 和下一个 INSERT 语句。

此外，如果您有从 object_member.object_id 到 object.id 的 FOREIGN KEY 约束(这似乎很可能)，则参照完整性是无论如何保证。如果您不添加显式锁，并且在中间删除行，则会违反外键，并且 object_member 的 INSERT 被取消，连同整个交易。否则，具有 DELETE 的其他事务必须等到您的事务完成，然后被相同的 FK 约束取消，因为依赖行现在在那里(除非它被定义为 CASCADE ...) 因此，通过锁定(或不锁定)，您可以决定在这种情况下是阻止 DELETE 还是 INSERT。

然后你的电话就变成了:

query(
  `WITH o(id) AS (SELECT f_object_id($1, $2))
   INSERT INTO object_member (object_id, key, value)
   SELECT o.id, UNNEST($3::text[]), UNNEST($4::int[])
   FROM   o;`
  [$scope, $name, $keys, $values]
)

由于您显然将多行插入到 object_member 中，我将 f_object_id($1, $2) 移动到 CTE 以避免重复执行 - 这将 有效，但毫无意义的昂贵。

在 Postgres 12 或更高版本中，我会 make that explicit by adding MATERIALIZED (因为 INSERT 隐藏在函数中):

WITH o(id) AS MATERIALIZED (SELECT f_object_id($1, $2)) ...

旁白:对于 SELECT 列表中的多个 unnest()，请确保您使用的是 Postgres 10 或更高版本。见:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

细节问题

Will it make any difference (apart from execution time) to do this in the application logic with multiple queries in the same transaction?

基本上没有。唯一的区别是性能。嗯，短代码和可靠性。对于每个循环，在 db 和 client 之间来回切换客观上更容易出错。但除非你有极具竞争力的交易，否则你几乎永远不会循环。

另一个考虑是:这件事很棘手，大多数开发人员都不了解。封装在服务器端功能中，它不太可能被下一个应用程序程序员(或您自己)破坏。您还必须确保它也被实际使用。无论哪种方式，请正确记录您以某种方式这样做的原因......

I really wonder whether my second snippet is safe, or why not (given the quote about visibility in the SELECT after the INSERT).

大部分安全，但并非绝对安全。虽然下一个单独的 SELECT 将看到(现在已提交)与之前的 UPSERT 竞争的事务行，但没有什么可以阻止第三个事务在此期间再次删除它。该行尚未被锁定，当它不可见时您无法执行此操作，并且 Postgres 中没有可用的通用谓词锁定。

考虑一下(T1、T2、T3 是并发事务):

                               T2: BEGIN transaction
T1: BEGIN transaction
                               T2: INSERT object 666
T1: UPSERT object 666
    unique violation?
    -> wait for T2
                               T2: COMMIT
T1: unique violation -> NO ACTION
    finish statement
    can't return invisible object 666
                                             T3: DELETE object 666 & COMMIT
T1: SELECT object 666 -> no row!
    BOOM!

通常情况下，这种情况极不可能发生。
但这是可能的。因此循环。

另一个选项是SERIALIZABLE transaction isolation .通常更昂贵，并且您需要为序列化失败做好准备。第 22 条。