MySQL Table Locking: What Happens When You Add a Column or Index?

Modifying the structure of a MySQL table often involves the issue of table-level locking.

This is especially significant for tables with large volumes of data, as it can have a noticeable impact on the performance of business systems.

By optimizing table structure modification operations, developers can avoid or minimize table lock time, thereby ensuring the system runs smoothly.

Introduction to Table-Level Locks

Table-level locks refer to the locking of an entire table during certain operations to ensure data consistency.

Specifically:

When executing an ALTER TABLE operation on a table, MySQL by default locks the table, blocking other transactions from reading or writing to it until the ALTER TABLE operation completes.

This table-locking behavior has little impact on small tables or systems with low concurrency.

However, when dealing with large datasets or high-concurrency business systems, table locks may cause severe performance bottlenecks and even lead to system crashes.

Specific Impacts of Table Locking

When MySQL performs operations like adding a column, table locking can lead to the following issues:

Query delays: All SQL queries on that table will be blocked until the table lock is released.
Blocked write operations: All write operations (such as INSERT, UPDATE, DELETE) on the table will be put on hold until the lock is released.
System slowdown: If a table lock operation takes too long to complete, the overall performance of the business system will drop significantly, potentially causing a system outage.

However, in newer versions of MySQL, when adding a column to a table using the InnoDB storage engine, table locks may not necessarily occur.

The InnoDB storage engine provides some mechanisms to reduce table locking and improve concurrent performance.

In MySQL, whether adding a column to a table locks it depends on the storage engine used and the version of MySQL.

Before MySQL 5.6

In earlier versions of MySQL, using the ALTER TABLE command to add a column would, by default, lock the table if it used the InnoDB storage engine.

This means that during the operation, the table would be locked, preventing other read and write operations until the operation is complete.

This full-table locking behavior can result in long wait times and application interruptions when executing ALTER TABLE operations on large tables.

So: Before MySQL 5.6, modifying the table structure directly would lock the table.

The specific steps are as follows:

First, create a new temporary table with the modified structure using the ALTER TABLE command.
Then, import the data from the original table into the temporary table.
Delete the original table.
Finally, rename the temporary table to the original table's name.

MySQL versions 5.6 and 8.0 introduced optimizations to address table-locking issues.

MySQL 5.6

Starting from MySQL 5.6, InnoDB introduced Online DDL operations, allowing certain table modifications to be performed without locking the table.

Adding a column is an online operation and can use ALGORITHM=INPLACE to avoid full table locking.

ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE indicates that the in-place algorithm should be used for the modification, which is part of the online DDL mechanism.
- It explicitly instructs MySQL to attempt modifying the table structure in-place.
- MySQL will try to apply changes without recreating the entire table whenever possible.
LOCK=NONE means the table should not be locked, minimizing the impact on concurrent queries.
- Other sessions are allowed to read and write to the table.
- However, there may still be some risk of data inconsistency.

This greatly improves execution efficiency and avoids table locking.

However, this is divided into two cases:

Adding a non-nullable column:

A fast metadata-only operation is performed, without locking the entire table.
Other sessions can continue to read from and write to the table during the modification.

Adding a nullable column:

Also performed as a fast metadata-only operation without locking the whole table.
Other sessions can continue read and write operations, though brief row-level locks may occur during the modification.

Note:

Although the InnoDB storage engine provides reduced locking, executing ALTER TABLE may still have performance implications.

These are due to internal metadata operations, data reorganization, or log writing.

Therefore, when modifying the structure of large tables, it's still recommended to perform the operation during off-peak hours to minimize the impact on applications.

MySQL 8.0

MySQL 8.0 introduces new features that allow most ALTER TABLE operations to be performed without locking the table.

In simple terms: It enhances the capabilities of Online DDL operations.

By default, in MySQL 8.0, simple ALTER TABLE operations (like adding a column) usually do not lock the table.

To check whether a specific ALTER TABLE operation will lock the table, you can use the EXPLAIN statement before executing it:

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

This command displays the execution plan information, including whether the table will be locked.

Here are some specific optimizations in MySQL 8.0:

Atomic DDL

MySQL 8.0 introduces Atomic DDL operations, meaning that the execution of ALTER TABLE statements involves less blocking.
When adding a column, the Atomic DDL mechanism reduces table lock time and allows other sessions to continue reading and writing data.

Instant Metadata Updates

In MySQL 8.0, the table’s metadata is updated immediately when a column is added, without waiting for the entire operation to complete.
This enables faster completion of the ALTER TABLE operation and reduces lock time.

InnoDB Engine Optimizations

The InnoDB engine in MySQL 8.0 has been optimized for structural changes to large data tables.
For example, when adding non-nullable columns, InnoDB no longer needs to copy all the table data.
Instead, it uses a lighter-weight operation to add the new field, reducing lock time and resource usage.

Incremental Metadata Updates

MySQL 8.0 introduces Incremental Metadata Updates, which means only the affected metadata needs to be updated during the ALTER TABLE operation, rather than the entire table.
This reduces lock time and overhead.

Online DDL

Online DDL refers to executing Data Definition Language (DDL) operations while the database is running.

For example, creating, modifying, or deleting table structures, indexes, etc., without causing long-term locking or unavailability of the database.

Traditional DDL operations typically require exclusive locks on the affected tables.

This may prevent other sessions from performing read or write operations on the table, thereby affecting normal database usage.

Currently, three major algorithms are supported:

COPY: Used in pre-MySQL 5.6 versions, this is a non-online algorithm.
INPLACE: Introduced in MySQL 5.6.
INSTANT: Introduced in MySQL 8.0.12 (contributed by the Tencent DBA team).

Basic Principles

During DDL execution—regardless of the algorithm used—it generally goes through three phases:

Preparation phase
Execution phase (DDL)
Commit phase

The difference lies in the optimizations applied at each of these phases depending on the algorithm.

For implementation details, refer to the official documentation:

Summary

Since MySQL 5.6, simply adding a column, modifying table structures, or adding indexes generally does not require locking the entire table.

However, in some cases, MySQL may still need to lock the whole table.

Also, when dealing with large amounts of data, performance issues may still occur.

Therefore, during actual operations, pay attention to the amount of data in the table and its eventual size (especially index data).

At the same time, if your MySQL version is relatively old or if online DDL operations are not supported due to specific reasons, be sure to perform ALTER TABLE operations during off-peak hours to minimize the impact on business systems.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

Pay-as-you-go with no idle charges.
Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

Intuitive UI for effortless setup.
Fully automated CI/CD pipelines and GitOps integration.
Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

Auto-scaling to handle high concurrency with ease.
Zero operational overhead — just focus on building.

Explore more in the Documentation!