主页 / user-262080

Valentin Nasraty's questions

Martin Hope
Valentin Nasraty
Asked: 2022-10-12 08:04:37 +0800 CST
  • 4

我正在使用具有以下数据库架构的 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