Unlock MySQL Storage Engines: InnoDB vs MyISAM and Transaction Essentials

MySQL storage engines are the underlying file‑system mechanisms that manage how data is stored, accessed, and protected.

File System Overview

A file system is a software layer that organizes and stores data on disk. Different file systems (ext2/3/4, XFS, etc.) keep data content unchanged but vary in storage space, size, and speed.

MySQL Engine Concept

MySQL’s engine can be seen as its own file system with richer functionality, providing basic CRUD as well as transaction support, locking, backup/recovery, optimization, and special features.

Built‑in Storage Engines

InnoDB

MyISAM

MEMORY

ARCHIVE

FEDERATED

EXAMPLE

BLACKHOLE

MERGE

NDBCLUSTER

CSV

Third‑Party Engines

PerconaDB

MariaDB

Viewing Engines

# Show all engines
show engines;

# List InnoDB tables
select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables where engine='innodb';

# List MyISAM tables
select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables where engine='myisam';

InnoDB vs MyISAM

Physical Differences

# InnoDB files
student.frm   # table definition
student.ibd   # data file

# MyISAM files
user.frm       # table definition
user.MYD       # data file
user.MYI       # index file

Logical Differences

Since MySQL 5.5, InnoDB is the default engine, offering ACID compliance, MVCC, row‑level locking, foreign‑key support, high performance on large data sets, automatic crash recovery, and a buffer pool for caching.

InnoDB Core Features

MVCC (Multi‑Version Concurrency Control)

Transactional support (ACID)

Row‑level locks

Hot backup

Crash‑Safe Recovery

Table Spaces

Shared table space (ibdata1, ib_logfile*) – default before MySQL 5.6.

Independent table space – each table has its own .ibd file when innodb_file_per_table is ON.

Transactions

What Is a Transaction?

A transaction groups a set of DML statements (INSERT, UPDATE, DELETE) into a single unit of work that is either fully committed or fully rolled back, ensuring atomicity, consistency, isolation, and durability (ACID).

Isolation Levels

# Show current isolation level
show variables like '%iso%';

# Levels
READ UNCOMMITTED – allows dirty reads
READ COMMITTED – allows only committed data
REPEATABLE READ – default for InnoDB, guarantees repeatable reads
SERIALIZABLE – full isolation

Transaction Control Statements

# Start a transaction
BEGIN;   -- or START TRANSACTION

# Savepoint
SAVEPOINT abc;

# Commit
COMMIT;

# Rollback
ROLLBACK;
ROLLBACK TO SAVEPOINT abc;

# Disable autocommit temporarily
SET autocommit=0;

# Disable permanently (my.cnf)
[mysqld]
autocommit=0

Implicit Commits

Implicit commits occur when a new transaction is started before the previous one finishes, when DDL/DCL statements are executed, when LOCK/UNLOCK tables are used, during LOAD DATA INFILE, or when autocommit is enabled.

Transaction Logs

Redo log – Write‑Ahead Log that guarantees durability.

Undo log – used to roll back uncommitted changes.

Both logs are essential for crash‑safe recovery.

Locks

Exclusive (row‑level) lock – ensures data consistency during updates.

Shared lock – allows reads but blocks writes.

Optimistic lock – “first‑committer wins”.

Pessimistic lock – blocks other transactions until completion.

MVCC

Multi‑Version Concurrency Control allows concurrent reads without blocking writes, implementing optimistic locking.

Practical Migration Example

A company migrated from a LAMP site using MyISAM (MySQL 5.1) to InnoDB on MySQL 5.6 to solve table‑level locking and lack of automatic crash recovery.

# Migration steps (simplified)
1. Plan downtime and document procedures.
2. Prepare new environment (CentOS, MySQL 5.6, tuned my.cnf).
3. Stop application services.
4. Stop MySQL.
5. Backup all databases (mysqldump --single-transaction).
6. Transfer backup to new server.
7. Convert tables:
   ALTER TABLE student ENGINE='InnoDB';
   ALTER TABLE student CHARSET='latin1';
8. Verify tables and start services.
9. Cut over production traffic.

Enterprise Recovery Scenario

When a power loss corrupted a MyISAM table, the recovery process involved creating a new InnoDB table, discarding the old tablespace, copying the .ibd file, importing the tablespace, and renaming the table.

# Example commands
ALTER TABLE world.city1 DISCARD TABLESPACE;
# copy .ibd file
ALTER TABLE world.city1 IMPORT TABLESPACE;