4 types of Isolation

Okay! Imagine you and your friend are playing with LEGO blocks. You both are building your own houses at the same time.

What is Isolation?

It makes sure that when you are building your house, your friend can’t take your blocks while you’re still placing them. And you can’t take theirs either!

Without isolation, your friend might grab a block from your house before you finish, and your house might look weird or broken. But with isolation, you both finish your houses properly without messing each other up.

That’s why databases use isolation—so one person’s work doesn’t mess up another person’s work while it's still happening! 😊

I = Isolation (in ACID)

• Definition: Isolation ensures that transactions are executed independently, preventing them from interfering with each other.
• Why It Matters: If multiple transactions run at the same time, one transaction shouldn’t see partial results from another unfinished transaction.

Example Scenario

Imagine a banking system where:

1. You transfer $100 from Account A to Account B.
2. At the same time, another transaction tries to read Account A’s balance.

Without Isolation, the second transaction might see a half-updated state (money deducted from A but not yet added to B), leading to incorrect behavior.

Isolation Levels in Databases

Most databases allow you to choose different levels of isolation, balancing consistency and performance:

1. Read Uncommitted – Transactions can read uncommitted changes from other transactions (dirty reads possible).
2. Read Committed – Transactions only see committed changes (no dirty reads).
3. Repeatable Read – Ensures a transaction sees the same data if read multiple times.
4. Serializable – The highest level; transactions are fully isolated as if they were executed sequentially.

Analogy

Think of isolation like cooking in a shared kitchen:

• If two people cook at the same time, one shouldn’t take an ingredient that the other is still using.
• High isolation = Separate workstations, avoiding interference.
• Low isolation = People grabbing ingredients from each other’s stations, leading to inconsistencies.

A transaction isolation level is defined by the following phenomena:

• Dirty Read – A Dirty read is a situation when a transaction reads data that has not yet been committed. For example, Let’s say transaction 1 updates a row and leaves it uncommitted, meanwhile, Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.
• Non Repeatable read – Non-realatable read occurs when a transaction reads the same row twice and gets a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.
• Phantom Read – Phantom Read occurs when two same queries are executed, but the rows retrieved by the two, are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for Transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.

https://www.youtube.com/watch?v=-gxyut1VLcs