主页 / dba / 问题 / 318088
Accepted
Valentin Nasraty
Asked: 2022-10-12 08:04:37 +0800 CST

两个不同表的并发更新失败

  • 772

我正在使用具有以下数据库架构的 PostgreSQL：

CREATE TABLE plans (
    slug VARCHAR(500) PRIMARY KEY
);

CREATE TABLE users (
    id VARCHAR(16) PRIMARY KEY,
    org_id VARCHAR(16) NOT NULL
);

CREATE TABLE orgs (
    id VARCHAR(16) PRIMARY KEY,
    plan_slug VARCHAR(500) NOT NULL,
    last_write_at DOUBLE PRECISION
);

就我而言，我想编写一个查询来更新一些组织plan_slug并保护它免受其他可能的并发更新。为此，我SELECT在子查询中使用 a 和 aFOR UPDATE以特定顺序锁定行以避免死锁。像下面的查询：

UPDATE orgs
SET plan_slug = 'plan_1'
WHERE id = ANY(
    SELECT subquery_orgs.id
    FROM orgs AS subquery_orgs
    JOIN users ON users.org_id = subquery_orgs.id
    WHERE users.id = ANY('{user_1, user_2, user_3}')
    ORDER BY subquery_orgs.id
    FOR UPDATE
);

我注意到，如果此请求需要很长时间才能运行，则很有可能与另一个尝试更新last_write_at组织的查询发生冲突（一个已被第一个查询更新的组织）。

如下：

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1';

查询将成功。但是如果我在查询plan_slug的WHERE子句中添加，它总是无法更新。Postgre 返回UPDATE 0.

查询如下：

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1' 
AND plans.slug = orgs.plan_slug;

据我了解，该过程应该是：

  1. 第一个查询执行。该plan_slug行已锁定。
  2. 第二个查询执行。它停止执行并等待行解锁。
  3. 第一个查询完成其更新。
  4. 第二个查询重新启动并重新评估更改。
  5. plan_slug已更改，但表中存在新的，plans第二个查询应该成功。

plan_slug那么为什么当我添加到WHERE子句时查询无法更新？

postgresql update

1 个回答

  1. Best Answer

    相关问题

    问题

    您对过程的总结是正确的，直到第 4 步。获取锁后查询不会重新启动；修改的行版本被读取并替换旧的行版本。在获取锁之前，发生了表连接，新的行版本使连接变得毫无意义，并且这些行被丢弃。

    这是我修改后的事件：

    1. 第一个查询开始执行。中的行orgs被锁定。

    2. 第二个查询开始执行。

      一个。从andUPDATE读取旧的行版本（忽略 #1，因为它还没有）。orgsplansCOMMIT

      湾。UPDATE执行连接，orgs并且plans有资格orgs.id构建目标行列表。

      C。UPDATE尝试将目标行FOR NO KEY SHARE和块锁定在第一个查询的FOR UPDATE锁上。

    3. 第一个查询COMMIT。

    4. 第二个查询解除阻塞并注意到它所针对的行已更改。

      一个。读取新的行版本并替换连接中的现有行版本。

      湾。重新评估条件，现在连接条件失败，因为orgs.plan_slug已更改并且不再匹配plans之前连接的行。行被丢弃。

    5. 第二个查询找不到要使用的行UPDATE。

    以图形方式显示它：

    • 第二个查询以联接开始，如下所示：

      ID plan_slug last_write_at 蛞蝓
      用户_1 计划_1 888 计划_1

    • 第一个查询完成，orgs.plan_slug从plan_1变为plan_2。

    • 第二个查询继续，读取orgs行版本，并将其连接状态更改为：

      ID plan_slug last_write_at 蛞蝓
      用户_1 计划_2 888 计划_1

    • 第二个查询现在重新评估其WHERE条件。哦哦，plan_2!= plan_1，行被丢弃。

    解决方案

    1. 不要FROM plans在你的UPDATE last_write_at.... 老实说，我不明白您查询的目的是什么；中的列plans没有以任何有意义的方式引用。我认为为了简化问题而删除了更多内容。也许您可以更新问题以阐明为什么会这样。
    2. REPEATABLE READ对UPDATE last_write_at...事务使用隔离级别。在那个级别，你得到的不是更新任何行，而是错误ERROR: could not serialize access due to concurrent update。您可以中止并重试事务，知道它正在获取新数据。
    3. SELECT .. FOR UPDATE在事务中使用previous来锁定before 行UPDATE。然后UPDATE永远不必与从它下面改变的行抗衡。在 PL/pgSQL 中，您可以改用它PERFORM 1 ... FOR UPDATE来避免输出行。举个例子： 
      BEGIN;
SELECT id FROM orgs WHERE id='org_1' FOR UPDATE;
UPDATE orgs SET last_write_at = 999 FROM plans 
  WHERE orgs.id = 'org_1' AND plans.slug = orgs.plan_slug;
COMMIT;

    学分

    • 5

相关问题