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

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

我想在一个表中INSERT或-SELECT一行，然后在第二个表中插入行时引用它。由于RETURNING不能很好地与ON CONFLICT配合使用，到目前为止，我使用了一个简单的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]
)

然而，我了解到，这种CTE在并发写负载下并不完全安全，当不同的事务插入同一行时，可能会出现upstart和select都为空的情况。

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

启动一个新命令(在同一事务中)，然后可以看到上一个查询中的这些冲突行。

如果我理解正确，这意味着要进行

$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]
)

我相信这足以防止这种特殊的竞争条件(在这个答案中被称为"并发问题1")，但我不能100%确定不会错过任何东西。

还有关于"；并发问题2"；？如果我理解正确的话，这是关于在INSERT和SELECT语句之间删除或更新现有行的另一个事务，并且在使用多个查询而不是CTE方法时更可能发生这种情况。我到底该怎么处理？我认为在第二个代码片段中，用FOR KEY SHARE锁定SELECT是必要的，但在同一查询中使用id的第三个片段中，我是否也需要这样做？如果这有助于简化答案，那么我们假设object只能插入或删除，但永远不会更新。

为了确保第一个表中的单行在那里，并且返回了它的ID，您可以创建一个如下所示的函数：

函数中的SELECT或INSERT是否容易出现竞争条件

要确保行在事务期间也保持在那里，只需确保它被锁定即可。如果您INSERT该行，它无论如何都会被锁定。如果您SELECT是一个现有的id，则必须显式锁定它——就像您建议的那样。只要(scope, name)上有一个(非部分、非函数)UNIQUE索引，FOR KEY SHARE就足以满足我们的目的，考虑到您的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$;

如果可以想象并发事务可能在SELECT和下一个INSERT语句之间的微小时间帧内DELETE(而不是UPDATE)，那么您实际上只需要锁定该行。

此外，如果您有一个从object_member.object_id到object.id的FOREIGN KEY约束(这似乎很可能)，那么引用完整性无论如何都是有保证的。如果不添加显式锁，并且在其间删除了行，则会出现外键冲突，INSERT到object_member以及整个事务都会被取消。否则，具有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中，以避免重复执行——这将有效，但代价毫无意义。

在Postgres12或更高版本中，我会通过添加MATERIALIZED来明确这一点(因为INSERT隐藏在函数中)：

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

旁白：对于SELECT列表中的多个unnest()，请确保您在Postgres 10或更高版本上。参见：

SELECT子句中多个集合返回函数的预期行为是什么

细节事项

在同一事务中有多个查询的应用程序逻辑中这样做会有什么不同吗(除了执行时间)？

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

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

我真的想知道我的第二个片段是否安全，或者为什么不安全(在INSERT之后引用了关于SELECT中可见性的引用)。

大部分是安全的，但不是绝对的。虽然下一个单独的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!

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

细节事项

相关内容

最新更新

热门标签：