Transaction Isolation and Exported Snapshots in PostgreSQL
Transaction isolation levels control what data a transaction can see when other transactions are concurrently modifying the database. Understanding them is essential for CDC (Change Data Capture), where an initial snapshot of the database must be taken at the exact point a replication slot is created — ensuring no gaps or overlaps between the snapshot and the WAL stream.
This post explains the four isolation levels, the read anomalies they prevent, how MVCC snapshots work under the hood, and how PostgreSQL’s exported snapshot mechanism ties it all together for CDC.
Isolation Levels in PostgreSQL
You can set the isolation level per transaction or at the session level as a default. PostgreSQL defaults to READ COMMITTED.
PostgreSQL supports four isolation levels, though READ UNCOMMITTED behaves identically to READ COMMITTED (PostgreSQL never allows dirty reads).
| Level | Snapshot Behavior | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Serialization Anomalies |
|---|---|---|---|---|---|
| READ UNCOMMITTED | Same as READ COMMITTED | No | Yes | Yes | Yes |
| READ COMMITTED (default) | Fresh snapshot per statement | No | Yes | Yes | Yes |
| REPEATABLE READ | Single snapshot at first statement | No | No | No | Yes |
| SERIALIZABLE | Single snapshot + conflict detection | No | No | No | No |
- READ COMMITTED: Each statement sees all data committed before that statement started. Two identical queries in the same transaction can return different results if another transaction commits in between. Even within a
BEGIN/COMMITblock, rows can change between statements because each statement gets its own fresh snapshot. - REPEATABLE READ: The transaction takes a snapshot at the first statement. All subsequent reads see that same snapshot, regardless of concurrent commits. This prevents non-repeatable reads — reading the same row twice and getting different values because another transaction modified it in between. If a concurrent transaction modifies a row this transaction also tries to modify, PostgreSQL aborts the transaction with a serialization error.
- SERIALIZABLE: Like REPEATABLE READ, but PostgreSQL also tracks read/write dependencies between concurrent transactions. It guarantees that the result of concurrent transactions is equivalent to some serial ordering (A then B, or B then A). If no such ordering exists that produces the same result, PostgreSQL aborts one of the transactions. This is the most restrictive level — it provides the strongest correctness guarantees but requires retry logic since transactions are aborted more often.
From least to most restrictive: READ COMMITTED < REPEATABLE READ < SERIALIZABLE. More restrictive means more aborts but stronger correctness guarantees.
Read Anomalies
Dirty Read
Reading uncommitted data from another transaction. Transaction B sees a row that Transaction A wrote but hasn’t committed yet. If A rolls back, B acted on data that never existed.
1
2
3
4
5
6
7
8
Transaction A Transaction B
───────────── ─────────────
INSERT INTO demo VALUES (3, 'charlie');
-- not committed yet
SELECT * FROM demo;
→ alice, bob, charlie ← dirty read
ROLLBACK;
-- charlie never existed, but B already saw it
PostgreSQL never allows dirty reads, even at READ UNCOMMITTED.
Non-Repeatable Read
Reading the same row twice in the same transaction and getting different values because another transaction modified and committed it in between.
1
2
3
4
5
6
7
8
Transaction A Transaction B
───────────── ─────────────
SELECT name FROM demo WHERE id=1;
→ 'alice'
UPDATE demo SET name='ALICE' WHERE id=1;
COMMIT;
SELECT name FROM demo WHERE id=1;
→ 'ALICE' ← different value, same row
Under READ COMMITTED, this happens because each statement gets a fresh snapshot. Under REPEATABLE READ, the second read still returns 'alice' because the snapshot is frozen at transaction start.
Phantom Read
Running the same query twice in a transaction and getting a different set of rows back — another transaction inserted or deleted rows matching the WHERE clause.
1
2
3
4
5
6
7
8
Transaction A Transaction B
───────────── ─────────────
SELECT * FROM demo WHERE id > 0;
→ alice, bob
INSERT INTO demo VALUES (3, 'charlie');
COMMIT;
SELECT * FROM demo WHERE id > 0;
→ alice, bob, charlie ← new row appeared
This is different from a non-repeatable read: phantom reads are about new or missing rows, non-repeatable reads are about the same row returning different values.
In the SQL standard, REPEATABLE READ only prevents non-repeatable reads but still allows phantoms. PostgreSQL’s implementation is stricter — REPEATABLE READ prevents both because it uses a true snapshot.
Serialization Anomaly
Two transactions each make decisions based on what they read, and both commit successfully, but the combined result is impossible if they had run one at a time in any order.
Two counters that must sum to 100:
1
2
3
4
5
6
7
8
9
10
11
12
13
Table: counters a = 60, b = 40
Transaction A Transaction B
───────────── ─────────────
SELECT b FROM counters;
→ 40
SELECT a FROM counters;
→ 60
SET a = 40 + 10 = 50
SET b = 60 + 10 = 70
COMMIT; COMMIT;
Result: a = 50, b = 70 (sum = 120, should be 100)
If A ran first then B, or B first then A, the sum would stay at 100. But running concurrently under REPEATABLE READ, both transactions see consistent snapshots and don’t touch the same rows, so no conflict is detected. Under SERIALIZABLE, PostgreSQL detects the read/write dependency cycle and aborts one of them.
Syntax
1
2
3
4
5
6
7
8
9
-- set at transaction start
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or set inside an already-open transaction (before any queries)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or set at session level (default for all transactions in this session)
SET SESSION default_transaction_isolation = 'repeatable read';
MVCC and Why a Snapshot Name, Not an LSN
PostgreSQL uses MVCC (Multi-Version Concurrency Control) to handle concurrent access. Instead of locking rows when they’re read, PostgreSQL keeps multiple versions of each row. Each transaction decides which versions are visible based on a snapshot — a record of which transactions were committed, in-progress, or not yet started at a particular moment.
A snapshot is not just a position in the WAL. It contains:
- The highest completed transaction ID at snapshot time
- The list of transaction IDs that were still in-progress (and therefore invisible)
- The earliest transaction ID that was still active
Each row in PostgreSQL has a xmin (the transaction ID that created it) and xmax (the transaction ID that deleted/updated it). When a query runs, PostgreSQL compares each row’s xmin/xmax against the snapshot’s visibility rules to decide whether the row is visible.
This is why SET TRANSACTION SNAPSHOT takes a name, not an LSN. An LSN is a byte offset in the WAL — it tells you where in the change stream something happened, but it doesn’t tell you which transactions were committed and which were still in-flight at that moment. Two transactions at the same LSN can have different visibility depending on which other transactions had committed.
The snapshot name is a handle to this full visibility state, exported by the transaction that created the replication slot. When another transaction calls SET TRANSACTION SNAPSHOT '<name>', it adopts the exact same visibility rules — seeing the same committed rows and ignoring the same in-progress transactions. An LSN alone couldn’t provide this.
The snapshot is held in memory — not persisted to disk. It’s only valid while the exporting transaction is still open. Old row versions referenced by the snapshot are retained in the heap (table files) via MVCC as long as any transaction still holds that snapshot. Once all referencing transactions end, VACUUM can clean them up.
Exported Snapshots and CDC
In CDC, the initial data export must be taken at the exact point a replication slot is created. The slot determines where the WAL stream will begin, and the snapshot determines what the database looked like at that moment. Together, they cover the full timeline: existing state from the snapshot + all subsequent changes from the WAL.
When a replication slot is created via the replication protocol (CREATE_REPLICATION_SLOT), PostgreSQL returns four columns:
| Column | Description |
|---|---|
slot_name |
Name of the created slot |
consistent_point |
LSN at slot creation |
snapshot_name |
Identifier for SET TRANSACTION SNAPSHOT |
output_plugin |
The logical decoding plugin (e.g. pgoutput) |
This requires a replication protocol connection (connection string includes replication=database), not a regular SQL connection. The SQL function pg_create_logical_replication_slot() only returns slot_name and lsn — it does not expose the snapshot name.
The snapshot and the LSN serve completely different purposes:
- Snapshot name — used with
SET TRANSACTION SNAPSHOTtoSELECT *from tables and get the initial state via a normal SQL connection - LSN — used with
START_REPLICATIONto begin streaming WAL changes via the replication connection
Neither can do the other’s job. The WAL only contains changes, not full table state. A snapshot can’t stream future changes.
Snapshot Lifecycle
The exported snapshot has a specific lifetime, defined by the PostgreSQL replication protocol:
The identifier of the snapshot exported by the command is valid until a new command is executed on this connection or the replication connection is closed.
In practice, a CDC system uses two connections:
- Replication connection — creates the slot, holds the snapshot alive
- Normal SQL connection — opens a
REPEATABLE READ READ ONLYtransaction, imports the snapshot withSET TRANSACTION SNAPSHOT, reads all tables
Once the export finishes, the SQL connection commits. Then the replication connection issues START_REPLICATION, which releases the snapshot and begins WAL streaming.
1
2
3
4
5
6
7
8
9
10
Replication connection SQL connection
────────────────────── ──────────────
CREATE_REPLICATION_SLOT
→ snapshot exported, WAL retention begins
BEGIN REPEATABLE READ READ ONLY
SET TRANSACTION SNAPSHOT '<name>'
SELECT * FROM tables...
COMMIT
START_REPLICATION
→ snapshot released, WAL streaming begins
The WAL retention is separate from the snapshot — it’s a property of the slot itself and lasts as long as the slot exists and hasn’t confirmed past those segments.
This pattern is documented by PostgreSQL as the intended use case for exported snapshots:
When a new replication slot is created using the streaming replication interface, a snapshot is exported which shows exactly the state of the database after which all changes will be included in the change stream. This can be used to create a new replica by using SET TRANSACTION SNAPSHOT to read the state of the database at the moment the slot was created.
Why REPEATABLE READ READ ONLY?
The snapshot transaction must use REPEATABLE READ because SET TRANSACTION SNAPSHOT requires it. Under READ COMMITTED, each statement gets its own fresh snapshot, so pinning to an external snapshot would be meaningless — the next SELECT would ignore it. REPEATABLE READ freezes the snapshot for the entire transaction.
SERIALIZABLE would also work but adds unnecessary overhead — it tracks read/write dependencies and can abort transactions. Since the snapshot transaction only reads, there are no write conflicts to detect.
READ ONLY prevents accidental writes, allows PostgreSQL to optimize the transaction (no write locks or WAL entries needed), and makes the intent explicit: this is a data export, not a mutation.
Hands-On Demo
Start a PostgreSQL 17 instance with logical replication enabled:
1
docker run --name pg-test -e POSTGRES_PASSWORD=test -p 5432:5432 -d postgres:17 -c wal_level=logical
Connect and create test data:
1
2
3
4
5
psql "postgresql://postgres:test@localhost:5432/postgres"
CREATE TABLE demo (id int PRIMARY KEY, name text);
INSERT INTO demo VALUES (1, 'alice'), (2, 'bob');
CREATE PUBLICATION demo_pub FOR TABLE demo;
Demo 1: READ COMMITTED vs REPEATABLE READ
Terminal 1 — start a REPEATABLE READ transaction:
1
2
3
4
5
6
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM demo;
-- id | name
-- ----+-------
-- 1 | alice
-- 2 | bob
Terminal 2 — insert a row and commit:
1
INSERT INTO demo VALUES (3, 'charlie');
Terminal 1 — query again inside the same transaction:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * FROM demo;
-- id | name
-- ----+-------
-- 1 | alice
-- 2 | bob
-- charlie is NOT visible — the snapshot is frozen
COMMIT;
-- now outside the transaction:
SELECT * FROM demo;
-- id | name
-- ----+-------
-- 1 | alice
-- 2 | bob
-- 3 | charlie
-- charlie is visible now
Under READ COMMITTED, the second SELECT inside the transaction would have shown charlie.
Demo 2: SET TRANSACTION SNAPSHOT with a Replication Slot
Terminal 1 — open a replication connection and create a slot:
1
2
3
4
5
psql "postgresql://postgres:test@localhost:5432/postgres?replication=database"
CREATE_REPLICATION_SLOT demo_slot LOGICAL pgoutput;
-- note the snapshot_name in the output (e.g. 00000003-00000001-1)
-- keep this connection open — the snapshot is only valid while it lives
Terminal 2 — insert a row AFTER slot creation:
1
2
psql "postgresql://postgres:test@localhost:5432/postgres"
INSERT INTO demo VALUES (4, 'diana');
Terminal 3 — attach to the exported snapshot:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
psql "postgresql://postgres:test@localhost:5432/postgres"
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
SET TRANSACTION SNAPSHOT '<snapshot_name>'; -- use the name from Terminal 1
SELECT * FROM demo;
-- id | name
-- ----+---------
-- 1 | alice
-- 2 | bob
-- 3 | charlie
-- diana is NOT visible — the snapshot is from before her insert
COMMIT;
SELECT * FROM demo;
-- now diana is visible
The snapshot sees the database exactly as it was when the replication slot was created. The WAL stream from that slot would deliver diana’s insert as a change event. Together, they cover the full timeline with no gaps and no overlaps.
Cleanup
1
2
3
SELECT pg_drop_replication_slot('demo_slot');
DROP PUBLICATION demo_pub;
DROP TABLE demo;
1
docker rm -f pg-test
