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.

Practice

1. Which transaction isolation level in PostgreSQL allows a transaction to see only committed data at the time each query starts, but can see different data if the same query is run again within the same transaction?

easy

A. SERIALIZABLE

B. REPEATABLE READ

C. READ COMMITTED

D. READ UNCOMMITTED

Solution

1. Step 1: Understand READ COMMITTED behavior

   READ COMMITTED shows only data committed before each query starts, so data can change between queries in the same transaction.

2. Step 2: Compare with other levels

   REPEATABLE READ and SERIALIZABLE keep a consistent snapshot for the whole transaction, so data does not change between queries.

3. Final Answer:

   READ COMMITTED -> Option C

4. Quick Check:

   READ COMMITTED = sees committed data per query [OK]

Hint: READ COMMITTED sees latest committed data per query [OK]

Common Mistakes:

• Confusing REPEATABLE READ with READ COMMITTED
• Thinking SERIALIZABLE allows data changes mid-transaction
• Assuming READ UNCOMMITTED exists in PostgreSQL

2. Which of the following is the correct SQL command to set the transaction isolation level to SERIALIZABLE in PostgreSQL?

easy

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B. SET ISOLATION LEVEL = SERIALIZABLE;

C. BEGIN TRANSACTION ISOLATION SERIALIZABLE;

D. SET TRANSACTION LEVEL SERIALIZABLE;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   The correct syntax is SET TRANSACTION ISOLATION LEVEL followed by the level name.

2. Step 2: Check each option

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches the correct syntax exactly. Others have incorrect keywords or missing parts.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option A

4. Quick Check:

   Correct SET TRANSACTION syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; [OK]

Hint: Use full phrase: SET TRANSACTION ISOLATION LEVEL [OK]

Common Mistakes:

• Omitting 'TRANSACTION' keyword
• Using '=' sign incorrectly
• Starting with BEGIN instead of SET

3. Consider two concurrent transactions in PostgreSQL using the REPEATABLE READ isolation level. Transaction A reads a row, then Transaction B updates and commits that row. What will Transaction A see if it reads the same row again before committing?

medium

A. The updated row from Transaction B

B. The original row before Transaction B's update

C. An error due to concurrent update

D. No row, because it is locked

Solution

1. Step 1: Understand REPEATABLE READ snapshot

   REPEATABLE READ provides a consistent snapshot for the whole transaction, so it sees data as it was at the start.

2. Step 2: Apply to scenario

   Transaction A will see the original row even after Transaction B commits an update, because its snapshot does not change.

3. Final Answer:

   The original row before Transaction B's update -> Option B

4. Quick Check:

   REPEATABLE READ = consistent snapshot [OK]

Hint: REPEATABLE READ shows data as of transaction start [OK]

Common Mistakes:

• Assuming it sees latest committed data mid-transaction
• Expecting an error or lock blocking read
• Confusing with READ COMMITTED behavior

4. You wrote this command to set the isolation level but get an error: SET TRANSACTION LEVEL = READ COMMITTED; What is the error and how to fix it?

medium

A. Syntax error: remove '=' and use 'ISOLATION' keyword

B. Wrong isolation level name, use 'READ COMMIT' instead

C. Missing semicolon at end of statement

D. SET TRANSACTION cannot be used inside a transaction

Solution

1. Step 1: Identify syntax error

   The command incorrectly uses '=' and omits 'ISOLATION' keyword.

2. Step 2: Correct syntax

   The correct command is SET TRANSACTION ISOLATION LEVEL READ COMMITTED; without '='.

3. Final Answer:

   Syntax error: remove '=' and use 'ISOLATION' keyword -> Option A

4. Quick Check:

   Correct syntax requires 'ISOLATION' and no '=' [OK]

Hint: No '=' sign; use 'ISOLATION' keyword in SET TRANSACTION [OK]

Common Mistakes:

• Using '=' sign in SET TRANSACTION
• Misspelling isolation level names
• Trying to set isolation level outside allowed scope

5. You want to ensure that two concurrent transactions in PostgreSQL never see inconsistent data and avoid phantom reads. Which isolation level should you choose and why?

hard

A. READ UNCOMMITTED, because it allows maximum concurrency

B. REPEATABLE READ, because it prevents non-repeatable reads but allows phantoms

C. READ COMMITTED, because it is fastest and avoids dirty reads

D. SERIALIZABLE, because it fully isolates transactions preventing phantoms

Solution

1. Step 1: Understand phantom reads and isolation levels

   Phantom reads occur when new rows appear in repeated queries within a transaction.

2. Step 2: Match isolation level to requirement

   SERIALIZABLE prevents phantom reads by fully isolating transactions, ensuring consistency.

3. Final Answer:

   SERIALIZABLE, because it fully isolates transactions preventing phantoms -> Option D

4. Quick Check:

   SERIALIZABLE = no phantoms, full isolation [OK]

Hint: Use SERIALIZABLE to prevent phantom reads fully [OK]

Common Mistakes:

• Choosing REPEATABLE READ and expecting no phantoms
• Thinking READ COMMITTED prevents phantoms
• Confusing READ UNCOMMITTED as safe option