Understanding MySQL InnoDB: Architecture, Storage, Indexes, Locks, and Transactions

Introduction

Developers frequently interact with databases such as SQLite, MySQL, or PostgreSQL, yet many lack a systematic understanding of their inner workings. After two months of reading books and the official MySQL documentation, this article consolidates essential concepts of the MySQL InnoDB storage engine.

Database and Instance Definitions

A database is a collection of physical files on the file system, while an instance consists of the MySQL server threads and a shared memory area that operate on those files. In MySQL, each database usually maps one‑to‑one with an instance, and all operations go through the instance.

MySQL Instance Processes

On Unix systems a MySQL instance typically spawns two processes: mysqld – the actual database daemon handling client requests. mysqld_safe – a wrapper that monitors mysqld, restarts it on failure, and sets up the environment.

MySQL Architecture

The server consists of three logical layers:

Connection and thread handling (not invented by MySQL).

SQL parsing, optimization, and caching.

Storage engines (e.g., InnoDB, MyISAM) that manage the physical data.

Data Storage in InnoDB

All data are stored logically in a tablespace , which is subdivided into segments , extents , and pages . By default each page is 16 KB; the size can be changed with the innodb_page_size option, which also affects the extent size.

Table Definition Files

Every MySQL table creates a .frm file that stores the table definition (schema). The file format is identical across platforms.

<ol><li><code>CREATE TABLE test_frm(</code></li><li><code>    column1 CHAR(5),</code></li><li><code>    column2 INTEGER</code></li><li><code>);</code></li></ol>

When this statement is executed, a file test_frm.frm appears in the data directory, containing the structural metadata.

InnoDB Data Files

InnoDB stores row data and indexes in .ibd files. When the innodb_file_per_table option is enabled, each table gets its own .ibd file; otherwise data resides in the shared system tablespace files ibdata1, ibdata2, etc.

Record Layouts

InnoDB supports several row formats:

Compact – stores column lengths in reverse order, saving about 20 % space.

Redundant – stores column offsets.

Compressed and Dynamic – introduced with the Barracuda file format; they keep only a 20‑byte pointer in the page and store the full data on overflow pages.

For long VARCHAR or BLOB values, InnoDB keeps the first 768 bytes in the data page and the remainder on overflow pages (or entirely on overflow pages for Compressed/Dynamic formats).

Data Page Structure

An InnoDB page (16 KB) consists of:

Page Header – status information.

Page Directory – offsets of records.

Infimum and Supremum – virtual placeholder records.

User Records – actual rows.

Free Space – linked list of empty slots.

Page Trailer – checksum and other metadata.

Records are linked via the next_record pointer, allowing inserts without strict ordering.

Indexes

Indexes are the primary performance optimization in relational databases. InnoDB mainly uses B+‑tree indexes.

Clustered Index

The primary key forms a clustered index; the leaf nodes store the full row data. A table can have only one clustered index, usually the primary key.

<ol><li><code>CREATE TABLE users(</code></li><li><code>    id INT NOT NULL,</code></li><li><code>    first_name VARCHAR(20) NOT NULL,</code></li><li><code>    last_name VARCHAR(20) NOT NULL,</code></li><li><code>    age INT NOT NULL,</code></li><li><code>    PRIMARY KEY(id)</code></li><li><code>);</code></li></ol>

Secondary (Auxiliary) Index

Secondary indexes store only the indexed columns plus a bookmark (the primary key) that points to the clustered index row.

Locks

InnoDB uses pessimistic (row‑level) locking. Locks can be classified by:

Optimistic vs. pessimistic (InnoDB uses pessimistic).

Granularity: row lock vs. table lock.

Lock Types

Shared lock (read lock) – allows concurrent reads.

Exclusive lock (write lock) – blocks other reads and writes.

Lock Granularity and Intention Locks

Row locks protect individual records, while table locks protect the whole table. Intention locks ( IS and IX) are table‑level markers that indicate a session intends to acquire row‑level shared or exclusive locks, reducing lock‑compatibility checks.

Lock Algorithms

Record lock – locks an index record.

Gap lock – locks the gap between index records, preventing inserts into the range.

Next‑Key lock – a combination of record lock and the preceding gap lock; used to prevent phantom reads.

Example of Next‑Key lock ranges:

<ol><li>(‑∞, 21]</li><li>(21, 30]</li><li>(30, 40]</li><li>(40, 50]</li><li>(50, 80]</li><li>(80, ∞)</li></ol>

Deadlocks

When two sessions each hold a lock the other needs, a deadlock occurs. InnoDB detects deadlocks and rolls back one transaction to break the cycle.

Transactions and Isolation Levels

Transactions obey the ACID properties (Atomicity, Consistency, Isolation, Durability). MySQL implements four isolation levels defined by the SQL standard: READ UNCOMMITTED – allows dirty reads. READ COMMITTED – prevents dirty reads but permits non‑repeatable reads. REPEATABLE READ – guarantees repeatable reads; phantom reads are prevented by Next‑Key locks. SERIALIZABLE – adds shared locks to all reads, fully preventing phantom reads.

The default level in MySQL is REPEATABLE READ.

Phenomena Demonstrations

Dirty read : Session 2 inserts a row without committing; Session 1 can read the uncommitted row under READ UNCOMMITTED.

Non‑repeatable read : Under READ COMMITTED, Session 1 reads a range, Session 2 inserts a new row in that range and commits, then Session 1 reads the same range again and sees a different result.

Phantom read : With REPEATABLE READ, Session 1 reads an empty table, Session 2 inserts a row and commits, but Session 1’s second read still returns empty because the snapshot is unchanged; however, an attempt to insert a duplicate later fails, revealing the phantom.