Serializable isolation in PostgreSQL - Step-by-Step Execution

Concept Flow - Serializable isolation

Start Transaction

↓

Set Isolation Level: Serializable

↓

Execute Queries

↓

Check for Conflicts

↓

No Conflicts

↓

Commit

↓

End Transaction

The transaction starts, sets isolation to serializable, runs queries, checks for conflicts, and either commits or aborts to ensure full isolation.

Execution Sample

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance > 1000;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

This transaction reads and updates accounts under serializable isolation, ensuring no conflicting concurrent changes.

Execution Table

Step	Action	Query/Check	Result	Transaction State
1	Begin transaction	BEGIN;	Transaction started	Active
2	Set isolation level	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	Isolation set to serializable	Active
3	Execute SELECT	SELECT * FROM accounts WHERE balance > 1000;	Rows returned with balance > 1000	Active
4	Execute UPDATE	UPDATE accounts SET balance = balance - 100 WHERE id = 1;	Row updated	Active
5	Check for conflicts	Conflict detection at commit	No conflicts detected	Active
6	Commit transaction	COMMIT;	Transaction committed successfully	Committed

💡 Transaction commits successfully as no serialization conflicts were detected.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Transaction State	None	Active	Active	Committed
Isolation Level	Default	Serializable	Serializable	Serializable
Accounts Table (id=1 balance)	1500	1500	1400	1400

Key Moments - 2 Insights

Why might a transaction abort under serializable isolation?

If a conflict is detected at commit time (see step 5 in execution_table), the transaction aborts to prevent anomalies.

Does serializable isolation block reads during the transaction?

No, reads proceed normally but conflicts are checked at commit (step 5), ensuring serializability without blocking reads.

Visual Quiz - 3 Questions

Look at the execution_table, what is the transaction state after executing the UPDATE query?

A Committed

B Aborted

C Active

D Idle

💡 Hint

Check the 'Transaction State' column at step 4 in execution_table.

At which step does the system check for conflicts that might cause the transaction to abort?

A Step 4

B Step 5

C Step 3

D Step 6

💡 Hint

Look for 'Check for conflicts' in the Action column of execution_table.

If a conflict was detected at step 5, what would be the next action?

A Abort and rollback transaction

B Commit transaction

C Continue executing queries

D Set isolation level again

💡 Hint

Refer to the concept_flow where conflicts lead to abort and rollback.

Concept Snapshot

Serializable isolation ensures transactions appear to run one after another.
Set with: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Conflicts are detected at commit time.
If conflicts occur, transaction aborts and rolls back.
Prevents anomalies like dirty reads, non-repeatable reads, and phantom reads.

Full Transcript

Serializable isolation is the strictest transaction isolation level in PostgreSQL. When a transaction starts, it sets the isolation level to serializable. The transaction executes queries normally but the system tracks conflicts. At commit time, PostgreSQL checks if any concurrent transactions caused conflicts that could break serializability. If no conflicts are found, the transaction commits successfully. If conflicts are detected, the transaction aborts and rolls back to maintain data integrity. This isolation prevents anomalies such as dirty reads, non-repeatable reads, and phantom reads, ensuring transactions behave as if they ran one after another.