PostgreSQL/可见性

PostgreSQL
可见性

一些典型的問題

很明显，每个事务都会“看到”在其生命周期内执行的所有数据更改，而不会出现问题。但有一些情况，多个进程想要在它们事务的重叠时间间隔内，甚至是在同一时间点读取或写入相同数据，这在具有多个 CPU 或磁盘阵列的服务器上是可能的。在这些情况下，可能会发生不同类型的冲突和可疑影响。

应用程序可以接受或不接受从这些竞争情况产生的影响。它们可以根据自己的需求选择不同级别的隔离来抵御其他事务的活动。该级别定义了它们愿意接受哪些影响以及不愿意接受哪些影响。级别越高意味着可以发生的负面影响越少，但数据库系统必须更加努力工作，并且整体吞吐量会降低。

以下是一些关于两个事务 T_A 和 T_B 的示例。如果未明确说明，两者都不会执行 COMMIT。

T_A 读取 id = 1 的行。T_B 读取同一行。T_A 将列 X 增加 1。T_B 将同一列增加 1。结果会怎样？存在“丢失更新”的风险。

T_A 更改了 id = 1 的行的值。如果 T_B 读取同一行，它会看到什么？T_A 可能会执行 ROLLBACK。(未提交读取)

T_A 读取 id = 1 的行。T_B 读取同一行，更改一个值并执行 COMMIT。T_A 再次读取该行。与第一次读取相比，它将看到一个不同的值。(不可重复读取)

T_A 读取所有 status = 'ok' 的行。T_B 插入一个额外的 status = 'ok' 的行并执行 COMMIT。T_A 再次读取所有 status = 'ok' 的行，并接收一个不同的行数。(幻读)

T_A 读取并更改 id = 1 的行。T_B 读取并更改 id = 2 的行。T_B 想要读取并更改 id = 1 的行。因为 T_A 尚未提交其更改，所以 T_B 必须等待 T_A。T_A 想要读取并更改 id = 2 的行。因为 T_B 尚未提交其更改，所以 T_A 必须等待 T_B。(死锁)

PostgreSQL 的解决方案

SQL 标准描述了 3 种影响（或问题情况）“未提交读取”、“不可重复读取”和“幻读”，并定义了事务之间的 4 种隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。每个级别都比其前身更严格，并且可以防止更多影响，这意味着例如“不可重复读取”可以在 READ COMMITTED 级别发生，但在 REPEATABLE READ 或 SERIALIZABLE 级别无法发生。

PostgreSQL 实现这些级别。但是，由于其 MVCC 模型，它对某些方面的实现比标准要求的更加严格。如果一个事务请求 READ UNCOMMITTED 级别，PostgreSQL 始终将其处理为 READ COMMITTED，这导致了所有未提交更改在任何级别上对所有其他事务都不可见 - 只有已提交更改才能被其他事务看到。

示例

以下示例作用于一个名为 t1 的表，它有两个列 id 和 col，只有一行。

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INTEGER, col INTEGER);
INSERT INTO t1 VALUES (1, 100);
SELECT * FROM t1;
id | col 
----+-----
  1 | 100
(1 row)

未提交读取

该示例表明 PostgreSQL 仅向其他事务显示已提交的行。

事务 A	事务 B
BEGIN; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- you can shorten the two commands into one: -- BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; UPDATE t1 SET col=101 WHERE id=1; SELECT col FROM t1 WHERE id=1; -- 101
	-- 'READ UNCOMMITTED' acts equal to 'READ COMMITTED' -- other transactions solely sees committed rows! BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT col FROM t1 WHERE id=1; -- 100 (the committed one!)
COMMIT; SELECT col FROM t1 WHERE id=1; -- 101
	SELECT col FROM t1 WHERE id=1; -- 101 (again: the committed one!) COMMIT; -- no real effect SELECT col FROM t1 WHERE id=1; -- 101

丢失更新

该示例表明 PostgreSQL 在最低隔离级别（以及所有其他级别）中都阻止了“丢失更新”。（表 t1 包含其原始值。）

事务 A	事务 B
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT col FROM t1 WHERE id=1; -- 100 UPDATE t1 SET col=col+1 WHERE id=1; SELECT col FROM t1 WHERE id=1; -- 101
	BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT col FROM t1 WHERE id=1; -- 100 UPDATE t1 SET col=col+1 WHERE id=1; -- UPDATE is queued and must wait for the -- COMMIT of transaction A . .
COMMIT;
	-- the above UPDATE executes after (!) the COMMIT -- of transaction A SELECT col FROM t1 WHERE id=1; -- 102

两个 UPDATE 语句都执行了，没有丢失任何内容。

请注意，事务 B 是“不可重复读取”的示例（见下文），因为隔离级别是“（UN）COMMITTED READ”。首先，它使用 SELECT 命令读取值“100”。接下来，它使用 UPDATE 命令读取“101” - 在事务 A 提交之后 - 并将其增加到“102”。如果隔离级别是“REPEATABLE READ”，事务 B 会收到错误消息“由于并发更新而无法序列化访问”，这是 PostgreSQL 对 UPDATE 请求的反应。

不可重复读取

该示例显示了不可重复读取。（表 t1 包含其原始值。）

事务 A

事务 B

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT col FROM t1 WHERE id=1;
-- 100

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET col=101 WHERE id=1;
SELECT col FROM t1 WHERE id=1;
-- 101
COMMIT;

SELECT col FROM t1 WHERE id=1;
-- 101 (same transaction, but different value)
-- ' ISOLATION LEVEL REPEATABLE READ' or 
-- 'SERIALIZATION' will avoid such an effect

幻读

该示例显示了幻读。（表 t1 包含其原始值。）

事务 A

事务 B

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT col FROM t1 WHERE id>0;
-- 1 row: 100

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO t1 VALUES (2, 200);
COMMIT;
SELECT col FROM t1 WHERE id>0;
-- 2 rows: 100 and 200

SELECT col FROM t1 WHERE id>0;
-- 2 rows: 100 and 200
-- (same transaction, same query, but different rows)
-- ' ISOLATION LEVEL SERIALIZABLE'
-- will avoid such an effect

死锁

该示例显示了死锁。（表 t1 包含两行。）

DELETE FROM t1;
INSERT INTO t1 VALUES (1, 100);
INSERT INTO t1 VALUES (2, 200);

事务 A	事务 B
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE t1 SET col=col+1 WHERE id=1; SELECT col FROM t1 WHERE id=1; -- 101
	BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE t1 SET col=col+1 WHERE id=2; SELECT col FROM t1 WHERE id=2; -- 201 UPDATE t1 SET col=col+1 WHERE id=1; . . -- must wait for COMMIT/ROLLBACK of transaction A
UPDATE t1 SET col=col+1 WHERE id=2; -- must wait for COMMIT/ROLLBACK of transaction B. -- -- PostgreSQL detects the deadlock and performs a -- ROLLBACK to overcome the circular situation. -- message: "ERROR: deadlock detected ..."
	-- processing goes on with a 'success message' SELECT col FROM t1 WHERE id>0; -- 101 -- 201 -- no UPDATEs from transaction A. They were -- ROLLBACK-ed by PostgreSQL.

ACID

PostgreSQL
可见性

MVCC