postgresql - 使用 PostgreSQL MVCC 跨多个表的事务隔离

Question

问题总结

这是一个关于 SQL 事务中查询的可串行化的问题。

具体来说，我正在使用 PostgreSQL。可能假设我使用的是最新版本的 PostgreSQL。根据我的阅读，我相信用于支持我正在尝试做的事情的技术被称为“多版本并发控制”或“MVCC”。

总结一下:如果我有一个主表，以及连接到该主表的多于 1 个外键链接表，我如何保证，对于表中的给定键，以及任何数字在一个事务中使用该键的 SELECT 语句的数量，每个语句都从任何链接表中进行选择，我将获得在我开始事务时存在的数据？

其他问题

这个问题类似，但范围更广，问题和答案与 PostgreSQL 没有具体关系: Transaction isolation and reading from multiple tables on SQL Server Express and SQL Server 2005

例子

假设我有 3 个表:

bricks
    brickworks (primary key)
    completion_time (primary key)
    has_been_sold

brick_colors
    brickworks (primary key, foreign key pointing to "bricks")
    completion_time (primary key, foreign key pointing to "bricks")
    quadrant (primary key)
    color

brick_weight
    brickworks (primary key, foreign key pointing to "bricks")
    completion_time (primary key, foreign key pointing to "bricks")
    weight

砖厂一次生产一 block 砖。它制作的砖 block 在其 4 个象限中的每个象限中可能具有不同的颜色。

后来有人分析这些砖 block 以确定它们的颜色组合，并将结果写入 brick_colors 表。

其他人分析砖 block 以确定它们的重量，并将结果写入 brick_weight 表。

在任何给定时间，现有积木可能有也可能没有记录颜色，可能有也可能没有记录重量。

---

存在一个应用程序，并且该应用程序收到有人想要购买特定砖 block 的消息(此时应用程序已通过其 brickworks/completion_time 组合键获知)。

应用程序希望在它开始查询的那一刻选择砖 block 的所有已知属性。

如果颜色或重量信息被添加到 MID-TRANSACTION，应用程序不想知道它。

应用程序想要执行单独的查询(而不是对外键链接表进行多个 JOIN 的 SELECT，因为 brick_colors 表，这可能会返回多行)。

---

这个例子刻意简单；如果我的示例包括 10 个外键链接表，并且其中许多或所有表都可以为同一主键返回多行(就像 brick_colors 在我上面的例子)。

尝试的解决方案

这是我到目前为止的想法:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY ;

-- All this statement accomplishes is telling the database what rows should be returned from the present point-in-time in future queries within the transaction
SELECT DISTINCT true
FROM bricks b
LEFT JOIN brick_colors bc ON bc.brickworks = b.brickworks AND bc.completion_time = b.completion_time
LEFT JOIN brick_weight bw ON bw.brickworks = b.brickworks AND bw.completion_time = b.completion_time
WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;

SELECT * FROM brick_colors WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;
SELECT * FROM brick_weight WHERE b.brickworks = 'Brick-o-Matic' AND b.completion_time = '2017-02-01T07:35:00.000Z' ;

COMMIT ;

仅出于确保可序列化的目的而将第一个 SELECT 与 JOIN 一起使用似乎很浪费。

还有其他方法吗？

引用资料

PostgreSQL Concurrency Control

PostgreSQL Transcation Isolation

PostgreSQL SET TRANSACTION statement

Answer 1

这是你问题的本质:

how do I guarantee that, for ...... any number of SELECT statements ..... inside one transaction ....... I will get data as it existed at the time I started the transaction?

---

这正是Repeatable Read Isolation Level保证:

The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. (However, the query does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) This is a stronger guarantee than is required by the SQL standard for this isolation level, and prevents all of the phenomena described in Table 13-1. As mentioned above, this is specifically allowed by the standard, which only describes the minimum protections each isolation level must provide.

This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the transaction, not as of the start of the current query within the transaction. Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started.

---

一个实际的例子 - 假设我们有 2 个简单的表:

CREATE TABLE t1( x int );
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2( y int );
INSERT INTO t2 VALUES (1),(2),(3);

一些表，它们的结构，主键，外键等在这里并不重要。

让我们打开第一个 session ，启动可重复读取隔离级别，并运行两个简单且独立的 SELECT 语句:

test=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
test=# SELECT * FROM t1;
 x
---
 1
 2
 3
(3 wiersze)


test=# SELECT * FROM t2;
 y
---
 1
 2
 3
(3 wiersze)

请注意，START TRANSACTION 命令会自动禁用 session 中的自动提交模式。

---

现在在另一个 session 中(启用默认自动提交模式)将一些记录插入到t1:

test2=# INSERT INTO t1 VALUES(10),(11);

新值被插入并自动提交(因为自动提交已打开)。

---

现在回到第一个 session 并再次运行 SELECT:测试=#从t1中选择*；

 x
---
 1
 2
 3
(3 wiersze)

如您所见，session1(具有事件的可重复读取事务)在事务开始后看不到任何提交的更改。

---

让我们用表 t2 做同样的实验 - 转到第二个 session 并发出:

test2=# DELETE FROM t2 WHERE y = 2;
DELETE 1

现在回到第一个 session 并再次运行 SELECT:

test=# SELECT * FROM t2;
 y
---
 1
 2
 3
(3 wiersze)

再次如您所见，session1(具有事件的可重复读取事务)在事务开始后没有看到任何提交的更改。

---

现在，在 session 1 中，完成发出 COMMIT 的事务，然后选择:

test=# SELECT * FROM t1;
 x
---
 1
 2
 3
(3 wiersze)

test=# SELECT * FROM t2;
 y
---
 1
 2
 3
(3 wiersze)

test=# COMMIT;
COMMIT

test=# select * from t1;
 x
----
  1
  2
  3
 10
 11
(5 wierszy)


test=# select * from t2;
 y
---
 1
 3
(2 wiersze)

如您所见，当可重复读取事务启动并处于事件状态时，您可以多次运行许多单独的 select 语句，并且所有这些 select 语句都会看到与事务开始时相同的稳定数据快照，而不管其他 session 中提交的任何数据。