InnoDB serves as MySQL's default storage engine and supports full transaction capabilities. Every operation runs within a transaction context; explicit BEGIN or START TRANSACTION statements start user-defined transactions, while implicit ones are created automatically for individual statements when not explicitly managed.

MySQL defines four transaction isolation levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Core Concepts

• MVCC (Multi-Version Concurrency Control): Maintains multiple versions of rows to provide consistent read snapshots during a transaction. A snapshot ensures row version stability for the duration of that read operation.
• Dirty Read: Accessing uncommitted changes from another transaction, risking inconsistency.
• Non-Repeatable Read: Observing different row values across multiple reads within the same transaction.
• Phantom Read: Encountering a different number of rows matching a range condition between successive reads in one transaction.
• Gap Lock: Locks ranges between index records to prevent insertion of new rows within those intervals.

Read Uncommitted

Transactions may observe uncommitted modifications made by concurrent transactions. MVCC is bypassed; write operations acquire row-level exclusive locks.

Characteristics:

• Fast concurent reads due to absence of snapshot creation.
• Risks: dirty reads, non-repeatable reads, phantom reads.

-- Example: Reading uncommitted data
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- May see uncommitted updates
COMMIT;

Read Committed

Only committed changes become visible. Each read statement constructs a fresh MVCC snapshot; writers still take row-level exclusive locks.

Characteristics:

• Eliminates dirty reads.
• Risks: non-repeatable reads, phantom reads.

-- Example: Reading only committed data
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Sees latest committed state per query
COMMIT;

Repeatable Read

Committed data is visible; MVCC generates a single snapshot at transaction start, ensuring consistent reads throughout. Writers use row-level exclusive locks. Range queries via indexes employ gap locks; absent an index, a table-level lock applies.

Characteristics:

• No dirty reads.
• Consistent repeated reads in side the same transaction.
• Reduces phantom read occurrences.
• Gap locking raises deadlcok potential.
• Table locks on non-indexed scans degrade performance sharply.

-- Example: Stable snapshot reads
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Snapshot fixed at transaction start
-- Other queries in this transaction see identical snapshot data
COMMIT;

Serializable

Reads acquire shared locks, writes obtain exclusive locks, and range conditions trigger gap or table locks. MVCC is disabled; transactions execute in apparent serial order.

Characteristics:

• Prevents dirty reads, non-repeatable reads, and phantom reads.
• Highest isolation, lowest concurrency throughput.

-- Example: Fully isolated execution
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000; -- Shared locks block conflicting writes
COMMIT;

MySQL configures REPEATABLE READ as the default isolation level. This can be altered per session using connection parameters or SQL statements like SET SESSION TRANSACTION ISOLATION LEVEL.