Transaction Isolation in Databases

Transaction Isolation in Databases — Explained with Real Examples

If you’ve built anything that talks to a database, you’ve probably used transactions. But once concurrency comes into play — say, two users updating their carts or modifying the same inventory item — things get tricky.

To deal with this, databases use isolation levels. In Go or distributed systems, these are often represented like this:

const (
    LevelDefault IsolationLevel = iota
    LevelReadUncommitted
    LevelReadCommitted
    LevelWriteCommitted
    LevelRepeatableRead
    LevelSnapshot
    LevelSerializable
    LevelLinearizable
)

Each of these isolation levels is meant to prevent certain types of concurrency bugs. To understand when and why to use them, we need to talk about the anomalies they guard against.

Common Transaction Anomalies

1. Dirty Read

What it is:
Reading data written by another transaction that hasn’t committed yet.

Example:
Transaction A sets balance = 50
Transaction B reads it before A commits
Then A rolls back — B saw a value that never existed

2. Dirty Write

What it is:
Two transactions update the same row before either commits.

Example:
Transaction A sets stock = 8
Transaction B sets stock = 10
Both commit — the last one overwrites the other, possibly losing valid updates

3. Non-Repeatable Read

What it is:
Reading the same row twice in the same transaction gives different results.

Example:
Transaction A reads price = 100
Transaction B updates price to 120 and commits
Transaction A reads again and sees price = 120

4. Phantom Read

What it is:
You query a set of rows multiple times in the same transaction, and the result set changes.

Example:
Transaction A: SELECT * FROM orders WHERE status = 'pending'
Transaction B adds a new pending order and commits
Transaction A runs the same query and now sees one more row

Isolation Levels in Go

Here’s what each isolation level means, along with when to use it.

LevelDefault = 0

Use the database or driver’s default isolation level.
Safe if you’re unsure or if you’re okay with what your DB provides out of the box.
Usually maps to ReadCommitted in most databases.

LevelReadUncommitted = 1

• Allows everything: dirty reads, dirty writes, phantom reads
• You can read uncommitted changes from other transactions

Use when:
You’re optimizing for speed over accuracy, maybe for internal analytics or logging

LevelReadCommitted = 2

• Blocks dirty reads
• Still allows non-repeatable reads and phantom reads
• Common default in many systems like PostgreSQL

Use when:
You want to avoid reading half-completed changes, but you’re okay if data shifts during the transaction

LevelWriteCommitted = 3

• Less common; not part of standard SQL
• Used in some distributed databases to ensure changes are only visible after being fully committed

Use when:
You’re working in a system where consistency on writes is more important than reads

LevelRepeatableRead = 4

• Blocks dirty and non-repeatable reads
• Still allows phantom reads

Use when:
You need reliable reads of the same rows within a transaction. Useful for billing, inventory calculations, etc.

LevelSnapshot = 5

• Uses a consistent snapshot of the database state
• Reads don’t block writes and vice versa
• Often seen in MVCC-based systems like PostgreSQL

Use when:
You want a clean view of the data as it existed when the transaction started, without interfering with others

LevelSerializable = 6

• The strictest SQL-standard isolation
• Prevents all anomalies
• Transactions behave as if they were run one at a time

Use when:
Correctness is absolutely critical. Think: ledgers, accounting, anything involving money transfers

LevelLinearizable = 7

• Strongest level, even stricter than serializable
• Guarantees real-time ordering, often across distributed nodes
• Every read sees the most recent committed write, globally

Use when:
You’re building distributed coordination services like etcd, leader election, or locking systems

Summary Table

What is MVCC?

MVCC stands for Multi-Version Concurrency Control. It’s a way databases let multiple transactions happen at once without locking each other out.

Instead of locking rows during reads or writes, the database keeps multiple versions of data:

• Each transaction sees a snapshot of the database as it existed when it started.
• Writers don’t block readers, and readers don’t block writers.

Example:

Transaction A reads balance = 100
Transaction B updates balance = 50 and commits
Transaction A still sees balance = 100 because it started before B committed

MVCC helps achieve consistent reads with better performance and is the basis of Snapshot or Repeatable Read isolation in systems like PostgreSQL.

Final Thoughts

Most of the time, ReadCommitted or RepeatableRead gives a solid balance of safety and performance. But when correctness matters — like in financial apps — don’t hesitate to use stronger levels like Serializable or Snapshot.

And if you’re working in distributed systems, understand when to reach for Linearizable. It can save you from nasty bugs you don’t want to debug in production.