主页 / dba / 问题 / 312261
Accepted
auxdevelopment
Asked: 2022-05-18 12:37:10 +0800 CST

将 (LEFT) JOIN 与 SELECT FOR UPDATE 组合时数据不一致

  • 772

我最近偶然发现了SELECT ... FOR UPDATE与(LEFT) JOIN. 这是表结构以及重现结果的场景：

表结构

create table counter (
  counter_id serial primary key,
  current_counter int not null default 0
);

create table diff (
  diff_id serial primary key,
  diff_increase int not null default 0,
  counter_id serial references counter(counter_id) not null
);

设想

有两个并发事务 A 和 B，都执行相同的查询。

  1. 事务 A 以该查询开始，并且能够获取锁并继续。
select *
  from counter
  left join diff on counter.counter_id = diff.counter_id
 where counter.counter_id = 1
 order by diff.diff_id desc
 limit 1
   for update of counter
;

  1. 事务 B 尝试执行相同的查询，但无法获取锁，因此等待。

  2. 事务 A 将执行以下查询：

update counter
   set current_counter = current_counter + 100
 where counter_id = 1
;

insert into diff (diff_increase, counter_id) values (100, 1)
;

commit;
  1. 事务 A 已完成，数据库的状态现在应如下所示：
-- counter table
counter_id | current_counter
------------------------------
1          | 200

-- diff table
diff_id | diff_increase | counter_id
--------------------------------------
1       | 50            | 1
2       | 50            | 1
3       | 100           | 1

预期行为

事务 B 看到更新的计数器 ( current_counter = 200) 和最后的差异 ( diff_id = 3)。

实际行为

事务 B 继续使用counter表的新状态（意思是current_counter = 200），而diff_id仍然是 2 而不是 3。

这种行为是预期的吗？如果是这样，为什么同一个查询会看到数据库的不同状态？这不违反READ COMMITTED隔离级别的保证吗？

在 Linux 上使用 PostgreSQL 13 进行测试。

postgresql locking

2 个回答

  1. 本质上，考虑了并发更新的行，但不考虑并发插入的行。

    在默认READ COMMITTED隔离级别下，每个命令只能看到在它开始之前已经提交的行。UPDATE添加新行版本，而不是新行。

    diff_id = 3在您的示例中插入的行 ( )对于在提交该行之前启动的并发事务不可见。锁定与行的可见性FOR UPDATE无关。但是会考虑并发添加的新行版本。UPDATE

    手册中的基本报价：

    1.

    [...]SELECT查询（没有FOR UPDATE/SHARE子句） 只能看到在查询开始之前提交的数据；

    UPDATE, DELETE, SELECT FOR UPDATE, 和SELECT FOR SHARE 命令在搜索目标行方面的行为相同SELECT：它们只会找到在命令开始时间提交的目标行。

    WHERE重新评估命令（子句）的搜索条件，以查看行的更新版本是否仍然匹配搜索条件。如果是这样，则第二个更新程序使用行的更新版本继续其操作。

    整章推荐阅读。

    这种情况下最好的行动方案可能是SERIALIZABLE交易

    • 1
  2. Best Answer

    由于锁定子句，您可能会看到对diff表的无序影响。FOR UPDATE

    SELECT FOR UPDATE的文档在以下“警告”段落中对此提出了警告：

    SELECT 命令在 READ COMMITTED 事务隔离级别上运行并使用 ORDER BY 和锁定子句可能会乱序返回行。这是因为首先应用 ORDER BY。该命令对结果进行排序，但随后可能会阻止尝试获取一个或多个行的锁定。一旦 SELECT 解除阻塞，一些排序列值可能已被修改，导致这些行看起来是乱序的（尽管它们在原始列值方面是按顺序排列的）。

    您可能还对该问题的 pgsql 邮件列表中的答案感兴趣：SELECT ... FOR UPDATE OF SKIP LOCKED 返回可以在与 lock 不同的表上过滤表时返回同一行，其中 Thomas Munro 指向该部分文档并解释：

    这可能看起来很令人惊讶，但这是因为 FOR UPDATE 遵循更新链，允许您查看活动快照不可见的已提交元组，只要它们仍然满足 WHERE 子句

    然后

    您需要确保行锁定适用于与 WHERE 子句相同的关系以避免这种情况。

    您的查询无法确定这一点，如果它被锁定并期望具有相同可见性级别的counter相应值。diff

    一般来说，Read Committed隔离级别在面对并发写入时并不能提供任何保证，所以并发异常的通用解决方案是使用更高的隔离级别并处理序列化失败。

    • 1

相关问题