Understanding Database Transaction Isolation Levels and MySQL Experiments

In a database, a transaction is a set of operations that must either all succeed or all be rolled back, ensuring atomicity. The transaction lifecycle ends with a commit when all steps succeed or a rollback when any step fails.

The ACID properties of a transaction are:

Atomicity : All operations in the transaction are performed as a single unit; they either all complete or none do.

Isolation : Transactions are isolated from each other, preventing interference.

Durability : Once a transaction is committed, its changes are permanent.

Consistency : The database moves from one consistent state to another; any failure leaves the database unchanged.

MySQL defines four isolation levels that control the visibility of changes between transactions:

Read Uncommitted : Transactions can see uncommitted changes from other transactions (dirty reads). This level is rarely used in production.

Read Committed : Transactions see only changes that have been committed. This level prevents dirty reads but allows non‑repeatable reads.

Repeatable Read (MySQL default): Guarantees that repeated reads within the same transaction return the same rows, preventing non‑repeatable reads; phantom reads are avoided using MVCC.

Serializable : Provides the highest isolation by forcing transactions to execute sequentially, eliminating phantom reads but potentially causing lock contention.

The following experiments illustrate the behavior of each isolation level.

Experiment 1: Read Uncommitted

Set isolation level:

set session transaction isolation level Read Uncommitted;

Steps:

Transaction A starts and selects data.

Transaction B starts, updates a row, but does not commit.

Transaction A selects the same row and reads the uncommitted value (dirty read).

Transaction B rolls back.

Transaction A selects again and sees the original value.

Conclusion: With Read Uncommitted , Transaction A can read data that Transaction B has not committed, demonstrating dirty reads.

Experiment 2: Read Committed

Set isolation level:

set session transaction isolation level Read Committed;

Steps:

Transaction A starts and selects data.

Transaction B starts, updates a row, and does not commit.

Transaction A selects the same row and still sees the old committed value.

Transaction B commits.

Transaction A selects again and now sees the new value.

Conclusion: Read Committed prevents dirty reads but allows non‑repeatable reads because the data can change between two reads in the same transaction.

Experiment 3: Repeatable Read

Set isolation level:

set session transaction isolation level repeatable read;

Steps:

Transaction A starts and selects data.

Transaction B starts, updates a row, but does not commit.

Transaction A selects again and still sees the original data (repeatable read).

Transaction B commits.

Transaction A selects once more and still sees the original data because the snapshot was taken at the start of the transaction.

Transaction B inserts a new row and commits.

Transaction A selects again and still does not see the newly inserted row until it ends.

Conclusion: Repeatable Read ensures that a transaction sees a consistent snapshot of the data, preventing both dirty and non‑repeatable reads; phantom reads are avoided by MVCC.

Experiment 4: Serializable

Set isolation level:

set session transaction isolation level serializable;

Steps:

Transaction A starts and reads data.

Transaction B attempts to insert a row but is blocked until Transaction A commits.

Transaction A commits.

Transaction B proceeds with the insert.

Conclusion: Serializable provides full isolation by locking the affected rows or tables, ensuring no other transaction can modify the data until the first transaction finishes, at the cost of reduced concurrency.

Overall, the article demonstrates how each isolation level affects data visibility and consistency, helping developers choose the appropriate level for their use case.