Isolation in ACID

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
  • 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, 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.

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 the table which isolation level has which anomaly,

Isolation LevelDirty ReadsNon-repeatable ReadsPhantom Reads
Read Uncommittedmay occurmay occurmay occur
Read Committeddon’t occurmay occurmay occur
Repeatable Readdon’t occurdon’t occurmay occur
Snapshotdon’t occurdon’t occurdon’t occur
Serializabledon’t occurdon’t occurdon’t occur

Leave a Comment

Your email address will not be published. Required fields are marked *