Just like Atomicity; Isolation is also important principle of ACID. It ensures multiple transactions can work concurrently without effecting with each other.
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
Each isolation level creates a different kind of anomaly. Three anomalies are:
- Dirty Read
- Non-Repeatable Read
- Phantom Read
Let’s understand each anomaly.
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.
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.
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.
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.
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.
At this level, the transaction will make sure that when it reads a row, it will remain unchanged while it is running. This level dirty reads and non-repeatable reads, but not phantom read. For example, transaction A reads the table, and while it is reading the table, transaction B inserts a new row in the table. So transaction A will see the new row.
In Repeatable Read the value of all attributes will remain unchanged while the transaction is running.
This is the default isolation level for MySQL.
At this isolation level, every query in a transaction will see a version of the table that was created when the transaction started.
It states that all transactions be executed serially, so there will be no concurrency.
See the table which isolation level has which anomaly,