Just like Atomicity; Isolation is also important principle of ACID. It ensures multiple transactions can work independently of each other as well as data integrity and consistency.
Transection A and Transection B working independently.
Each database has specific level of isolation, you can change by yourself. There are four kinds of isolation levels:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Snapshot
- Serializable
Each isolation level creates a different kind of anomaly. Three anomalies are:
- Dirty Read
- Non-Repeatable Read
- Phantom Read
Let’s understand each anomaly.
Dirty Read
It occurs when a transection reads uncommitted data. For example, Transaction A updates the row but is not committed. Transaction B reads the data. Transaction A rolled back, and Transaction B just read data that is considered Dirty Read.
Non-Repeatable Read
It occurs when a transaction reads different value for a specific attribute. Let’s say for example, transaction A reads table and transaction B updates the data and committed it. Then transaction A reads the data again and see a different value, the old value is not repeating.
Phantom Read
It occurs when two (same or different) queries return different rows. For example, transaction A reads table and transaction B insert a new data. Then transaction A fetch the table again it will see a new row inserted.
Inserting a new row or deleting a row can create phantom read anomaly.
Now let’s understand isolation level and which level can create which anomaly.
Read Uncommitted
It is the lowest isolation level. It means you can see the value of a data changed by multiple transaction no matter it is committed or not. So dirty read can exist in this level.
Read Committed
It means each query in a transaction will only see the committed value made by other transactions. Non-repeatable read anomaly can exist in this level.
Repeatable Read
At this level, transactions maintain row consistency during execution, allowing dirty and non-repeatable reads but not phantom reads.
In Repeatable Read the value of all attributes will remain unchanged while the transaction is running.
Let’s say WordCamp Sylhet 2024 registration is happening. People are ready to register themself.
After 3 minutes only 1 seat left. And now 5 people are trying to get that one slot at same time.
Five people just made the request. Now, MySQL by default always carries out one by one; it processes request number 1 first, and after finishing that, the database table updates the slot for request number 1. Then request 2 will see there is no slot, then same for number 3, 4 and 5.
This is the default isolation level for MySQL.
Snapshot
At this isolation level, every query in a transaction will see a version of the table that was created when the transaction started.
Serializable
It states that all transactions be executed serially, so there will be no concurrency.
See let’s summarise which isolation level has which anomaly,
| Isolation Level | Dirty Reads | Non-repeatable Reads | Phantom Reads |
| Read Uncommitted | may occur | may occur | may occur |
| Read Committed | don’t occur | may occur | may occur |
| Repeatable Read | don’t occur | don’t occur | may occur |
| Snapshot | don’t occur | don’t occur | don’t occur |
| Serializable | don’t occur | don’t occur | don’t occur |