Isolation

Most important commands to remember

• psql -X -d lab — open each independent test session.
• BEGIN ISOLATION LEVEL REPEATABLE READ — retain a transaction snapshot.
• COMMIT — end that transaction so later statements can see newer committed data.

Commands and flags

Command or syntax	Meaning
-X -d lab	Skip psql startup files and select the test database.
CREATE TABLE / INSERT	Create the dedicated shared fixture and its initial row.
PRIMARY KEY / NOT NULL	Require a unique row identifier / a nonnull value.
BEGIN ISOLATION LEVEL REPEATABLE READ	Start a transaction with repeatable-read isolation.
SELECT … WHERE id = 1	Read the fixture row.
UPDATE … SET value = 200	Change that row in session B.
COMMIT / DROP TABLE	End A’s transaction / remove the example table.
-- A1 / ; / \q	SQL session-order comment / statement terminator / quit psql.

Outside an explicit transaction, each successful statement commits under normal psql autocommit behavior.

The concepts that matter

1. Isolation defines what concurrent work can observe

Transactions do not run alone. While one session reads, another may update and commit the same data. Isolation defines which changes each transaction can observe and which concurrent combinations must be prevented.

Atomicity alone does not answer this question. Two individually all-or-nothing transactions can still make incompatible decisions if each reads a view that does not account for the other’s work.

2. A snapshot is a visibility rule

PostgreSQL uses multiversion concurrency control: readers can see suitable row versions instead of simply waiting for every writer. A snapshot determines which transactions’ changes are visible to a query.

It is not a full copied database created for every read. Old row versions and visibility information support the view. Long-running transactions can keep versions needed longer and affect cleanup, so leaving a transaction open has consequences even when it appears idle.

3. Read committed and repeatable read choose different boundaries

Under PostgreSQL’s usual read-committed level, successive statements can see newly committed changes because each statement obtains a new snapshot. Repeating a query inside one transaction can therefore return a newer value.

Repeatable read retains a snapshot established by its first non-transaction-control statement. Later ordinary reads keep that view, while the transaction also sees its own changes. Other sessions can commit newer data without making those versions visible inside the existing snapshot.

4. Serializable protects a stronger correctness condition

Serializable aims for results consistent with some serial execution of committed transactions. PostgreSQL can reject a transaction with a serialization failure when it cannot preserve that guarantee.

The application must be prepared to retry the whole transaction with fresh reads, not just repeat its last statement. Stronger isolation does not eliminate constraints, business rules, or external side-effect coordination. Choose it according to the decisions that concurrent work must preserve.

One small example

Optional: open the first command in terminals A and B, using the same database and schema. Execute A1, then B1, then A2. Stop if table creation fails; do not reuse or drop a pre-existing table. Enter the final quit command in both sessions.

psql -X -d lab

-- A1
CREATE TABLE btc_isolation_lab (id integer PRIMARY KEY, value integer NOT NULL);
INSERT INTO btc_isolation_lab VALUES (1, 100);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT value FROM btc_isolation_lab WHERE id = 1;

-- B1
UPDATE btc_isolation_lab SET value = 200 WHERE id = 1;

-- A2
SELECT value FROM btc_isolation_lab WHERE id = 1;
COMMIT;
SELECT value FROM btc_isolation_lab WHERE id = 1;
DROP TABLE btc_isolation_lab;

\q

A1 should read 100. B1 commits its update independently. A2’s first read should still show 100 inside the existing snapshot; after COMMIT, the next read should show 200. These are expected results for the stated order.

This demonstrates snapshot visibility, not a lost update or serialization failure. The final DROP removes only the table created by A1. If interrupted, finish or roll back open transactions before removing that fixture; closing sessions alone does not remove this ordinary shared table.

Keep this idea: Isolation determines which concurrent changes a transaction can see and whether its decisions remain valid alongside other transactions.