Understanding MySQL Transaction Isolation Levels

MySQL's transaction isolation levels control how transactions interact with each other. READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads. REPEATABLE READ (MySQL default) prevents both but allows phantom reads. SERIALIZABLE prevents all anomalies but has the lowest performance.

Transaction isolation is one of those topics that sounds academic until you lose data because of it. Consider a financial application where two users transfer money from the same account simultaneously. Without proper isolation, both transfers succeed, even though the account does not have enough balance for both. That is a real-world consequence of choosing the wrong isolation level.

These problems are not edge cases. They happen regularly in any system with concurrent users. Choosing the right isolation level is about deciding how much inconsistency your application can tolerate versus how much performance you are willing to sacrifice.

What Problems Exist?

Before understanding isolation levels, you need to understand the problems they solve.

The SQL standard defines these anomalies to help developers reason about what guarantees a database provides. Each isolation level offers a different tradeoff between consistency and concurrency. Understanding these tradeoffs helps you make informed decisions instead of just picking the highest level and hoping for the best.

Dirty Read

Transaction A reads data that Transaction B has modified but not yet committed. If Transaction B rolls back, Transaction A has read data that never existed.

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
UPDATE accounts SET balance = 1000
WHERE id = 1;
                                      SELECT balance FROM accounts
                                      WHERE id = 1;
                                      -- Returns 1000 (dirty read)
ROLLBACK;
-- Balance is still 500,
-- but Session 2 read 1000

A dirty read means you are working with data that might not actually exist. If you use that value to make a decision, like approving a loan or sending a notification, you could be acting on information that was rolled back seconds later. This is why most production systems avoid dirty reads entirely.

Non-Repeatable Read

Transaction A reads the same row twice and gets different values because Transaction B modified and committed the row between the two reads.

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500
                                      UPDATE accounts SET balance = 1000
                                      WHERE id = 1;
                                      COMMIT;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 1000 (different value)

Non-repeatable reads cause problems when your transaction logic depends on reading the same value twice. For example, if you check a balance, then calculate a transfer amount based on that balance, a non-repeatable read means the balance might have changed between those two operations. The transfer amount would be based on stale data.

Phantom Read

Transaction A runs the same query twice and gets different row counts because Transaction B inserted or deleted rows between the two executions.

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
SELECT COUNT(*) FROM orders
WHERE user_id = 1;
-- Returns 5
                                      INSERT INTO orders (user_id, total)
                                      VALUES (1, 100.00);
                                      COMMIT;
SELECT COUNT(*) FROM orders
WHERE user_id = 1;
-- Returns 6 (phantom row)

Phantom reads are particularly tricky because your query returns different rows even though you did not change anything. This breaks assumptions in code that processes a result set and then acts on it. For example, summing up orders in a loop might miss new rows inserted by another transaction.

The Four Isolation Levels

READ UNCOMMITTED

The lowest isolation level. Transactions can read data modified by other uncommitted transactions. Dirty reads, non-repeatable reads, and phantom reads are all possible.

SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
                                      UPDATE accounts SET balance = 1000
                                      WHERE id = 1;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 1000 (dirty read!)
-- Session 2 has not committed yet

When to use: Almost never. The only legitimate use case is when you need to read the latest possible data and can tolerate inconsistencies, like monitoring dashboards that show approximate real-time data.

The main reason to avoid READ UNCOMMITTED is that you cannot trust any data you read. Even read-only queries can return values that were never committed. For most business applications, this level of inconsistency is unacceptable.

READ COMMITTED

Prevents dirty reads. Transactions can only read committed data. Non-repeatable reads and phantom reads are still possible.

SET SESSION transaction_isolation = 'READ-COMMITTED';

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500
                                      UPDATE accounts SET balance = 1000
                                      WHERE id = 1;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500 (no dirty read)
                                      COMMIT;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 1000 (non-repeatable read)

When to use: When you need consistency within a single statement but not across multiple statements. Many Oracle and PostgreSQL applications use this level. It is a good balance between consistency and performance.

The key insight with READ COMMITTED is that each statement gets a fresh snapshot of the data. This means a single SELECT sees consistent data, but two different SELECTs in the same transaction might see different data. This is fine for many use cases but can cause subtle bugs if you are not aware of the behavior.

REPEATABLE READ (MySQL Default)

Prevents dirty reads and non-repeatable reads. Phantom reads are possible (though MySQL's implementation with next-key locking prevents most phantom reads).

-- Default in MySQL
SET SESSION transaction_isolation = 'REPEATABLE-READ';

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500
                                      UPDATE accounts SET balance = 1000
                                      WHERE id = 1;
                                      COMMIT;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 500 (repeatable read)
-- Even though Session 2 committed a change

When to use: When you need consistent reads across multiple queries within a transaction. This is the default and appropriate for most applications.

MySQL's implementation of REPEATABLE READ is more capable than the SQL standard requires. It uses next-key locking, which combines row locks and gap locks, to prevent most phantom reads. However, this is an implementation detail and should not be relied upon for strict phantom prevention.

SERIALIZABLE

The highest isolation level. Transactions execute as if they were serialized (one after another). All anomalies are prevented. Performance is the lowest because transactions block each other.

SET SESSION transaction_isolation = 'SERIALIZABLE';

-- Session 1                          -- Session 2
START TRANSACTION;                    START TRANSACTION;
SELECT * FROM accounts
WHERE id = 1;
-- Session 2 is BLOCKED here
-- until Session 1 commits
                                      UPDATE accounts SET balance = 1000
                                      WHERE id = 1;
                                      -- Blocked! Waiting for Session 1
COMMIT;
-- Session 2 now proceeds

When to use: When data consistency is more important than performance. Financial transfers, inventory management, and any operation where partial visibility of changes would cause incorrect results.

The performance cost of SERIALIZABLE comes from the fact that transactions block each other more aggressively. Read operations acquire shared locks, and write operations acquire exclusive locks. This means fewer transactions can run concurrently, which directly impacts throughput under high load.

Choosing the Right Level

When choosing an isolation level, think about what your application actually needs. A reporting system that runs batch queries at night has different requirements than a real-time trading platform. There is no one-size-fits-all answer, and the right choice depends on your specific consistency and performance requirements.

My Recommendation

Start with REPEATABLE READ (MySQL default). It handles most use cases correctly. If you encounter performance issues with concurrent writes, consider stepping down to READ COMMITTED. Only use SERIALIZABLE for critical financial or inventory operations where consistency is more important than performance.

The biggest mistake is developers using SERIALIZABLE for everything "to be safe." This kills performance and causes deadlocks. Use the lowest isolation level that gives you the consistency you need.

The best approach is to start with the default and adjust based on observed behavior. Use tools like SHOW ENGINE INNODB STATUS to monitor lock contention and deadlocks. If you see frequent lock waits, consider whether a lower isolation level is acceptable for your use case.

Common Mistakes

1. Using SERIALIZABLE for everything - This kills performance and causes unnecessary deadlocks. Only use it when you absolutely need strict consistency
2. Ignoring non-repeatable reads - Many developers focus on dirty reads but forget that non-repeatable reads can cause logic errors in multi-step operations
3. Assuming MySQL prevents all phantoms by default - REPEATABLE READ in MySQL uses next-key locking to prevent most phantoms, but this is an implementation detail, not a guarantee

When you suspect an isolation level issue, the first step is to reproduce it under controlled conditions. Use two concurrent sessions and step through the operations manually. This makes it clear exactly what anomalies are possible at your current isolation level.

Real-World Example: E-Commerce Order Processing

Imagine an e-commerce platform where customers place orders. Two customers buy the last item in stock at the same time. Without proper isolation, both orders succeed, leading to overselling.

-- Inventory check and order placement
-- Using REPEATABLE READ (default)
START TRANSACTION;

-- Check inventory
SELECT stock FROM products WHERE product_id = 42;
-- Returns 1 (last item)

-- Both transactions see stock = 1
-- Now both try to decrement
UPDATE products SET stock = stock - 1 WHERE product_id = 42;
-- First transaction succeeds: stock = 0
-- Second transaction succeeds: stock = -1 (oversold!)

COMMIT;

With SERIALIZABLE isolation, the second transaction blocks until the first commits. By the time it runs, stock is 0 and the application can reject the order. The tradeoff is that the second customer waits longer during high-traffic periods.

Another scenario involves reporting queries that run alongside order processing. A nightly report counts total orders for the day. Under READ COMMITTED, the report might miss orders committed while it is running, giving inaccurate totals. Under REPEATABLE READ, the report gets a consistent snapshot from when it started, which is usually the desired behavior for business reports.

Key Takeaways

• MySQL defaults to REPEATABLE READ, which prevents dirty reads and non-repeatable reads
• Higher isolation levels reduce anomalies but increase locking and reduce concurrency
• Use the lowest isolation level that meets your consistency requirements
• Monitor lock wait timeouts and deadlocks when adjusting isolation levels
• Test your application under concurrent load to verify isolation behavior

FAQ

What is MySQL's default isolation level?

REPEATABLE READ. This is set in the MySQL configuration file (my.cnf) and applies to all sessions unless explicitly changed.

Can I change the isolation level for a single transaction?

Yes. Use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before starting a transaction. This affects only the next transaction.

Does SERIALIZABLE prevent all deadlocks?

No. SERIALIZABLE reduces deadlocks by serializing transactions, but deadlocks can still occur if transactions access resources in different orders. Always use consistent ordering when accessing multiple resources.

How do I check the current isolation level?

Run SELECT @@transaction_isolation to see the current session or global isolation level. You can also check SHOW VARIABLES LIKE 'transaction_isolation'.

Can different sessions use different isolation levels?

Yes. Each session can set its own isolation level without affecting other sessions. Use SET SESSION transaction_isolation to change the level for the current session only.