Understanding how multiple transactions work in a database

Just like Atomicity, Isolation is also an important principle of ACID. It ensures that multiple transactions can work independently of each other, maintaining 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 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

For better understanding, let’s think of a scenario. Two transactions called A and B. They perform the following operations,

  • Transaction B is first reads from Table X
  • Transaction A then writes to table X
  • Transaction B reads again from Table X

Isolation Levels

Read Uncommitted

Transaction B can read uncommitted changes made by Transaction A. This means B could see inconsistent or intermediate data (also known as “dirty reads”).

Read Committed

Transaction B can only read data that has been committed. If A is still in progress and hasn’t committed its changes, B will not see these changes.

Repeatable Read

Transaction B will see the same data for its duration, ensuring consistency across multiple reads within the same transaction. However, phantom reads (new rows added or removed by other transactions) can still occur.

Serializable

Each transaction works with its own snapshot of the database, taken at the start of the transaction. Changes made by other transactions are not visible until they commit.

I hope you understand. If you have any questions please comment.

Leave a Comment

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