postgresql - 如果不存在则插入行会导致竞争条件？

Question

我正在使用 python(不太相关)和 Postgresql(9.2，如果相关)实现一个简单的基于 Web 的 RSS 阅读器。数据库架构如下(基于RSS格式):

CREATE TABLE feed_channel
(
    id SERIAL PRIMARY KEY,
    name TEXT,
    link TEXT NOT NULL,
    title TEXT
);
CREATE TABLE feed_content
(
    id SERIAL PRIMARY KEY,
    channel INTEGER REFERENCES feed_channel(id) ON DELETE CASCADE ON UPDATE CASCADE,
    guid TEXT UNIQUE NOT NULL,
    title TEXT,
    link TEXT,
    description TEXT,
    pubdate TIMESTAMP
);

当我创建一个新 channel (并查询更新的提要信息)时，我请求提要，将其数据插入 feed_channel 表，选择新插入的 ID - 或现有的 ID 以避免重复 - 然后将提要数据添加到feed_content 表。一个典型的场景是:

1. 查询提要 url，获取提要标题和所有当前内容
2. 如果不存在，则将提要标题插入 feed_channel...如果已经存在，则获取现有 ID
3. 对于每个 feed 项目，将引用存储的 channel ID 插入到 feed_content 表中

这是一个标准的“如果不存在则插入，但返回相关 ID”的问题。为了解决这个问题，我实现了以下存储过程:

CREATE OR REPLACE FUNCTION channel_insert(
  p_link feed_channel.link%TYPE,
  p_title feed_channel.title%TYPE
) RETURNS feed_channel.id%TYPE AS $$
  DECLARE
    v_id feed_channel.id%TYPE;
  BEGIN
    SELECT id
    INTO v_id
    FROM feed_channel
    WHERE link=p_link AND title=p_title
    LIMIT 1;

    IF v_id IS NULL THEN
      INSERT INTO feed_channel(name,link,title)
      VALUES (DEFAULT,p_link,p_title)
      RETURNING id INTO v_id;
    END IF;

    RETURN v_id;

  END;
$$ LANGUAGE plpgsql;

这称为“select channel_insert(link, ti​​tle);”从我的应用程序插入如果不存在，然后返回相关行的 ID，无论它是插入的还是刚刚找到的(上面列表中的第 2 步)。

效果很好!

但是，我最近开始想知道如果使用相同的参数同时执行两次此过程会发生什么情况。让我们假设以下情况:

1. 用户 1 尝试添加新 channel 并因此执行 channel_insert
2. 几毫秒后，用户 2 尝试添加相同的 channel 并执行 channel_insert
3. 用户 1 对现有行的检查已完成，但在插入完成之前，用户 2 的检查已完成并表示不存在现有行。

这会是 PostgreSQL 中的潜在竞争条件吗？解决此问题以避免此类情况的最佳方法是什么？是否可以使整个存储过程原子化，即同一时间只能执行一次？

我尝试过的一个选项是使字段成为唯一，然后首先尝试插入，如果有异常，则选择现有的...这行得通，但是，每次尝试时 SERIAL 字段都会增加，留下很多空白在序列中。我不知道从长远来看这是否会成为问题(可能不会)，但有点烦人。也许这是首选解决方案？

感谢任何反馈。这种 PostgreSQL 魔法水平超出了我的能力范围，因此我们将不胜感激任何反馈。

Answer 1

Will this be a potential race condition in PostgreSQL?

是的，事实上它可以在任何数据库引擎中。

What is the best way to solve this problem to avoid such scenarios?

这是一个有难度的问题，需要对多个用户使用数据库的情况有深入的了解。但是，我会给你一些选择。简而言之，您唯一的选择是在此过程中LOCK 表，但是如何锁定该表将取决于 如何全天使用数据库。

让我们从基本的 LOCK 开始:

LOCK TABLE feed_channel

这将使用 ACCESS EXCLUSIVE 锁定选项锁定表。

Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way.

现在，这是可用的限制性最强的锁，肯定会解决竞争条件，但可能不是您想要的。这是你必须决定的事情。因此，虽然它清除，但您将不得不LOCK 表，它不是清除 如何。

你还有什么要决定的？

1. 你想怎样 LOCK the table ？研究该链接上的锁定选项以做出决定。
2. 您要在何处 LOCK 表？或者换句话说，您想在函数的顶部 LOCK(我认为您会根据可能的竞争条件这样做) ，还是您只是想在 INSERT 之前 LOCK？

Is it possible to make the entire stored procedure atomically, i.e. that it can only be executed once at the same time?

不，任何连接到数据库的人都可以执行代码。

---

我希望这对您有所帮助。