时间戳

首页 > 解决方案 > 涉及子选择和外键的 Postgres 竞争条件

sql - 涉及子选择和外键的 Postgres 竞争条件

问题描述

我们有 2 个表定义如下

CREATE TABLE foo (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE bar (
  foo_id BIGINT UNIQUE,
  foo_name TEXT NOT NULL UNIQUE REFERENCES foo (name)
);

我注意到在同时执行以下两个查询时

INSERT INTO foo (name) VALUES ('BAZ')

INSERT INTO bar (foo_name, foo_id) VALUES ('BAZ', (SELECT id FROM foo WHERE name = 'BAZ'))

bar在某些情况下，最终可能会在where foo_idis中插入一行NULL。这两个查询由两个完全不同的进程在不同的事务中执行。

这怎么可能？我希望第二条语句要么因外键违规而失败（如果其中的记录foo不存在），或者以非空值foo_id（如果存在）成功。

是什么导致了这种竞争状况？是由于子选择，还是由于检查外键约束的时间？

我们正在使用隔离级别“已提交”和 postgres 版本 10.3。

编辑

我认为这个问题并不是特别清楚让我感到困惑的是什么。问题是关于在执行单个语句期间如何以及为什么观察到数据库的 2 种不同状态。子选择正在观察 foo 中的记录不存在，而 fk 检查将其视为存在。如果只是没有规则阻止这种竞争条件，那么这本身就是一个有趣的问题——为什么不能使用事务 ID 来确保两者都观察到相同的数据库状态？

标签： sqlpostgresqlconcurrencyforeign-keyssubquery

解决方案

中的子选择INSERT INTO bar看不到同时插入的新行，foo因为后者尚未提交。

但是到执行检查外键约束的查询时，INSERT INTO foo已经提交，因此外键约束不会报告错误。

解决此问题的一种简单方法是REPEATABLE READ使用INSERT INT bar. 然后外键检查使用与 相同的快照INSERT，它不会看到新提交的行，并且会抛出约束冲突错误。

推荐阅读