Perfect! Let’s do a real SQL example where we:

• Cause a Dirty Read at low isolation.
• Fix it by changing the isolation level.

Step-by-Step Dirty Read Example

Assume we have a simple Accounts table:

CREATE TABLE Accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

INSERT INTO Accounts (account_id, balance) VALUES (1, 1000.00);

We have:

account_id	balance
1	1000.00

Now Simulate Two Transactions

Transaction A (updates balance, but does NOT commit yet):

-- Transaction A
START TRANSACTION;

UPDATE Accounts
SET balance = 0
WHERE account_id = 1;
-- DO NOT COMMIT YET!

Transaction B (tries to read the balance at the same time):

✅ In READ UNCOMMITTED (lowest isolation level):

-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

SELECT balance FROM Accounts WHERE account_id = 1;

🎯 Result:

• Transaction B sees balance = 0, even though A hasn’t committed!
• This is a dirty read — Transaction B sees uncommitted data!

Now Rollback Transaction A:

ROLLBACK;  -- Undo the update

✅ The balance is back to 1000.00 — but Transaction B already saw the wrong value 0!

🛡️ How to Fix It

Use a higher isolation level — for example, READ COMMITTED (default in most systems):

-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT balance FROM Accounts WHERE account_id = 1;

✅ Now:

• Transaction B will wait until Transaction A commits or rollbacks.
• It won’t see uncommitted data.
• No dirty read happens.

Key Point

Isolation Level	Behavior
READ UNCOMMITTED	Sees uncommitted data → dirty reads happen.
READ COMMITTED (or higher)	Waits for commit → no dirty reads.

Full Flow Summary

1. A starts transaction and updates balance to 0.
2. B at READ UNCOMMITTED sees balance = 0 (dirty read!).
3. A rolls back — but B already saw wrong value.
4. 🚫 Data inconsistency — BAD.

✅ Solution: Use READ COMMITTED or higher.

Real-Life Analogy

Imagine seeing a restaurant’s bill while the waiter is still writing it — not finalized — and assuming it’s final.
Later, the waiter throws it away and makes a new bill — but you already made decisions based on the wrong bill!