Understanding Isolation Levels in MySQL

Course: MySQL Training

Topic: Understanding Isolation Levels

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Transaction Isolation Levels

Transaction isolation levels are an important part of database transaction management in MySQL. They control how transactions interact when multiple users or applications access and modify the same data at the same time.

In a database application, several transactions may run concurrently. For example, two customers may try to purchase the last available product, two employees may update the same account, or one transaction may read data while another transaction is modifying it. Without appropriate concurrency control, these operations could produce inconsistent or unexpected results.

MySQL uses transaction isolation levels to define which changes made by other transactions can be observed during a transaction. In MySQL's InnoDB storage engine, the four standard isolation levels are:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Each level provides a different balance between consistency, concurrency, and transaction performance. Choosing the appropriate level depends on the application's requirements.

1.1 Learning Objectives

• Understand database transaction isolation and concurrent execution.
• Learn the four isolation levels supported by MySQL InnoDB.
• Understand dirty reads, non-repeatable reads, and phantom reads.
• Configure isolation levels at the session and global levels.
• Set an isolation level for a particular transaction.
• Test isolation behavior using multiple database sessions.
• Understand the role of MVCC, locks, and consistent reads.
• Identify common transaction problems and apply appropriate solutions.

2. Understanding Database Transactions

A transaction is a group of database operations treated as one logical unit of work. Transactions commonly use the following statements:

2.1 Example of a Transaction

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 202;

COMMIT;

This example transfers money between two accounts. The two updates are grouped in one transaction so that they can be committed together. Isolation levels help define how this transaction interacts with other concurrent transactions.

2.2 Why Isolation Is Necessary

Imagine two users accessing a bank account at the same time. One user reads a balance of 10,000 while another transaction changes the balance to 8,000. Depending on the isolation level and the type of read, the first user might see the original value, the updated value, or a value that is not yet committed.

Isolation rules help control what each transaction can observe while the database manages concurrent activity. They do not replace correct application logic, constraints, or transaction design.

3. What Is a Transaction Isolation Level?

A transaction isolation level specifies the visibility rules that apply when a transaction reads data while other transactions may be changing it.

In MySQL, isolation levels affect operations such as ordinary SELECT statements, locking reads, and data modifications. The exact behavior depends on the storage engine and the type of statement. The descriptions in these notes primarily refer to the InnoDB storage engine.

3.1 Main Goals of Isolation

• Prevent transactions from observing changes that should not yet be visible.
• Reduce inconsistent results when data is read more than once.
• Control interactions between concurrent reads and writes.
• Maintain database correctness while allowing useful concurrency.
• Balance consistency requirements against locking and performance costs.

4. Common Concurrency Problems

Understanding concurrency problems makes it easier to understand why isolation levels differ.

4.1 Dirty Read

A dirty read occurs when one transaction reads data written by another transaction that has not yet committed.

Example: Transaction A changes an account balance from 5,000 to 2,000 but has not committed. Transaction B reads 2,000. Transaction A then rolls back, restoring the balance to 5,000. Transaction B has read a value that was never committed.

-- Transaction A
START TRANSACTION;
UPDATE accounts
SET balance = 2000
WHERE account_id = 101;
-- Do not commit yet.

-- Transaction B
SELECT balance
FROM accounts
WHERE account_id = 101;

-- Transaction A
ROLLBACK;

Whether Transaction B can see the uncommitted value depends on the isolation level and read behavior. In InnoDB, ordinary consistent reads at READ UNCOMMITTED may observe uncommitted changes. The higher isolation levels prevent dirty reads.

4.2 Non-Repeatable Read

A non-repeatable read occurs when a transaction reads the same row twice and obtains different committed values because another transaction updated and committed that row between the reads.

Example: Transaction A reads an employee salary of 40,000. Transaction B updates the salary to 45,000 and commits. Transaction A reads the same row again and sees 45,000 under an isolation level that allows the new committed value to become visible to the second read.

4.3 Phantom Read

A phantom read occurs when a transaction repeats a search using the same condition and finds a different set of rows because another transaction inserted or deleted rows matching that condition.

Example: Transaction A runs a query that finds all products priced above 1,000 and receives 10 rows. Transaction B inserts another qualifying product and commits. If Transaction A repeats the query and now receives 11 rows, it has observed a phantom row.

4.4 Lost Update

A lost update occurs when concurrent transactions read and modify the same value in a way that causes one transaction's intended change to be overwritten by another.

For example, two transactions read a balance of 1,000. One calculates a new balance of 900 after a withdrawal of 100, while the other calculates 800 after a withdrawal of 200. If both write absolute values without appropriate coordination, one update may overwrite the other. Isolation levels alone are not a substitute for safe update patterns; atomic SQL updates, locking reads, and suitable transaction design are important.

4.5 Summary of Concurrency Problems

5. The Four Isolation Levels in MySQL

MySQL InnoDB supports four standard isolation levels. They differ in how they handle dirty reads, non-repeatable reads, and phantom reads.

Important: This is a high-level comparison of standard phenomena. InnoDB uses multiversion concurrency control (MVCC) and locking, so the behavior of ordinary consistent reads can differ from locking reads such as SELECT ... FOR UPDATE. Applications should test the exact statements they use.

6. READ UNCOMMITTED Isolation Level

READ UNCOMMITTED is the least restrictive isolation level. It allows a transaction to read changes made by another transaction even when those changes have not been committed.

6.1 Characteristics

• Dirty reads are possible.
• Repeated reads may return different values.
• Repeated searches may return different sets of rows.
• Ordinary consistent reads are generally nonlocking.
• It may be useful for certain approximate reporting scenarios where temporary inconsistencies are acceptable.
• It is generally unsuitable for financial calculations or other operations requiring reliable, committed data.

6.2 Setting READ UNCOMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @@SESSION.transaction_isolation;

This changes the isolation level for the current database session. Use a separate database connection to test concurrent transactions.

6.3 Example Scenario

Suppose Transaction A updates a product price from 500 to 100 but has not committed. Transaction B reads the price as 100. If Transaction A subsequently rolls back, the actual committed price returns to 500. Transaction B has therefore read an uncommitted value.

Best practice: Avoid this isolation level for workflows in which incorrect intermediate values could lead to financial loss, incorrect decisions, or inconsistent application behavior.

7. READ COMMITTED Isolation Level

READ COMMITTED ensures that an ordinary consistent read sees committed data rather than another transaction's uncommitted changes. Each consistent read obtains a fresh snapshot, so data committed by another transaction may become visible between two reads in the same transaction.

7.1 Characteristics

• Dirty reads are prevented.
• Non-repeatable reads can occur.
• Phantom rows can appear in repeated searches.
• Each consistent read uses a fresh snapshot.
• InnoDB generally uses fewer gap locks for ordinary locking operations than it does under REPEATABLE READ.
• It can be appropriate for applications that need committed data but do not require every ordinary read in a transaction to use the same snapshot.

7.2 Setting READ COMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@SESSION.transaction_isolation;

7.3 Example of a Non-Repeatable Read

Use two MySQL sessions connected to the same database and containing the same test data.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO accounts (account_id, balance)
VALUES (101, 5000.00);

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT balance
FROM accounts
WHERE account_id = 101;

The first query returns 5000.00.

Session B:

START TRANSACTION;

UPDATE accounts
SET balance = 4500.00
WHERE account_id = 101;

COMMIT;

Return to Session A:

SELECT balance
FROM accounts
WHERE account_id = 101;

COMMIT;

The second query in Session A can return 4500.00 because READ COMMITTED uses a fresh snapshot for each ordinary consistent read. The row changed between the two queries, resulting in a non-repeatable read.

8. REPEATABLE READ Isolation Level

REPEATABLE READ is the default isolation level for MySQL's InnoDB storage engine. For ordinary, nonlocking consistent reads, InnoDB uses a consistent snapshot established by the first such read in the transaction. Subsequent consistent reads generally continue to see the same snapshot.

8.1 Characteristics

• Dirty reads are prevented.
• Non-repeatable reads are prevented for ordinary consistent reads within the same transaction snapshot.
• Ordinary consistent reads use a stable snapshot, so newly committed rows from other transactions do not appear in those repeated reads.
• Locking reads and data modification statements have different visibility and locking behavior from ordinary consistent reads.
• InnoDB can use record locks, gap locks, and next-key locks for appropriate locking operations.
• It offers a useful balance between consistent reads and concurrency for many transactional applications.

8.2 Setting REPEATABLE READ

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT @@SESSION.transaction_isolation;

8.3 Example of a Repeatable Read

Use the same accounts table created in the previous example. Make sure its starting balance is 5000.00 before testing.

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT balance
FROM accounts
WHERE account_id = 101;

The first query establishes the consistent snapshot and returns 5000.00.

Session B:

START TRANSACTION;

UPDATE accounts
SET balance = 4500.00
WHERE account_id = 101;

COMMIT;

Return to Session A:

SELECT balance
FROM accounts
WHERE account_id = 101;

COMMIT;

The second ordinary consistent read in Session A still returns 5000.00 because it uses the same transaction snapshot. After Session A commits and begins a new transaction, a new ordinary read can see the committed balance of 4500.00.

Important: This example concerns ordinary consistent reads. Locking reads, updates, and deletes can use the latest available row versions and locking rules, so do not assume that every statement in a REPEATABLE READ transaction observes identical data in every circumstance.

9. SERIALIZABLE Isolation Level

SERIALIZABLE is the most restrictive standard isolation level. It provides stronger coordination between concurrent transactions to make their results consistent with a serial execution order.

9.1 Characteristics

• Dirty reads are prevented.
• Non-repeatable reads are prevented through transaction coordination.
• Phantom-read anomalies are prevented through appropriate locking and transaction behavior.
• Concurrency can be reduced because transactions may need to wait for locks.
• Lock waits and deadlocks may become more frequent in busy workloads.
• It can be useful when a particular operation requires stricter coordination and the performance trade-off is acceptable.

9.2 Setting SERIALIZABLE

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT @@SESSION.transaction_isolation;

9.3 Example Scenario

Imagine a system that checks whether enough seats remain before confirming a booking. Two concurrent transactions might both read the available seat count and attempt to reserve the same remaining seats. A suitable transaction design, locking strategy, and database constraints are needed to prevent overbooking.

SERIALIZABLE can provide stronger coordination, but it should not be treated as a substitute for checking affected rows, enforcing constraints, or implementing correct booking logic.

9.4 SERIALIZABLE and SELECT Statements

With InnoDB, when autocommit is disabled, ordinary SELECT statements under SERIALIZABLE are treated as shared locking reads unless the transaction is explicitly read-only. When autocommit is enabled, an individual SELECT is its own transaction and may be handled as a read-only transaction.

10. How to Change Isolation Levels in MySQL

MySQL allows isolation levels to be set at different scopes. Select the scope carefully because a session-level change does not automatically affect other connections.

10.1 Check the Current Isolation Level

SELECT @@transaction_isolation;

SELECT @@SESSION.transaction_isolation;

SELECT @@GLOBAL.transaction_isolation;

The session value describes the current connection. The global value describes the server's global default for new sessions, subject to server configuration and any later session-level changes.

10.2 Set the Isolation Level for the Current Session

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

This affects subsequent transactions in the current session. It does not change the isolation level for every connected user.

10.3 Set the Isolation Level for the Next Transaction

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT *
FROM accounts
WHERE account_id = 101;

COMMIT;

Without GLOBAL or SESSION, SET TRANSACTION sets the characteristics for the next transaction only. Issue it before beginning that transaction.

10.4 Set the Global Default

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

This changes the global default used by new sessions. Existing sessions are not automatically switched to the new level. Changing the global value generally requires the appropriate administrative privilege, and server configuration may be used to establish the default at startup.

Warning: Changing the global isolation level affects application connections created afterward. Test the change and its compatibility with the application's transaction logic before using it on a production server.

11. Understanding MVCC in MySQL

MVCC stands for Multi-Version Concurrency Control. InnoDB uses multiple versions of rows to support consistent reads while allowing transactions to work concurrently.

Instead of requiring every ordinary read to block until another transaction finishes, InnoDB can use an appropriate earlier version of a row for a consistent read. This helps readers and writers operate concurrently while preserving the visibility rules of the chosen isolation level.

11.1 How MVCC Works

• Transactions read data according to their snapshot and isolation level.
• When a row is updated, InnoDB maintains information that allows older versions to be reconstructed for consistent reads.
• Ordinary consistent SELECT statements can often read without acquiring row locks.
• Locking reads and data modification statements use locks to coordinate concurrent changes.
• Old row versions can be cleaned up when they are no longer needed by active transactions.

11.2 MVCC Example

Suppose a transaction begins and reads a product price of 1,200. Another transaction updates the price to 1,350 and commits. Under REPEATABLE READ, the first transaction's ordinary consistent read can continue to see 1,200 through its original snapshot. A new transaction can see 1,350.

11.3 Benefits of MVCC

• Improves read concurrency.
• Allows many ordinary reads to proceed without waiting for write locks.
• Supports consistent snapshots.
• Helps reduce unnecessary blocking between readers and writers.

12. Locks and Isolation Levels

Locks coordinate transactions when data is read or modified in ways that require protection from concurrent changes. InnoDB uses different locking strategies depending on the statement, index, search condition, and isolation level.

12.1 Locking Read Example

START TRANSACTION;

SELECT balance
FROM accounts
WHERE account_id = 101
FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 101;

COMMIT;

FOR UPDATE requests an exclusive lock on the selected rows. This is useful when an application must read a value and then make a related change while preventing another transaction from modifying the locked row first.

12.2 Why Indexes Matter

Indexes can help InnoDB locate the rows that need to be locked. A selective index may reduce the number of records scanned and locked. Poor indexing can increase scanning, locking, contention, and the risk of deadlocks.

13. Isolation Levels and Performance

Isolation levels influence transaction concurrency and consistency, but the performance difference depends on the workload, queries, indexes, transaction duration, and contention.

Do not choose an isolation level based on speed alone. A fast transaction that produces incorrect business results is not a successful transaction. Benchmark realistic workloads and verify correctness under concurrent execution.

14. Practical Project: Demonstrating Isolation Levels

This exercise demonstrates how the same transaction behaves under READ COMMITTED and REPEATABLE READ. Run the commands in two separate MySQL connections.

14.1 Create a Test Table

CREATE TABLE bank_accounts (
    account_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO bank_accounts
(account_id, customer_name, balance)
VALUES
(1, 'Customer A', 10000.00),
(2, 'Customer B', 7500.00);

14.2 Test REPEATABLE READ

Connection A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT balance
FROM bank_accounts
WHERE account_id = 1;

Connection B:

START TRANSACTION;

UPDATE bank_accounts
SET balance = 9500.00
WHERE account_id = 1;

COMMIT;

Connection A:

SELECT balance
FROM bank_accounts
WHERE account_id = 1;

COMMIT;

Connection A's second ordinary consistent read continues to see the original snapshot value of 10000.00. A new transaction can see 9500.00.

14.3 Test READ COMMITTED

Reset the row before repeating the test:

UPDATE bank_accounts
SET balance = 10000.00
WHERE account_id = 1;

Connection A:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT balance
FROM bank_accounts
WHERE account_id = 1;

Connection B:

START TRANSACTION;

UPDATE bank_accounts
SET balance = 9500.00
WHERE account_id = 1;

COMMIT;

Connection A:

SELECT balance
FROM bank_accounts
WHERE account_id = 1;

COMMIT;

Connection A can see 9500.00 on the second read because READ COMMITTED creates a fresh snapshot for each ordinary consistent read.

14.4 Project Observations

• Record the result of the first SELECT in each test.
• Update and commit the row from the second connection.
• Record the result of the second SELECT.
• Compare the results under the two isolation levels.
• Repeat the exercise with a search that returns multiple rows to explore phantom behavior.
• Do not run both tests simultaneously against the same row without resetting the starting data.

15. Common Mistakes and Troubleshooting

16. Best Practices for Transaction Isolation

• Understand the application's consistency requirements before choosing an isolation level.
• Remember that REPEATABLE READ is the default isolation level for InnoDB.
• Use READ COMMITTED when fresh committed data per ordinary read is suitable for the workflow.
• Avoid READ UNCOMMITTED for critical financial or business operations.
• Use SERIALIZABLE selectively because stronger coordination can reduce concurrency.
• Use transactions to group related operations and commit only after required steps succeed.
• Use atomic SQL updates, appropriate constraints, and locking reads when modifying shared data.
• Keep transactions short and avoid waiting for user input while a transaction remains open.
• Create indexes that support common filters and joins.
• Handle deadlocks and lock timeouts safely, including retrying the entire transaction when appropriate.
• Test concurrency with multiple connections rather than relying only on single-user tests.
• Monitor lock waits and query performance before changing server-wide settings.

17. Practice Exercises

1. Create an InnoDB table for product prices and test a non-repeatable read using READ COMMITTED.
2. Repeat the same test using REPEATABLE READ and compare the results.
3. Use two sessions to demonstrate a dirty read attempt under READ UNCOMMITTED.
4. Create a transaction that reads all products priced above 1,000 and test how the result can change under READ COMMITTED.
5. Set SERIALIZABLE for a session and observe how concurrent operations behave when they access the same records.
6. Compare session-level isolation settings with the global default.
7. Use SELECT ... FOR UPDATE to protect a balance update and explain how it changes concurrency behavior.
8. Design a booking transaction that prevents two users from reserving the same final seat.

18. Interview Questions and Answers

Q1. What is a transaction isolation level?

It defines the visibility and concurrency rules that determine how one transaction can observe data changed by other transactions.

Q2. Which isolation levels does MySQL InnoDB support?

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

Q3. What is the default isolation level in InnoDB?

REPEATABLE READ.

Q4. What is a dirty read?

A dirty read occurs when a transaction reads another transaction's uncommitted change.

Q5. What is a non-repeatable read?

It occurs when repeated reads of the same row return different values because another transaction changed and committed the row between reads.

Q6. What is a phantom read?

It occurs when a repeated search returns a different set of matching rows due to concurrent inserts or deletes.

Q7. Which isolation level prevents dirty reads?

READ COMMITTED, REPEATABLE READ, and SERIALIZABLE prevent dirty reads in InnoDB.

Q8. What is the main difference between READ COMMITTED and REPEATABLE READ?

READ COMMITTED generally uses a fresh snapshot for each ordinary consistent read. REPEATABLE READ uses the snapshot established by the first ordinary consistent read in the transaction.

Q9. Does REPEATABLE READ mean every SQL statement sees exactly the same database state?

No. Its stable-snapshot behavior applies to ordinary consistent reads. Locking reads and data modification statements can follow different visibility and locking rules.

Q10. What is MVCC?

Multi-Version Concurrency Control is a technique that allows transactions to read appropriate versions of rows without requiring every ordinary read to block on writes.

Q11. How do you change the isolation level for a session?

Use SET SESSION TRANSACTION ISOLATION LEVEL level_name; with one of the four supported levels.

Q12. What is the purpose of SELECT FOR UPDATE?

It requests exclusive locks on the selected rows so that a transaction can safely coordinate a read followed by a modification.

Q13. Which isolation level offers the strongest standard transaction coordination?

SERIALIZABLE. It can increase blocking and deadlock risk, so it should be selected based on the application's requirements.

Q14. Can changing the global isolation level change existing sessions?

No. A global change sets the default for new sessions; existing sessions may retain their own isolation settings.

Q15. Can isolation levels alone prevent every lost update?

No. Correct update patterns, locking, constraints, and application-level validation may also be required.

19. Quick Revision Checklist

• Isolation is the I in ACID.
• InnoDB supports four standard isolation levels.
• READ UNCOMMITTED may allow dirty reads.
• READ COMMITTED provides a fresh snapshot for each ordinary consistent read.
• REPEATABLE READ is the default for InnoDB and provides a stable snapshot for ordinary consistent reads.
• SERIALIZABLE provides stronger coordination but may reduce concurrency.
• MVCC helps support consistent reads and concurrent database activity.
• Locking reads such as SELECT ... FOR UPDATE behave differently from ordinary consistent reads.
• Session, global, and next-transaction isolation settings have different scopes.
• Use indexes, short transactions, error handling, and concurrency testing to support reliable results.

20. Conclusion

Transaction isolation levels are essential for controlling concurrent database activity in MySQL. They determine what transactions can observe while other transactions read or modify the same data. READ UNCOMMITTED permits the least restrictive visibility, READ COMMITTED prevents dirty reads while allowing fresh snapshots, REPEATABLE READ provides stable ordinary read snapshots, and SERIALIZABLE provides stronger coordination at a potential cost to concurrency.

For many applications using InnoDB, REPEATABLE READ is a practical default. However, the correct isolation level depends on the application's consistency requirements, SQL statements, locking strategy, and workload. Use transactions carefully, validate critical updates, and test with multiple sessions to ensure the database behaves correctly under concurrent access.

21. Official Learning Resources

• MySQL Official Documentation: InnoDB Transaction Isolation Levels
• MySQL Official Documentation: SET TRANSACTION Statement
• MySQL Official Documentation: InnoDB Transaction Model

Continue Learning: Explore the MySQL Training Course at JustAcademy or register for a course demo to continue learning MySQL concepts and practical database development.