Hint: Dirty Reads, Phantom Reads, Repeatable Reads, Fractured Reads and Non-repeatable Reads
Early Rant: Left on seen by your Crush? Well, it will be a greater problem if your Database leaves you on read!
Honestly, I don’t get it. We have evolved too much, We are supposed to be just eating and living beautiful lives, when did Databases become such an important part of our evolution.
Topic: Let’s say you are in a flight which is about to take off, your wife calls you and asks what is the balance in your account and you say $100 and after the flight takes off you pay 50$ for a glass of whiskey in the flight while your wife goes out for shopping and tries to pay 100$ at a salon and bang suddenly your wife is mad at you because the transaction declined.
This is what a dirty read is.
A dirty read occurs when transaction T2 reads a data item that was written by transaction T1 before T1 has committed. If T1 later aborts (rolls back), T2 has acted on a value that never officially existed. This happens because Database lets readers see uncommitted data from other transactions. Writes become visible the instant they’re applied in memory, not when the transaction commits.
Before we proceed I would like to talk about two most weakest kryptonite of SQL
1.Read Uncommitted
Rule: a transaction can see changes made by other transactions even if they haven’t committed yet.
2.Read Committed
Rule: a transaction only sees data that has been committed. Each statement sees a fresh snapshot of whatever was committed at the moment that statement started.
These two rules are very important to understand because of the ‘I’ in ACID which stands for Isolation, and they way Isolation changes in different Databases leads to various phenomenon's.
The next one is known as Non-repeatable read, this is rather a tricky one because it tells a different result for the same query within a transaction.
In a more technical sense, A non-repeatable read happens when a transaction reads the same row twice and gets different values, because another transaction modified or deleted that row and committed between the two reads.
Ex: you check the price of a pizza on the menu: $10. You ask a friend a question, look back, and now it says $12. You didn’t misread it. Someone changed the menu while you were deciding.
But one important thing to remember is Non-repeatable reads only involve committed data.
The next one is what scares me the most, it is known as Phantom Read. Because in this you are trying to read something that doesn’t exists yet.
Get Kunalagarwal’s stories in your inbox
Join Medium for free to get updates from this writer.
A phantom read happens when a transaction runs the same predicate-based query (a WHERE condition or range) twice, and the second run returns a different set of rows because another transaction inserted, deleted, or updated rows matching that predicate (condition) and committed in between.
For Example: you count the people in a room: 10. You turn around for a second, and when you count again there are 11. Nobody you counted changed. A new person just appeared, like a ghost.
Now why is Phantom read dangerous?
That last row is the crux. Standard row locks can’t prevent phantoms, because you can’t lock a row that doesn’t exist yet. The database has to protect the gap or the condition itself.
Then we have our Repeatable reads, the most easiest and the safest ones, Repeatable Read is a transaction isolation level that guarantees: if you read a row once, you will get the same value every time you read it again within the same transaction, even if other transactions commit changes to that row in the meantime. It fixes dirty reads and non-repeatable reads, and per the SQL standard it still allows phantoms. But how is Repeatable reads possible? There are two ways:
- Snapshot/MVCC
Before we dive into the way of working, let’s understand what Sanpshot is. A snapshot is a frozen view of the database showing only the data that was committed at a specific moment. A transaction reading from a snapshot sees that frozen view, no matter what other transactions commit afterward and to maintain snapshots we have MVCC which is known as Multi Version Concurrency Control, which means that the databases keep multiple versions of the rows which are trying to be over-written and each transaction is shown the version that was valid for its particular snapshot. So, the database enables repeatable reads by giving your transaction a consistent snapshot, and every read comes from that snapshot. Other transactions’ later commits are invisible to you. Readers don’t block writers and writers don’t block readers.
2. Locking (classic SQL Server)
Every row you read gets a shared lock held until the transaction ends, so writers must wait. This prevents non-repeatable reads, but not phantoms, since rows that don’t exist yet can’t be locked, and this also avoids the problem of the lost updates, A lost update happens when two transactions both read the same value, compute a new value from it, and write it back, so the second write silently overwrites the first. One transaction’s update is lost, as if it never happened.
So to avoid this, Both transactions hold shared locks from their reads, then each tries to upgrade to an exclusive lock for the write. Each waits for the other, so you get a deadlock, and the database kills one transaction. Safe, but via an error.
Isolation Levels
There are 4 standard isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable), with Snapshot Isolation as a common fifth in practice. The higher you go, the safer your data and the lower your concurrency. Serializable is the strongest isolation level. It guarantees that the outcome of running transactions concurrently is the same as if they had run one at a time, in some serial order, with no overlap.
Different Databases implement different isolation levels, most implement repeatable reads and read commited becasue that prevents dirty reads and tries to avoid other phenomenons using various techniques like MVCC and Locking.
Finally Coming to the last and most complex one, Fractured Reads. This is generally not possible in a normal database but only in a sharded or distributed databases.
A fractured read happens when a transaction sees some, but not all, of another transaction’s committed writes. The writes were meant to be one atomic unit, but the reader observes only part of it.
Example: A transation to happen between two shards, Shard 1 has person 1 account and shard 2 contains person 2 account, (Scenario: person 1 trasnfers 100$ to person 2) So, incase of a fractured read one query of debit is successfull which is person 1 account is debited by 100$ but the second query within the same transaction is not committed yet i.e.. crediting the person 2 account with 100$ and a fractured read takes place exactly when a second transaction tries to read person 2 account balance which is currently not updated but person 1’s account has been debited with the amount.
The key difference among other phenomenons is that a fractured read involves committed data, and the problem is a mismatch across items written together, not a change over time. This is usually avoided by Atomic reads, which is Keeping multiple versions plus metadata about which other items each write belongs to. A reader that sees a fractured set detects it and fetches the matching versions in a second round. No locks, so partitions don’t block each other.
This can also be avoided by distributed snapshots, Give every transaction one consistent timestamp across shards, so reads only see states that existed at that timestamp.
Read more on sharding : https://medium.com/@kunalagarwal0202/database-sharding-71d48d8dfaab
