MySQL & Database Design

Isolation Levels & Concurrency

13 min Lesson 27 of 40

Isolation Levels & Concurrency

When multiple transactions run simultaneously in a database, they can interfere with each other in unexpected ways. Transaction isolation levels define how changes made by one transaction become visible to other concurrent transactions, balancing data consistency with system performance.

The Isolation Challenge

Imagine two users trying to book the last available seat on a flight at the same time. Without proper isolation, both might see the seat as available and successfully book it, resulting in data corruption. Isolation levels prevent such conflicts.

The Tradeoff: Higher isolation levels provide more consistency but reduce concurrency and performance. Lower levels improve performance but can lead to anomalies. Choosing the right level depends on your application's needs.

The Four Isolation Levels

SQL defines four standard isolation levels, from least to most isolated:

READ UNCOMMITTED - Lowest isolation, highest performance
READ COMMITTED - Default in many databases
REPEATABLE READ - Default in MySQL/InnoDB
SERIALIZABLE - Highest isolation, lowest performance

Setting Isolation Levels

You can set isolation levels at the session or transaction level:

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for next transaction only
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set globally (requires privileges)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1. READ UNCOMMITTED

The lowest isolation level allows transactions to read uncommitted changes from other transactions. This can lead to "dirty reads".

-- Transaction 1
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE product_id = 1;
-- Not yet committed

-- Transaction 2 (in another session, READ UNCOMMITTED)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT price FROM products WHERE product_id = 1;
-- Sees 99.99 even though Transaction 1 hasn't committed!

-- If Transaction 1 rolls back, Transaction 2 read incorrect data

Dirty Read Problem: Transaction 2 reads data that Transaction 1 might roll back. This rarely used isolation level is suitable only for analytics or logging where approximate data is acceptable.

2. READ COMMITTED

This level prevents dirty reads by only allowing transactions to read committed data. However, it permits "non-repeatable reads".

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT price FROM products WHERE product_id = 1;
-- Returns 50.00

-- Meanwhile, Transaction 2 commits a change
-- (in another session)
UPDATE products SET price = 75.00 WHERE product_id = 1;
COMMIT;

-- Back to Transaction 1
SELECT price FROM products WHERE product_id = 1;
-- Now returns 75.00 - different from before!

COMMIT;

Non-Repeatable Read: The same query returns different results within the same transaction because another transaction modified and committed the data. This is acceptable for many applications.

3. REPEATABLE READ (MySQL Default)

This level prevents dirty reads and non-repeatable reads by ensuring that data read once remains consistent throughout the transaction:

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT price FROM products WHERE product_id = 1;
-- Returns 50.00

-- Transaction 2 changes and commits
-- (in another session)
UPDATE products SET price = 75.00 WHERE product_id = 1;
COMMIT;

-- Back to Transaction 1
SELECT price FROM products WHERE product_id = 1;
-- Still returns 50.00 (repeatable read!)

COMMIT;

-- After commit, new transactions see 75.00

REPEATABLE READ can still experience "phantom reads" in theory, though InnoDB prevents them:

-- Transaction 1
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE customer_id = 123;
-- Returns 5

-- Transaction 2 inserts a new order
-- (in another session)
INSERT INTO orders (customer_id, order_date) VALUES (123, NOW());
COMMIT;

-- Transaction 1
SELECT COUNT(*) FROM orders WHERE customer_id = 123;
-- In pure REPEATABLE READ: might return 6 (phantom read)
-- In InnoDB: still returns 5 (phantom reads prevented)

MySQL Advantage: InnoDB uses "next-key locking" which prevents phantom reads even at REPEATABLE READ level, giving you SERIALIZABLE-like consistency with better performance.

4. SERIALIZABLE

The highest isolation level makes transactions execute as if they were running serially (one after another):

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

SELECT * FROM products WHERE category = 'Electronics';
-- This places locks on all matching rows and gaps

-- Transaction 2
START TRANSACTION;
INSERT INTO products (name, category, price)
VALUES ('New Phone', 'Electronics', 599.99);
-- This waits! Transaction 1 has locked the category

-- Transaction 1 must commit first
COMMIT;

-- Now Transaction 2 can proceed
COMMIT;

Performance Impact: SERIALIZABLE dramatically reduces concurrency and can cause many transactions to wait. Use only when absolute consistency is required (e.g., financial reconciliation).

Understanding Read Phenomena

Here's a summary of what each isolation level prevents:

Isolation Level     | Dirty Read | Non-Repeatable Read | Phantom Read
--------------------|------------|---------------------|-------------
READ UNCOMMITTED    | Possible   | Possible            | Possible
READ COMMITTED      | Prevented  | Possible            | Possible
REPEATABLE READ     | Prevented  | Prevented           | Prevented*
SERIALIZABLE        | Prevented  | Prevented           | Prevented

* InnoDB prevents phantom reads at REPEATABLE READ

Real-World Example: Bank Account

Consider checking account balance during transfers:

-- Scenario: User checking balance while transfer is processing

-- Transfer Transaction (Session 1)
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
-- Processing... takes 5 seconds
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202;
COMMIT;

-- User Checking Balance (Session 2)
-- With READ UNCOMMITTED:
SELECT SUM(balance) FROM accounts WHERE user_id = 5;
-- Shows incorrect total during transfer!

-- With READ COMMITTED or higher:
SELECT SUM(balance) FROM accounts WHERE user_id = 5;
-- Waits for transfer to complete or shows consistent pre-transfer state

Choosing the Right Isolation Level

READ UNCOMMITTED:
✓ Analytics, logs, approximate counts
✗ Financial data, critical operations

READ COMMITTED:
✓ Web applications, general CRUD operations
✓ Good balance of consistency and performance
✗ Operations requiring consistent snapshots

REPEATABLE READ:
✓ MySQL default - excellent for most applications
✓ Reports that need consistent data views
✓ Inventory management, booking systems

SERIALIZABLE:
✓ Financial reconciliation
✓ Critical compliance operations
✗ High-traffic applications (poor performance)

Practical Example: Seat Booking System

Here's how different isolation levels affect a booking system:

-- Seat booking with REPEATABLE READ (recommended)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- Check if seat is available
SELECT is_available INTO @available
FROM seats
WHERE seat_id = 42
FOR UPDATE;  -- Lock the row

IF @available = 1 THEN
    -- Book the seat
    UPDATE seats SET is_available = 0, customer_id = 123 WHERE seat_id = 42;
    INSERT INTO bookings (seat_id, customer_id, booking_date)
    VALUES (42, 123, NOW());
    COMMIT;
    SELECT 'Booking successful' AS message;
ELSE
    ROLLBACK;
    SELECT 'Seat unavailable' AS message;
END IF;

Key Pattern: The FOR UPDATE clause explicitly locks rows, preventing other transactions from modifying them. This is crucial for booking systems, inventory management, and any "check-then-update" operation.

Locking Reads

You can explicitly control locking behavior within transactions:

-- Shared lock (other transactions can read but not modify)
SELECT * FROM products WHERE product_id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (other transactions can't read or modify)
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;

-- MySQL 8.0+ syntax
SELECT * FROM products WHERE product_id = 1 FOR SHARE;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;

Practice Exercise:

Scenario: Implement a ticket booking system that prevents double-booking.

Requirements:

Check ticket availability
Lock the ticket during check
Update ticket status to sold
Record the purchase
Use appropriate isolation level

Solution:

-- Use REPEATABLE READ (MySQL default is fine)
START TRANSACTION;

-- Check and lock ticket
SELECT status, price INTO @status, @price
FROM tickets
WHERE ticket_id = 7890
FOR UPDATE;  -- Exclusive lock prevents other transactions

-- Verify availability
IF @status = 'available' THEN
    -- Update ticket
    UPDATE tickets
    SET status = 'sold',
        sold_at = NOW(),
        customer_id = 456
    WHERE ticket_id = 7890;

    -- Record purchase
    INSERT INTO purchases (customer_id, ticket_id, amount, purchase_date)
    VALUES (456, 7890, @price, NOW());

    COMMIT;
    SELECT 'Ticket purchased successfully' AS message;
ELSE
    ROLLBACK;
    SELECT 'Ticket no longer available' AS message;
END IF;

Summary

In this lesson, you learned:

Isolation levels control how concurrent transactions interact
Four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
Higher isolation = more consistency but less performance
MySQL's REPEATABLE READ (default) is excellent for most use cases
Use FOR UPDATE to explicitly lock rows during check-then-update operations
Choose isolation levels based on your application's consistency requirements

Next Up: In the next lesson, we'll explore locks and deadlocks in detail, including how to detect and prevent them!