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