Isolation and Concurrency

Transactions: Isolation and Concurrency

🏦 The Bank Story: When Multiple People Touch the Same Money

Imagine you have a piggy bank at home. You’re the only one who uses it, so there’s no problem. But what if your whole family starts using the same piggy bank at the same time?

• Mom is counting how much is inside
• Dad is adding $20
• Your sister is taking $5 for ice cream

Chaos! Everyone might see wrong amounts. Someone might count money that isn’t really there yet.

This is exactly what happens in databases when many people (or programs) try to work with the same data at the same time. We call this concurrency.

Isolation is the magic shield that protects each person’s work from getting mixed up with others.

🎭 The Isolation Levels: Four Shields of Protection

Think of isolation levels like doors with different locks:

👻 The Three Ghosts: Problems That Haunt Databases

Before we learn about each level, meet the three troublemakers:

1. Dirty Read 🗑️

Reading something that might disappear

Imagine your friend writes “I’ll give you $10” on a note, but then erases it before giving you money. If you already told everyone you’re getting $10… oops!

-- Transaction A starts
UPDATE accounts
SET balance = 500
WHERE id = 1;
-- NOT committed yet!

-- Transaction B reads
SELECT balance FROM accounts
WHERE id = 1;
-- B sees 500, but A might cancel!

2. Non-Repeatable Read 🔄

Same question, different answer

You ask “How much candy is in the jar?” and someone says “10 pieces.” You ask again 5 seconds later, and now they say “7 pieces.” The jar changed while you were thinking!

-- Transaction A reads first
SELECT balance FROM accounts
WHERE id = 1;
-- Gets 1000

-- Transaction B updates and commits
UPDATE accounts
SET balance = 500
WHERE id = 1;
COMMIT;

-- Transaction A reads again
SELECT balance FROM accounts
WHERE id = 1;
-- Now gets 500! Different!

3. Phantom Read 👻

New things appear from nowhere

You count all the red cars in the parking lot: “5 cars!” But while you’re writing it down, 3 more red cars park. You count again: “8 cars!” Ghosts?

-- Transaction A counts
SELECT COUNT(*)
FROM products
WHERE price > 100;
-- Gets 5

-- Transaction B inserts new row
INSERT INTO products
VALUES (6, 'New Item', 150);
COMMIT;

-- Transaction A counts again
SELECT COUNT(*)
FROM products
WHERE price > 100;
-- Now gets 6! A phantom appeared!

🔓 READ UNCOMMITTED: The Open Door

No protection at all. Like a room with no door.

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED;

What happens:

• You can see other people’s work before they’re done
• Very fast, but very risky
• Can experience: Dirty Reads ✓, Non-Repeatable Reads ✓, Phantom Reads ✓

Real-life example: Reading someone’s unfinished text message over their shoulder. They might delete everything they wrote!

graph TD
    A["Transaction A writes 500"] --> B["NOT committed yet"]
    B --> C["Transaction B reads 500"]
    C --> D["Transaction A ROLLBACK!"]
    D --> E["Transaction B has wrong data!"]
    style E fill:#ff6b6b,color:#fff

Use when: You only need rough estimates and don’t care about accuracy.

🚪 READ COMMITTED: The Basic Lock

Most popular level. Like a door that locks when someone is changing clothes.

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

What happens:

• You only see changes after they’re committed
• No more dirty reads!
• But you might still get different answers if you ask twice

Problems solved: Dirty Reads ✗ Problems remaining: Non-Repeatable Reads ✓, Phantom Reads ✓

graph TD
    A["Transaction A writes 500"] --> B["NOT committed"]
    B --> C["Transaction B tries to read"]
    C --> D["B sees OLD value 1000"]
    D --> E["A commits"]
    E --> F["NOW B can see 500"]
    style D fill:#4ecdc4,color:#fff

Real-life example: You can only read a document after the author clicks “Save.”

🔒 REPEATABLE READ: The Strong Lock

Your view stays frozen. Like taking a photo of the data.

SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ;

What happens:

• Once you read something, it won’t change during your transaction
• Same question = same answer (guaranteed!)
• But new rows can still sneak in

Problems solved: Dirty Reads ✗, Non-Repeatable Reads ✗ Problems remaining: Phantom Reads ✓

graph TD
    A["Transaction A reads balance: 1000"] --> B["Takes a snapshot"]
    B --> C["Transaction B updates to 500"]
    C --> D["Transaction B commits"]
    D --> E["A reads again"]
    E --> F["Still sees 1000!"]
    style F fill:#667eea,color:#fff

Real-life example: Taking a photo of a chalkboard. Even if someone erases it, your photo doesn’t change.

🏰 SERIALIZABLE: The Bank Vault

Maximum protection. Like everyone takes turns, one at a time.

SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;

What happens:

• Transactions run as if they’re the only one
• No surprises at all
• But it’s the slowest

Problems solved: Dirty Reads ✗, Non-Repeatable Reads ✗, Phantom Reads ✗

graph TD
    A["Transaction A enters vault"] --> B["Door locks"]
    B --> C["Transaction B waits outside"]
    C --> D["A finishes and leaves"]
    D --> E["B enters vault"]
    style B fill:#764ba2,color:#fff

Real-life example: Only one person can use the bathroom at a time. Everyone else waits in line.

📊 The Complete Protection Chart

🎯 Quick Summary: How to Choose

graph TD
    A["What do you need?"] --> B{Need speed?}
    B -->|Yes| C{Okay with wrong data?}
    C -->|Yes| D["READ UNCOMMITTED"]
    C -->|No| E["READ COMMITTED"]
    B -->|No| F{Need perfect data?}
    F -->|Somewhat| G["REPEATABLE READ"]
    F -->|Absolutely| H["SERIALIZABLE"]
    style D fill:#ff6b6b,color:#fff
    style E fill:#4ecdc4,color:#fff
    style G fill:#667eea,color:#fff
    style H fill:#764ba2,color:#fff

Remember this rhyme:

“Uncommitted is fast but may lie, Committed is safe for most things you try, Repeatable keeps your reads the same, Serializable wins the safety game!”

🌟 The Golden Rule

More protection = Slower speed

It’s like choosing between:

• 🏃 Running without looking (fast, might crash)
• 🚶 Walking carefully (slower, very safe)

Most apps use READ COMMITTED because it’s the sweet spot: safe enough and fast enough!