Does Adding an Index Lock the Table? JD Interview Deep Dive (Full Score Edition)

Interview Focus Points

The question tests four core abilities: version‑level differences (MySQL 5.5/5.6/8.0 lock mechanisms), the complete Online DDL workflow, algorithm and lock parameters (INPLACE/COPY/INSTANT, LOCK=NONE/SHARED), and online risk assessment for large‑table index creation.

Full‑Score Core Answer

MySQL 5.5 and earlier : uses the COPY algorithm, locks the entire table for the whole operation, blocking all reads and writes—unsafe for production.

MySQL 5.6 ~ 5.7 (Online DDL) : default INPLACE algorithm with LOCK=NONE; most of the time the table is not locked and supports concurrent reads/writes. Only the preparation and commit phases acquire a very short metadata lock, barely affecting business.

MySQL 8.0+ : secondary index addition supports INSTANT DDL, which takes zero time, performs no data copy, and incurs no lock—completion is in seconds and invisible to the workload.

Special Cases that Still Lock : adding a primary key, a unique index, a full‑text index, manually forcing the COPY algorithm, or operating on tables with uncommitted long transactions will still block reads/writes.

Deep Technical Analysis

1. MySQL 5.5 and Earlier – Full Table Lock

The COPY algorithm creates a temporary table with the new index, copies all data, drops the original table, and renames the temporary one. The whole process holds an exclusive table‑level lock, prohibiting any DML; on a million‑row table this can cripple production.

2. MySQL 5.6/5.7 Online DDL – Short Locks

Online DDL introduced the INPLACE algorithm, which modifies the index in place without copying the entire table. The operation consists of three phases:

Preparation phase : a brief exclusive metadata lock blocks new reads/writes for a few milliseconds.

Execution phase : the lock is downgraded to a shared lock, allowing full concurrent DML while the engine records incremental binlog changes.

Commit phase : another short exclusive metadata lock synchronizes remaining changes and updates metadata instantly.

Thus, from a business perspective the table appears unlocked, with only two millisecond‑level interruptions.

3. MySQL 8.0 INSTANT DDL – Zero Lock

INSTANT DDL, exclusive to 8.0+, modifies only metadata for ordinary secondary indexes. No data copy, no table scan, no incremental log sync are needed. The operation finishes in milliseconds with no lock, making large‑table index creation virtually risk‑free.

4. Scenarios That Must Lock

Adding a primary key (requires full table rebuild).

Adding a unique index (needs full‑table uniqueness check).

Adding a full‑text index.

Forcing ALGORITHM=COPY manually.

Tables with uncommitted or long‑running transactions, which can cause lock‑wait snowballing.

High‑Frequency Follow‑Up Questions

1. Is Online DDL truly lock‑free?

Not absolutely; the preparation and commit phases still acquire short‑lived metadata locks, but because they last only milliseconds, they are usually considered “no lock” in practice.

2. Why can large‑table index creation still cripple production?

During the long copy or rebuild phase, the system continuously records binlog changes, consuming I/O and CPU. If a long transaction remains uncommitted, it can trigger lock‑wait cascades and connection pile‑up, leading to a service snowball.

3. Differences among COPY, INPLACE, and INSTANT

COPY : copies data to a temporary table, locks the table for the entire duration, now deprecated.

INPLACE : modifies the index in place, core of Online DDL, with short, controllable locks.

INSTANT : 8.0‑only, changes only metadata, zero time, zero lock.

4. Recommended Production Solution for Large Tables

Prefer external tools such as pt‑online‑schema‑change or gh‑ost , which avoid native DDL’s short locks and long‑transaction blocking, achieving truly invisible schema changes.

5. What Kind of Lock Blocks DDL?

DDL acquires a metadata lock (MDL), which is a table‑level lock distinct from InnoDB row or table locks. Once held, it blocks all reads and writes on the table.

Mnemonic Summary

“55‑6 lock 57‑8 live, 80 instant no pain” – 5.5 and earlier lock the whole table; 5.6‑5.7 use Online DDL with brief locks; 8.0+ uses instant DDL with no lock.

Additional note: primary, unique, and full‑text indexes never support lock‑free online creation.

Final Interview Summary

Whether adding an index locks a MySQL table is determined by version and index type. Versions ≤5.5 lock the table completely; 5.6‑5.7 employ Online DDL with only millisecond‑level metadata locks; 8.0+ introduces INSTANT DDL for ordinary secondary indexes, achieving near‑zero risk. However, primary, unique, and full‑text indexes still require locking. For production‑grade large‑table changes, using tools like pt‑online‑schema‑change or gh‑ost is recommended to avoid any blocking.