Usage SET TRANSACTION SNAPSHOT_ID

A snapshot of an existing transaction can be used in a new transaction. This snapshot must first be exported (pg_export_snapshot). The SET TRANSACTION SNAPSHOT must be executed at the start of a new TRANSACTION. The transaction isolation level must be Serializable or Repeatable Read. If the new transaction isolation level is Serializable, then the old transaction must also be Serializable. If the old transaction was read-only, the new transaction needs to be read-only as well.

Session 1 exports snapshot and Session 2 synchronizes with session 1’s snapshot via SET TRANSACTION snapshot. Session 2 is not affected by whether or not another session commits a transaction after session 1 export snapshot.

Session 1 export snapshot No tuple with id=897 in t1

[postgres@hgcndn ~]$PSQL -p 5432 -U postgres-h 127.0.0.1 PSQL (12.4) Type "help" for help TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN postgres=# SELECT pg_export_snapshot(); pg_export_snapshot --------------------- 00000003-0000000F-1 (1 row) postgres=# select * from t1 where id=897; id | name ----+------ (0 rows)

Session 2 commits t1 insert tuple with id=897

[postgres@hgcndn ~]$ psql -p 5432 -U postgres -h 127.0.0.1
psql (12.4)
Type "help" for help.

postgres=#
postgres=# select * from t1 where id=897;
 id | name
----+------
(0 rows)

postgres=# insert into t1 values(897, 'test');
INSERT 0 1
postgres=# select * from t1 where id=897;
 id  | name
-----+------
 897 | test
(1 row)

Session 2 commits changes at read committed isolation level, but in a new TRANSACTION, align the SET TRANSACTION SNAPSHOT with the export SNAPSHOT TRANSACTION in Session 1. You don’t see the tuple for the insert in Session 2

[postgres@hgcndn ~]$ psql -p 5432 -U postgres -h 127.0.0.1
psql (12.4)
Type "help" for help.

postgres=#
postgres=# select * from t1 where id=897;
 id  | name
-----+------
 897 | test
(1 row)

postgres=#  BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# SET TRANSACTION SNAPSHOT '00000003-0000000F-1';
SET
postgres=# select * from t1 where id=897;
 id | name
----+------
(0 rows)

Reference https://www.postgresql.org/do… https://www.postgresql.org/do…