December 14, 2024 1:39 PM (GMT+5:30)

A fine Saturday evening in a coffee shop…

Alice: “Bob, last weekend I heard something called transaction isolation. Can you explain what that is to me?” she asked Bob with enthusiasm.

Bob: “Ah, transaction isolation! A fascinating topic. Transaction Isolation is the Isolation part of ACID properties of a database. It defines how concurrent transactions should be isolated from each other.” Bob has always been fascinated by databases and loves to talk about them.

Alice: “Oh yes, ACID I remember. But why is isolation needed?”

Bob: “Good question. Imagine you and I share a bank account with a balance of $500. You check the balance and see $500. At the same time, I transfer $400 from it and deposit $100 before you start a transaction. This makes the balance $200.” He started with a good analogy like he always does.

Alice: “Wow, that already sounds chaotic.”

Bob: “Yes. You now try to transfer $300 as you still assume the balance is $500. What should the database do in this case? It may process the request thinking that it has enough balance. But in reality, there isn’t, leading to data inconsistency.”

Alice: “Oh no, sounds terrible! I don’t like my balance to go negative. So, the main goal of isolation is to prevent this type of anomaly, is it?”

Bob: “Actually there can be many more anomalies than this and they are interesting too.” He surely knows how to spark interest in her.

Alice: “Really? Like what? Can you explain them to me?” she’s definitely curious to know more. She didn’t even notice her coffee going cold.

Bob: “Of course! Let’s start with dirty reads.”

Dirty Reads

Bob: “A dirty read happens when a transaction T1 sees a change that has not yet been committed by the other transaction T2 but was modified by T2.”

Alice: “Why is it called ‘dirty’?”

Bob: “It’s called dirty because the other transaction T2 might rollback or abort the transaction and the data read by the transaction T1 never existed.”

Alice: “What happens if T1 does something with the data that has been read from T1 and commits it later?” she’s very good at this critical thinking.

Bob: “That’s what we call a ‘dirty write’”