Isolation Levels

Introduction

Transactions running concurrently can see each other's partial work — unless prevented by an isolation level. Standard SQL defines: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

Understanding the topic

Core concepts to understand:

• Read Uncommitted: dirty reads possible (rare in PG).
• Read Committed: default in PG/Oracle; no dirty reads.
• Repeatable Read: default in MySQL; consistent reads in tx.
• Serializable: as if transactions ran one at a time.
• Higher isolation → more locking / aborts → less throughput.

Syntax reference

Visual workflow / architecture:

Anomalies prevented at each level:

                        Dirty  Non-rep   Phantom
Read Uncommitted          ✗       ✗        ✗
Read Committed            ✓       ✗        ✗
Repeatable Read           ✓       ✓        ✗*
Serializable              ✓       ✓        ✓

Real-world use

Banks set Serializable for ledger; SaaS dashboards stay at Read Committed for speed; ETL jobs sometimes use Repeatable Read.

Best practices

• Default is fine for most apps; bump for money-critical paths.
• Always handle serialization failures with retry.
• Document chosen levels per service.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Four isolation levels.
• Q2. Dirty read example.
• Q3. Phantom read example.
• Q4. Default in Postgres vs MySQL.
• Q5. Cost of Serializable.