Transaction isolation levels in PostgreSQL - Step-by-Step Execution

Concept Flow - Transaction isolation levels

Start Transaction

↓

Set Isolation Level

↓

Execute Queries

↓

Check for Conflicts

↓

Read Uncommitted

↓

Serializable

↓

Commit or Rollback

This flow shows starting a transaction, setting its isolation level, executing queries, checking for conflicts based on the isolation level, and then committing or rolling back.

Execution Sample

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

A transaction starts, sets isolation to REPEATABLE READ, reads data, updates it, then commits.

Execution Table

Step	Action	Isolation Level	Data Read	Data Updated	Conflict Check	Result
1	BEGIN transaction	REPEATABLE READ	N/A	N/A	N/A	Transaction started
2	Set isolation level	REPEATABLE READ	N/A	N/A	N/A	Isolation level set
3	SELECT accounts WHERE id=1	REPEATABLE READ	Balance=500	N/A	N/A	Data read consistent snapshot
4	UPDATE accounts balance -100	REPEATABLE READ	Balance=500	Balance=400	Check for write conflicts	No conflict, update allowed
5	COMMIT transaction	REPEATABLE READ	N/A	Balance=400	N/A	Transaction committed
6	Transaction ends	N/A	N/A	N/A	N/A	End of transaction

💡 Transaction ends after commit; data changes are saved with REPEATABLE READ isolation ensuring consistent reads during transaction.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Transaction State	Not started	Active	Active	Committed
Isolation Level	Default	REPEATABLE READ	REPEATABLE READ	N/A
Account Balance	500	500	400	400

Key Moments - 3 Insights

Why does the SELECT at step 3 always see balance=500 even if another transaction changes it?

Because REPEATABLE READ isolation takes a snapshot at transaction start, so all reads see the same data version during the transaction (see execution_table step 3).

What happens if another transaction tries to update the same account before commit?

The conflict check at step 4 prevents simultaneous conflicting writes; one transaction waits or fails to keep data consistent.

Why must we COMMIT to save changes?

Until COMMIT (step 5), changes are not visible to others; COMMIT finalizes and makes updates permanent.

Visual Quiz - 3 Questions

Look at the execution_table, what is the account balance after step 4?

A 400

B 500

C 600

D 100

💡 Hint

Check the 'Data Updated' column at step 4 in the execution_table.

At which step does the transaction isolation level get set?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look at the 'Action' and 'Isolation Level' columns in the execution_table.

If the isolation level was READ COMMITTED instead of REPEATABLE READ, what would change at step 3?

A Data read would be locked and never change

B Data read might reflect committed changes from other transactions

C Transaction would fail immediately

D No changes; same snapshot as REPEATABLE READ

💡 Hint

Consider how READ COMMITTED allows seeing committed changes during the transaction.

Concept Snapshot

Transaction Isolation Levels in PostgreSQL:
- Control visibility of data changes during transactions
- Levels: READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE
- Higher levels prevent more concurrency issues but may reduce performance
- Use SET TRANSACTION ISOLATION LEVEL before queries
- COMMIT finalizes changes, ROLLBACK cancels
- REPEATABLE READ ensures consistent snapshot during transaction

Full Transcript

This visual execution shows how a PostgreSQL transaction runs with the REPEATABLE READ isolation level. The transaction begins, sets the isolation level, reads data, updates it, and commits. The key point is that the SELECT reads a consistent snapshot of data at transaction start, so even if other transactions change data, this transaction sees the original values. Updates check for conflicts to avoid data corruption. Committing saves changes permanently. The execution table tracks each step, showing actions, data read and updated, and conflict checks. Variable tracking shows transaction state, isolation level, and account balance changes. Key moments clarify why snapshots matter, how conflicts are handled, and why commit is needed. The quiz tests understanding of these steps and isolation effects. This helps beginners see how isolation levels control data visibility and consistency in real transactions.