🏁🐘使用 PostgreSQL 赢得竞态条件

案例 2：复合独特性

案例 3：复合多重唯一性

案例 4：复合参照唯一性

竞态条件真是太糟糕了！它们极难调试，而且通常只会在生产环境中最关键的时刻（流量高峰期）自然发生。幸运的是，许多数据库（例如 PostgreSQL）都提供了强大的工具来管理和避免竞态条件。

本文将深入探讨如何在使用 PostgreSQL 时避免竞态条件，尤其侧重于强制执行唯一性约束。我们将从一些简单的案例入手，然后逐步深入到更复杂的案例，您可以根据自己的经验水平选择合适的章节。

本文假设您熟悉竞态条件，了解其成因和弊端。（如果您不熟悉，别担心，您很快就会明白的！我们都经历过。）本文后面的案例也假设您至少具备关系型数据库的中级知识。

本文将使用Node.js pg库进行示例演示。

让我们直接进入正题，看看我们的代码和数据容易受到竞态条件影响的一些不同情况，并找出修复它们的方法。

案例 1：简单唯一性

大多数应用程序不允许两个用户共用一个电子邮件地址。如果用户尝试使用已被其他帐户注册的电子邮件地址进行注册，则注册应失败。

可以用 JavaScript 编写这样的唯一性检查：

async function registerUser(email, hashedPassword) {
  const existingUserResult = await pool.query(
    'SELECT 1 FROM "user" WHERE email = ?;',
    [email],
  );

  if (existingUserResult.rows.length > 0) {
    throw new Error('User already exists');
  }

  await pool.query(
    'INSERT INTO "user" VALUES (?, ?);',
    [email, hashedPassword],
  );
}

但这段代码容易出现竞态条件。如果两个用户同时使用同一个邮箱注册，就可能出现如下情况：

用户 #1 选择操作
用户 #2 选择操作
用户 #1 插入操作
用户 #2 插入操作

由于SELECT操作在两个用户之间先后发生INSERT，因此两个用户都能通过重复检查并继续执行操作。异步编程INSERT的特性允许这种事件序列的发生。

幸运的是，这个问题很容易解决，我们只需要在表中的列UNIQUE上添加一个约束即可。我们可以在创建表时完成此操作：emailuser

CREATE TABLE "user" (
  "email" VARCHAR UNIQUE NOT NULL,
  "hashedPassword" NOT NULL
);

或者稍后，通过使用ALTER TABLE。

通过UNIQUE对该email列设置约束，我们告诉数据库任何两行都不能具有相同的该值email，数据库会自动执行此操作。即使两个INSERT操作同时发生，数据库的并发特性也能保证只有一个操作成功，另一个操作失败。

案例 2：复合独特性

假设我们正在运行一个多用户博客应用程序，例如 Dev.to，并且我们希望允许用户每周创建一篇精选文章。我们的posts表格可能如下所示：

CREATE TABLE "posts" (
  "userId" INT NOT NULL,
  "createdAt" TIMESTAMP WITHOUT TIMEZONE NOT NULL,
  "highlighted" BOOLEAN NOT NULL,
  "postContent" TEXT NOT NULL
);

我们可以编写类似于第一个例子的代码，并添加如下的唯一性检查：

const existingWeeklyHighlightedPostResult = await pool.query(
  `
    SELECT 1
    FROM "posts"
    WHERE
      "userId" = ?
      AND
      "highlighted" IS TRUE
      AND
      /* "createdAt" is greater than the first moment
       * of the current week */
      "createdAt" >= DATE_TRUNC('week', NOW());
  `,
  [userId],
);

if (existingWeeklyHighlightedPostResult.rows.length > 0) {
  throw new Error('You already have a highlighted post this week');
}

然而，这样做也存在同样的问题。如果用户同时提交两个精选帖子，这两个帖子都可能通过唯一性检查。也许用户不小心双击了提交按钮，或者他们试图利用这一点来获取更多广告收入🤷

和上次一样，我们能否创建一个UNIQUE约束来帮助我们？可以！即使我们存储的是确切的时间戳而不是帖子创建的周数，PostgreSQL 对表达式索引的支持也能满足我们的需求。此外，我们需要使用部分索引功能，以便仅对高亮显示的帖子强制执行此约束：

CREATE UNIQUE INDEX "one_highlighted_post_per_week_constraint"
ON "posts" ("userId", DATE_TRUNC('week', "createdAt"))
WHERE "highlighted" IS TRUE;

有了这个唯一索引，数据库将不允许两行数据同时拥有相同的“高亮显示”和“标记”"highlighted" IS TRUE组合。换句话说，一个用户每周只能有一个高亮显示的帖子。userIdDATE_TRUNC('week', "createdAt")

对于任何满足特定条件的行"highlighted" IS FALSE，它们不受此限制，我们可以根据需要插入任意数量的此类行。

案例 3：复合多重唯一性

与上述情况相同，但我们希望允许用户每周发布三篇精选帖子，而不是一篇。我们能否像上面那样使用 UNIQUE 约束来实现这一点？

没错，但这里的解决方案可能稍微复杂一些。首先，我们创建相同的posts表，但不是添加 BOOLEANhighlighted列，而是添加 INTweeklyHighlightedPostNumber列：

CREATE TABLE "posts" (
  "userId" INT NOT NULL,
  "createdAt" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  "weeklyHighlightedPostNumber" INT,
  "postContent" TEXT NOT NULL
);

如果帖子被高亮显示，则其"weeklyHighlightedPostNumber"值为整数；如果帖子未被高亮显示，"weeklyHighlightedPostNumber"则其值为 NULL。

现在我们添加一个约束条件，强制其为介于 0和weeklyHighlightedPostNumber1 之间的数字，如果它不为 NULL：13

ALTER TABLE "posts" ADD CONSTRAINT num_weekly_posts_constraint CHECK
(
  ("weeklyHighlightedPostNumber" IS NULL)
  OR
  ("weeklyHighlightedPostNumber" BETWEEN 1 AND 3)
);

现在我们可以添加一个UNIQUE约束条件：

CREATE UNIQUE INDEX "three_highlighted_posts_per_week_constraint"
ON "posts" (
  "userId",
  DATE_TRUNC('week', "createdAt"),
  "weeklyHighlightedPostNumber"
)
WHERE "weeklyHighlightedPostNumber" IS NOT NULL;

这将强制规定，对于任何高亮显示的帖子（非空行），它们的 `<div>`、`<span>` 和 `<span>`组合不能"weeklyHighlightedPostNumber"相同。由于之前的约束要求 ` <div>` 的值介于 1 和 3 之间，因此这将限制每个用户每周最多只能高亮显示 3 个帖子。"userId"DATE_TRUNC('week', "createdAt")"weeklyHighlightedPostNumber""weeklyHighlightedPostNumber"

这意味着在插入帖子时，你需要找到下一个可用的帖子编号。我们可以通过在 INSERT 操作中使用一些 SQL 代码来实现这一点。此解决方案还可以处理帖子编号出现空缺的情况（例如，如果你选中了三个帖子，然后删除了第二个）。这确实有点复杂，但请仔细查看：

async function createHighlightedPost(userId, content) {
  const insertResult = await pool.query(
    `
      WITH next_highlighted_post_num AS MATERIALIZED (
        SELECT series_num
        FROM GENERATE_SERIES(1, 3) AS series_num
        WHERE NOT EXISTS (
          SELECT *
          FROM posts
          WHERE
            posts."userId" = $1
            AND
            DATE_TRUNC('week', NOW()) <= posts."createdAt"
            AND
            posts."weeklyHighlightedPostNumber" = series_num
        )
        LIMIT 1
      )
      INSERT INTO posts
      SELECT $1, NOW(), series_num, $2
      FROM next_highlighted_post_num;    
      `,
    [userId, content],
  );

  if (insertResult.rowCount === 0) {
    throw new Error('Could not create highlighted post');
  }
}

当然，你也可以编写一个简单的查询来获取给定用户和当前周的SELECT所有现有记录，并编写 JavaScript 代码来选择新行。"weeklyHighlightedPostNumber""weeklyHighlightedPostNumber"INSERT

案例 4：复合参照唯一性

本案例与案例 2 类似，但条件不是“每周发布一篇重点帖子”，而是“必须等待 7 天才能发布另一篇重点帖子”。

在案例 2 中，如果用户在周三发布了一个精选帖子，那么他们下周初（周一）就可以再发布一个精选帖子。

但是，在案例 4 中，如果用户在周三发布了一条精选帖子，则必须等到下周三的同一时间才能发布另一条精选帖子。

这将要求任何约束都引用用户之前高亮显示的帖子创建日期，而典型的UNIQUE约束根本无法做到这一点。为了解决这个问题，我们需要引入一些额外的功能：事务和咨询锁。

这将是我们分析的最后一个也是最棘手的案例。

我们将使用与案例 2 相同的表结构：

CREATE TABLE "posts" (
  "userId" INT NOT NULL,
  "createdAt" TIMESTAMP WITHOUT TIMEZONE NOT NULL,
  "highlighted" BOOLEAN NOT NULL,
  "postContent" TEXT NOT NULL
);

以下是用 JavaScript 实现的方案：

async function createHighlightedPost(userId, content) {
  const postInsertLockNamespace = 5000;
  const queryRunner = await pool.connect();

  try {
    await queryRunner.query('BEGIN TRANSACTION;');

    await queryRunner.query(
      'SELECT PG_ADVISORY_XACT_LOCK(?, ?);',
      [postInsertLockNamespace, userId],
    );

    const existingWeeklyHighlightedPosts = await pool.query(
      `
        SELECT 1
        FROM posts
        WHERE
          "userId" = ?
          AND
          highlighted IS TRUE
          AND
          "createdAt" >= NOW() - INTERVAL '1 week'
      `,
      [userId],
    );

    if (existingWeeklyHighlightedPosts.rows.length > 0) {
      throw new Error(
        'Already have a highlighted post in the previous seven days'
      );
    }

    await queryRunner.query(
      'INSERT INTO "posts" VALUES (?, ?, ?, ?);',
      [userId, new Date(), true, content],
    );

    await queryRunner.query('COMMIT');
  } catch (err) {
    await queryRunner.query('ROLLBACK');
    throw err;
  } finally {
    queryRunner.release();
  }
}

为了实现这一点，任何要修改表中记录（例如 `<user>` INSERT、UPDATE`<post>` 或 `<post> `）的操作都必须先执行 `<user>` 操作。这会占用一个锁，任何其他尝试使用相同参数获取锁的事务都必须等待该锁被释放。如果我们始终在修改 `<user>` 之前谨慎地获取此锁，我们就可以确信，在我们获取锁之后，在我们释放锁之前，没有任何其他操作能够修改该用户的帖子。这意味着，在我们执行 `<user>`语句获取 `<user>`之后，我们知道其结果将保持正确，直到 `<user>` 操作完成且事务提交为止。这有效地阻止了用户在过去七天内已经提交过帖子的情况下再次提交高亮帖子，即使他们向我们发送大量并行帖子请求。DELETEpostsSELECT PG_ADVISORY_XACT_LOCK(5000, userId);postsSELECTexistingWeeklyHighlightedPostsINSERT

然而，要确保代码行为良好，并在修改前始终获取锁并非易事posts，尤其是在其他开发人员也在处理同一代码库的情况下（或者某个疯子（当然绝对不是你）用 pgAdmin 登录并运行随机查询！）。如果有人在插入数据前没有正确获取锁，那么这种方法就会失效。

为了稍微方便一些，我们可以创建一个触发器INSERT，当您需要对表中UPDATE的行进行操作时，该触发器会自动获取锁DELETE。代码如下：

CREATE FUNCTION "take_post_modify_lock_function"() 
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
  IF OLD."userId" IS NOT NULL THEN
    PERFORM PG_ADVISORY_XACT_LOCK(5000, OLD."userId");
  END IF;

  IF NEW."userId" IS NOT NULL THEN
    PERFORM PG_ADVISORY_XACT_LOCK(5000, NEW."userId");
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER "take_post_modify_lock_trigger"
BEFORE INSERT OR UPDATE OR DELETE ON "posts"
FOR EACH ROW
EXECUTE PROCEDURE "take_post_modify_lock_function"();

此触发器将强制任何修改都posts必须获取锁，因此在修改帖子时，您无需记住在代码中执行此操作。

不过，你仍然需要在SELECT执行操作之前在代码中验证约束条件。这里没有触发器SELECT，即使有，对于不需要获取锁的查询（例如，获取要在首页显示的帖子列表时），获取锁也是浪费资源。如果你在SELECT执行操作之前不获取锁，那么其他人可能会获取锁，并在你根据查询结果执行你计划的操作INSERT之前执行其他操作。INSERTSELECT

‡5000上面选择的第一个参数PG_ADVISORY_XACT_LOCK()是任意的。我们可以选择任何其他数字。重要的是，它应该与您创建的任何其他类型的锁都不同，这样不同含义的锁就不会重叠。例如，如果我们有另一个表comments，并且想对它执行类似的锁操作，我们可以使用5001它。

可序列化事务隔离级别

实际上，有一种秘而不宣的“魔法秘方”可以让这一切在无需显式锁定的情况下正常运行，那就是SERIALIZABLE 事务隔离级别。大多数 SQL 数据库都支持这个级别，但它们之间的实现方式往往略有不同。例如，PostgreSQL 的版本提供的隔离保证比 MySQL 的版本要强得多（MySQL 的版本有时会让人产生误解）。

当您使用 SERIALIZABLE 事务隔离级别并在事务中执行 SELECT 时，PostgreSQL 会“记住”您 SELECT 的内容，如果在事务完成之前任何数据发生更改，导致您的 SELECT 查询返回不同的结果，则您的事务将出现“序列化失败”，并且必须回滚。

我必须强调，这是一个极其强大的功能。如果您启用它（默认启用ALTER DATABASE <DATABASE NAME> SET DEFAULT_TRANSACTION_ISOLATION TO SERIALIZABLE;）并持续使用事务，那么您编写代码时就无需考虑显式加锁。但是，您需要注意一些准则和陷阱：

BEGIN TRANSACTION;对于执行多个 SQL 语句的操作（例如createHighlightedPost()我们上面看到的 JavaScript 函数），请使用显式事务（语句）。
避免运行任何隔离级别较低的事务，如果一定要运行，则务必非常小心，因为不同隔离级别的事务之间的交互方式可能难以理解。
请做好重试事务的准备，因为您可能会经常遇到序列化失败，这是正常且预期的。
尽量缩短事务处理时间，因为同时运行的事务越多，序列化失败的几率就越大。
请注意，SERIALIZABLE 事务使用的谓词锁定确实会引入一些不小的开销。
请注意，某些模式在 SERIALIZABLE 事务隔离级别下无法正常工作。例如，使用SELECT ... FOR UPDATE SKIP LOCKEDSERIALIZABLE 来实现高并发队列将无法正常工作（您会遇到大量的序列化失败）。
请确保您的查询有索引支持，因为必须执行全表扫描的事务可能会大大增加序列化失败的次数。

结论

有时候，需要担心竞态条件反而是件好事，因为它表明你的系统有很多用户。希望他们都在付费！

但随着用户群的增长，竞态条件可能会导致越来越多神秘且严重的故障，有时甚至可能是被恶意利用者故意造成的。

希望这篇文章能给你一些处理这类案件的新技巧，如果你知道其他好方法，请在评论区分享！

在撰写本文的过程中，我脑海中浮现出无数个关于案例、脚注和星号的想法，但如果全部都写进去，篇幅就会变得非常混乱。如果您有任何疑问或对其他案例感兴趣，欢迎留言！

文章来源：https://dev.to/mistval/ Winning-race-conditions-with-postgresql-54gn

菜单

分享

🏁🐘PostgreSQL 竞态条件处理之道：案例 2：复合唯一性；案例 3：复合多重唯一性；案例 4：复合引用唯一性

🏁🐘使用 PostgreSQL 赢得竞态条件

案例 2：复合独特性

案例 3：复合多重唯一性

案例 4：复合参照唯一性

案例 1：简单唯一性

案例 2：复合独特性

案例 3：复合多重唯一性

案例 4：复合参照唯一性

可序列化事务隔离级别

结论

系统设计面试中的 19 种微服务模式

使用 React 和 AWS Amplify 实现无服务器架构第三部分：跟踪应用使用情况

模型-视图-控制器（MVC）模式到底是什么？DEV 全球项目展示挑战赛，由 Mux 主办：快来展示你的项目吧！

我在两年内从 PHP 开发人员晋升为高级 C#/.NET 开发人员。

了解 Docker：第 12 部分 – 传递构建参数

Yarn 和第三方 NPM 客户端的黑暗未来 DEV 的全球展示与讲述挑战赛，由 Mux 呈现：展示你的项目！

CSS DEV 的全球展示挑战赛“响应式字体”由 Mux 呈现：展示你的项目！

我是如何以学生开发者的身份免费获得 Tabnine Pro 的，你也可以！

五大顶级JS框架

从 Rector PHP 开始：利用自动化改进您的 PHP 代码