Concurrency Control & Isolation Levels in MySQL

When multiple users access a database at the same time, problems can occur if transactions are not handled properly.

For example:

  • Two users try to withdraw money from the same bank account.
  • Two customers try to book the last ticket.
  • Two admins update the same product stock.

If MySQL does not manage these situations correctly, data may become inconsistent.

To solve this, MySQL uses Concurrency Control and Isolation Levels.

1. What Is Concurrency in MySQL?

Concurrency means multiple transactions executing at the same time.

In real-world applications:

  • Hundreds or thousands of users may access the database simultaneously.
  • Each user may run transactions that read or modify data.

Concurrency control ensures:

  • Data remains correct
  • Transactions do not interfere improperly with each other
  • ACID properties are maintained

2. Problems Caused by Concurrent Transactions

Without proper isolation, the following problems can occur.

1. Dirty Read

A dirty read happens when a transaction reads data that has not yet been committed.

Example:

  • Transaction A updates a balance but does not commit.
  • Transaction B reads that updated balance.
  • Transaction A rolls back.
  • Transaction B now has invalid data.

This causes inconsistency.

2. Non-Repeatable Read

A non-repeatable read happens when the same row gives different results within the same transaction.

Example:

  • Transaction A reads a product price.
  • Transaction B updates that price and commits.
  • Transaction A reads the same price again.
  • The value has changed.

The result is inconsistent within one transaction.

3. Phantom Read

A phantom read occurs when new rows appear during a transaction.

Example:

  • Transaction A selects all orders where the amount > 1000.
  • Transaction B inserts a new order with an amount > 1000 and commits.
  • Transaction A runs the same query again.
  • A new row appears.

This is called a phantom row.

4. Lost Update

A lost update happens when two transactions update the same row, and one overwrites the other.

Example:

  • Transaction A reads stock = 10.
  • Transaction B reads stock = 10.
  • Transaction A updates the stock to 9.
  • Transaction B updates the stock to 9.

One update is lost.

3. What Are Isolation Levels?

Isolation is one of the ACID properties. It defines how transactions are separated from each other.

MySQL provides four isolation levels.

1. READ UNCOMMITTED

  • Allows dirty reads.
  • One transaction can see uncommitted data from another.

This is the lowest isolation level.
Data consistency is weak.

2. READ COMMITTED

  • Prevents dirty reads.
  • A transaction can only read committed data.
  • Non-repeatable reads may still happen.

This level is commonly used in many database systems.

3. REPEATABLE READ

  • Prevents dirty reads.
  • Prevents non-repeatable reads.
  • Phantom reads may still occur in theory.

This is the default isolation level in MySQL (InnoDB engine).

4. SERIALIZABLE

  • Highest isolation level.
  • Transactions behave as if executed one after another.
  • Prevents dirty reads, non-repeatable reads, and phantom reads.

This level is safest but can reduce performance.

4. How to Set Isolation Level in MySQL

Set isolation level for the current session:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Start a transaction:

START TRANSACTION;

Check current isolation level:

SELECT @@transaction_isolation;

5. How MySQL Handles Concurrency Internally

MySQL (InnoDB storage engine) uses:

  • Row-level locking
  • Multi-Version Concurrency Control (MVCC)

MVCC allows:

  • Multiple versions of rows
  • Transactions to read consistent snapshots of data
  • Better performance with safety

This helps balance performance and data integrity.

6. Real-World Example

Online ticket booking system:

  • User A starts booking the last seat.
  • User B tries to book the same seat.
  • Isolation and locking mechanisms ensure:
    • Only one booking succeeds.
    • The other transaction fails or waits.

This prevents double booking.

7. Choosing the Right Isolation Level

Lower isolation:

  • Better performance
  • Higher risk of inconsistencies

Higher isolation:

  • Better data safety
  • Lower performance

Most applications use:

  • REPEATABLE READ (MySQL default)
  • READ COMMITTED (in high-concurrency systems)

SERIALIZABLE is used in highly sensitive systems like banking.

Summary

Concurrency control ensures that multiple transactions can run safely at the same time.

Common problems:

  • Dirty Read
  • Non-repeatable Read
  • Phantom Read
  • Lost Update

MySQL provides four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Understanding concurrency control is essential for building reliable, multi-user applications.