Chapter 7

Transaction Isolation Lab

MySQL Transaction Isolation Levels Complete Guide

Understanding transaction isolation is critical for building correct concurrent applications. This guide explores all four isolation levels with practical demonstrations of anomalies, performance implications, and when to use each level.

1. ACID Properties and Isolation

1.1 Transaction Fundamentals

A transaction is a sequence of operations that either all succeed (commit) or all fail (rollback).


ACID PROPERTIES:

A - Atomicity
    All operations succeed or all fail
    Intermediate states are never visible
    Example: Bank transfer (debit + credit both happen or neither)

C - Consistency
    Database moves from one valid state to another
    All constraints are maintained
    Integrity rules are enforced

I - Isolation
    Transactions don't interfere with each other
    One transaction's changes are hidden from others
    Until the transaction commits

D - Durability
    Once committed, data survives failures
    Persisted to disk
    Not lost even if server crashes

Transaction Lifecycle:


BEGIN TRANSACTION
  ↓
EXECUTE STATEMENTS
  ├─ SELECT (read data)
  ├─ INSERT/UPDATE/DELETE (modify data)
  └─ ... (more operations)
  ↓
DECISION POINT
  ├─ COMMIT (changes permanent)
  └─ ROLLBACK (changes discarded)

2. The Four Isolation Levels

2.1 READ UNCOMMITTED (RU) - Least Strict

Allows reading data written by uncommitted transactions (dirty reads).

Dirty Read Demonstration:


-- Session 1: Update without committing
BEGIN;
UPDATE accounts SET balance = 50.00 WHERE id = 1;
-- Do NOT commit yet

-- Session 2: Read uncommitted change
SELECT balance FROM accounts WHERE id = 1;
-- Result: 50.00 (dirty read - reads uncommitted data!)

-- Session 1: Rollback the transaction
ROLLBACK;

-- Session 2: Check balance again
SELECT balance FROM accounts WHERE id = 1;
-- Result: 100.00 (value changed back - Session 2 read dirty data)

Characteristics:

Allows dirty reads (reading uncommitted data)
Fastest (no locking required)
Almost never used in production
Suitable only for non-critical reads
No locks held on read operations

2.2 READ COMMITTED (RC) - Medium Isolation

Only reads committed data, but allows non-repeatable reads and phantom reads.

Non-Repeatable Read Demonstration:


-- Session 1: Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Result: 100.00

-- Session 2: Modify and commit
BEGIN;
UPDATE accounts SET balance = 75.00 WHERE id = 1;
COMMIT;

-- Session 1: Read same row again
SELECT balance FROM accounts WHERE id = 1;
-- Result: 75.00 (different value! - non-repeatable read)

-- Session 1: Rollback
ROLLBACK;

Phantom Read Demonstration:


-- Session 1: Count accounts
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT COUNT(*) FROM accounts;
-- Result: 1

-- Session 2: Insert new row
BEGIN;
INSERT INTO accounts VALUES (2, 'Bob', 200.00);
COMMIT;

-- Session 1: Count again in same transaction
SELECT COUNT(*) FROM accounts;
-- Result: 2 (phantom read - new row appeared!)

-- Session 1: Rollback
ROLLBACK;

Characteristics:

Prevents dirty reads (only reads committed data)
Allows non-repeatable reads
Allows phantom reads
Default in many databases (PostgreSQL, SQL Server)
Row locks released after reading
Good balance of concurrency and consistency

2.3 REPEATABLE READ (RR) - MySQL Default

Guarantees repeatable reads within a transaction using consistent snapshots.

Repeatable Read Guarantee:


-- Session 1: Start transaction
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Result: 100.00

-- Session 2: Modify and commit
BEGIN;
UPDATE accounts SET balance = 75.00 WHERE id = 1;
COMMIT;

-- Session 1: Read same row again
SELECT balance FROM accounts WHERE id = 1;
-- Result: 100.00 (consistent snapshot - value unchanged!)

-- Session 1: Rollback
ROLLBACK;

MySQL's Implementation (InnoDB):

Uses consistent read view (snapshot)
Reads from transaction's start point
Prevents non-repeatable reads
Mostly prevents phantom reads (but INSERT by self is allowed)
Uses gap locks on indexes for write predicates
Default isolation level in MySQL

2.4 SERIALIZABLE - Most Strict

Transactions execute in serial order, as if one after another.

Serializable Transactions:


-- Session 1: Start serializable transaction
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT COUNT(*) FROM accounts;
-- Result: 1

-- Session 2: Try to insert
BEGIN;
INSERT INTO accounts VALUES (2, 'Bob', 200.00);
-- Blocks! Waiting for Session 1 to release locks

-- Session 1: Commit
COMMIT;

-- Session 2: Now insert succeeds

-- All operations executed serially with no conflicts

Characteristics:

Prevents all anomalies (dirty, non-repeatable, phantom reads)
Achieves true serializability
Significant performance cost due to locking
High contention in multi-user systems
Use rarely - usually REPEATABLE READ is sufficient

3. Isolation Level Comparison Table

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance	Use Case
READ UNCOMMITTED	Possible	Possible	Possible	Fastest	Rarely used analytics
READ COMMITTED	Prevented	Possible	Possible	Good	General purpose (PostgreSQL default)
REPEATABLE READ	Prevented	Prevented	Mostly prevented*	Good	MySQL default, most applications
SERIALIZABLE	Prevented	Prevented	Prevented	Slowest	Critical/financial systems rarely

*REPEATABLE READ in InnoDB prevents phantom reads for statements within the transaction, but new rows inserted by the transaction itself may be visible.

4. Real-World Scenarios

4.1 E-commerce Scenario: Order Processing


-- High concurrency: thousands of orders per minute
-- Need to prevent inventory overselling

-- Recommended: REPEATABLE READ (MySQL default)
-- Why: Prevents non-repeatable reads but allows reasonable concurrency

BEGIN;  -- REPEATABLE READ

-- Check stock
SELECT stock_quantity FROM inventory WHERE product_id = 1;
-- Suppose result is 10

-- ... customer confirms order ...

-- Deduct stock
UPDATE inventory SET stock_quantity = stock_quantity - 1
WHERE product_id = 1;

COMMIT;

4.2 Financial Transfer: Account Balance


-- CRITICAL: Must ensure no lost updates
-- Use REPEATABLE READ + explicit locking

BEGIN;

-- Lock both accounts to transfer money safely
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

-- Now both rows are locked, read values
SET @from_balance = (SELECT balance FROM accounts WHERE id = 1);
SET @to_balance = (SELECT balance FROM accounts WHERE id = 2);

-- Check sufficient funds
IF @from_balance >= 25.00 THEN
  UPDATE accounts SET balance = balance - 25.00 WHERE id = 1;
  UPDATE accounts SET balance = balance + 25.00 WHERE id = 2;
  COMMIT;
ELSE
  ROLLBACK;
END IF;

4.3 Reporting: Analytics Queries


-- READ COMMITTED acceptable for analytics
-- May see temporarily inconsistent state but recovers quickly

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

SELECT
  DATE(order_date) as date,
  SUM(amount) as total_sales,
  COUNT(*) as order_count,
  AVG(amount) as avg_order_value
FROM orders
WHERE order_date = CURDATE()
GROUP BY DATE(order_date);

COMMIT;

5. Locking Mechanisms

5.1 InnoDB Lock Types


SHARED (S) LOCK
├─ Multiple transactions can hold shared lock on same row
├─ No transaction can hold exclusive lock while shared lock exists
└─ Acquired by: Serializable reads, explicit locking

EXCLUSIVE (X) LOCK
├─ Only one transaction can hold exclusive lock on row
├─ Other locks (shared/exclusive) cannot be held
└─ Acquired by: Write operations

GAP LOCKS
├─ Locks the gap between index records (not the records themselves)
├─ Prevents insertions in the gap
└─ Used in: REPEATABLE READ for phantom prevention

NEXT-KEY LOCKS
├─ Combination of record lock + gap lock
├─ Locks both the record AND the gap before it
└─ Default in REPEATABLE READ with WHERE conditions

INSERT INTENTION LOCKS
├─ Special gap lock acquired before INSERT
├─ Multiple transactions can acquire on same gap
└─ Allows concurrent INSERTs to different gaps

Viewing Locks:


-- Performance Schema lock info (MySQL 5.6+)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- InnoDB status
SHOW ENGINE INNODB STATUS;
-- Look for LOCK WAIT section and transaction info

-- Kill blocking transaction if necessary
SHOW PROCESSLIST;
KILL ;

6. Deadlock Handling

6.1 Deadlock Scenario


-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- (Has lock on account 1, waiting for lock on account 2)
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

-- At the same time...
-- Transaction B:
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 2;
-- (Has lock on account 2, waiting for lock on account 1)
UPDATE accounts SET balance = balance + 30 WHERE id = 1;

-- DEADLOCK! Both transactions waiting for each other
-- ERROR 1213 (40001): Deadlock found when trying to get lock

6.2 Deadlock Prevention


-- Strategy 1: Lock resources in consistent order
-- Always lock account with lower ID first

CREATE PROCEDURE transfer_safe(
  IN from_id INT,
  IN to_id INT,
  IN amount DECIMAL(10, 2)
)
BEGIN
  DECLARE min_id INT;
  DECLARE max_id INT;

  -- Ensure consistent lock order
  IF from_id < to_id THEN
    SET min_id = from_id;
    SET max_id = to_id;
  ELSE
    SET min_id = to_id;
    SET max_id = from_id;
  END IF;

  START TRANSACTION;

  -- Lock in consistent order to prevent deadlock
  SELECT balance FROM accounts WHERE id = min_id FOR UPDATE;
  SELECT balance FROM accounts WHERE id = max_id FOR UPDATE;

  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;

  COMMIT;
END;

-- Strategy 2: Use shorter transactions
-- Reduce time holding locks

-- Strategy 3: Increase innodb_lock_wait_timeout
SET GLOBAL innodb_lock_wait_timeout = 50;

-- Strategy 4: Monitor deadlocks
SHOW ENGINE INNODB STATUS;
-- Look for "LATEST DEADLOCK SECTION"

7. Performance vs Consistency Trade-offs

Level	Concurrency	Consistency	Lock Wait Likelihood	Best For
READ UNCOMMITTED	Excellent	Poor	None	Approximate analytics
READ COMMITTED	Very Good	Good	Low	Most general apps
REPEATABLE READ	Good	Very Good	Medium	MySQL default (recommended)
SERIALIZABLE	Poor	Perfect	Very High	Rarely needed

8. Best Practices

Use MySQL's default (REPEATABLE READ) — it's well-designed and performs well
Use explicit locking (FOR UPDATE) for critical sections — when you need to ensure specific behavior
Keep transactions short — reduces lock holding time
Lock resources in consistent order — prevents deadlocks
Use application-level retries for deadlocks — transient conflicts are normal
Monitor for long-running transactions — they block others
Avoid SERIALIZABLE unless absolutely required — severe performance impact
Test concurrent scenarios — isolation issues appear under load
Set appropriate innodb_lock_wait_timeout — balance between waiting and fast failure
Use READ COMMITTED for read-heavy analytics — good performance without much isolation loss

9. Checking Current Settings


-- Check current isolation level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Or for session
SELECT @@transaction_isolation;
SELECT @@SESSION.transaction_isolation;

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

-- Change globally (affects new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check other relevant settings
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';  -- Default 50 seconds
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';   -- 0/1/2
SHOW VARIABLES LIKE 'max_connections';             -- Affects concurrency

Conclusion

MySQL transaction isolation levels provide a spectrum of consistency vs performance:

READ UNCOMMITTED: Avoid — too risky
READ COMMITTED: Use for high-concurrency analytics
REPEATABLE READ: MySQL's default — use for most applications
SERIALIZABLE: Rarely needed — extreme performance cost

Most applications should use REPEATABLE READ with explicit locking (FOR UPDATE) for critical sections. Deadlocks are normal — handle them gracefully with retries. Monitor lock waits and adjust strategy if contention becomes an issue.

Rate this chapter

4.6 / 5 (60 ratings)

Transaction Isolation Lab

MySQL Transaction Isolation Levels Complete Guide

1. ACID Properties and Isolation

1.1 Transaction Fundamentals

2. The Four Isolation Levels

2.1 READ UNCOMMITTED (RU) - Least Strict

2.2 READ COMMITTED (RC) - Medium Isolation

2.3 REPEATABLE READ (RR) - MySQL Default

2.4 SERIALIZABLE - Most Strict

3. Isolation Level Comparison Table

4. Real-World Scenarios

4.1 E-commerce Scenario: Order Processing

4.2 Financial Transfer: Account Balance

4.3 Reporting: Analytics Queries

5. Locking Mechanisms

5.1 InnoDB Lock Types

6. Deadlock Handling

6.1 Deadlock Scenario

6.2 Deadlock Prevention

7. Performance vs Consistency Trade-offs

8. Best Practices

9. Checking Current Settings

Conclusion

💬 Comments