Снимки данных двух транзакций

Таблица с одной строкой: 

=> CREATE DATABASE mvcc_snapshots;

CREATE DATABASE

=> \c mvcc_snapshots

You are now connected to database "mvcc_snapshots" as user "postgres".

=> CREATE TABLE t(n integer);

CREATE TABLE

=> BEGIN;

BEGIN

=> INSERT INTO t(n) VALUES (1);

INSERT 0 1

=> SELECT txid_current();

 txid_current 
--------------
        55468
(1 row)


=> COMMIT;

COMMIT

Первая транзакция видит строку: 

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM t;

 n 
---
 1
(1 row)


=> SELECT txid_current();

 txid_current 
--------------
        55469
(1 row)


=> SELECT txid_current_snapshot();

 txid_current_snapshot 
-----------------------
 55469:55469:
(1 row)

Вторая транзакция в другом сеансе удаляет строку: 

=> \c mvcc_snapshots

You are now connected to database "mvcc_snapshots" as user "postgres".

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> DELETE FROM t;

DELETE 1

=> SELECT * FROM t;

 n 
---
(0 rows)


=> SELECT txid_current();

 txid_current 
--------------
        55470
(1 row)


=> SELECT txid_current_snapshot();

 txid_current_snapshot 
-----------------------
 55469:55469:
(1 row)

Первая транзакция продолжает видеть строку: 

=> SELECT xmin, xmax, * FROM t;

 xmin  | xmax  | n 
-------+-------+---
 55468 | 55470 | 1
(1 row)

Снимки обоих транзакций одинаковы, и по правилам видимости они обе должны были бы видеть строку, так как

Однако вторая транзакция все-таки не видит строку, поскольку она сама ее удалила, а это - исключение в обычных правилах видимости. 

=> COMMIT;

COMMIT

=> COMMIT;

COMMIT

Снимок вложенного запроса

Внутри функции, подсчитывающей число строк таблицы, вставим односекундную задержку для удобства тестирования: 

=> CREATE FUNCTION test() RETURNS integer AS $$
SELECT pg_sleep(1);
SELECT count(*)::integer FROM t;
$$ VOLATILE LANGUAGE sql;

CREATE FUNCTION

Теперь начинаем транзакцию с уровнем изоляции Read Committed и в запросе несколько раз вызываем функцию, одновременно подсчитывая количество строк подзапросом. 

=> BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN

=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);

Параллельно выполняем другую транзакцию, которая увеличивает число строк в таблице. Если основной запрос и вложенный запрос используют разные снимки, мы обнаружим это по разнице в результате. 

=> INSERT INTO t VALUES (1);

INSERT 0 1


 cnt | test 
-----+------
   0 |    0
   0 |    0
   0 |    1
   0 |    1
   0 |    1
(5 rows)


=> END;

COMMIT

Значение первого столбца не изменяется - запрос использует снимок, созданный в начале выполнения. Однако значение во втором столбце изменяется - запросы внутри volatile-функции используют отдельные снимки.

Повторим эксперимент для уровня изоляции Repeatable Read: 

=> TRUNCATE t;

TRUNCATE TABLE

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);

=> INSERT INTO t VALUES (1);

INSERT 0 1


 cnt | test 
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)


=> END;

COMMIT

Теперь еще раз то же самое для функции с категорией изменчивости stable. 

=> ALTER FUNCTION test STABLE;

ALTER FUNCTION

Уровень Read Committed: 

=> TRUNCATE t;

TRUNCATE TABLE

=> BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN

=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);

=> INSERT INTO t VALUES (1);

INSERT 0 1


 cnt | test 
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)


=> END;

COMMIT

Уровень Repeatable Read: 

=> TRUNCATE t;

TRUNCATE TABLE

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);

=> INSERT INTO t VALUES (1);

INSERT 0 1


 cnt | test 
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)


=> END;

COMMIT

Выводы: