Lockless DDL Overview for Schema Changes - Data Management

A lock-free DDL change prevents table locking during schema modifications. This avoids business disruptions from locked tables and eliminates the primary/standby latency that can occur with native Online DDL. You can submit lock-free changes during off-peak hours.

Background

When you modify the schema of a large table, you risk locking the table, which blocks write operations from your applications.

In MySQL 5.5 and earlier, Data Definition Language (DDL) operations use one of two algorithms: Table-Copy or In-Place.
- Table-Copy: This algorithm creates a temporary table to complete the change. During this process, the original table is locked, preventing write operations.
- In-Place: Introduced in MySQL 5.5, this algorithm allows read and write operations during the change but only supports index-related operations.
In MySQL 5.6 and later, DDL also includes Innodb-OnlineDDL. For more information about Innodb-OnlineDDL, see Innodb-OnlineDDL.
While Innodb-OnlineDDL supports a wide range of DDL operations such as add column, drop column, r************, add index, and modify index, it does not cover some common operations, including modify column type, m*******************, and modify character set.

Use cases

Change the schema of a database table.
Modify the character set and collation of a table or perform a time zone correction.
Use the OPTIMIZE TABLE operation to reclaim table space and reduce fragmentation without locking tables. For more information, see Reclaim fragmented space with lock-free schema change.

Supported database types

ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, and other MySQL sources.

Key features

Compared to native database operations, DMS lock-free schema change allows you to control the execution speed of the change. This minimizes the impact on database performance, helps prevent primary/standby latency, and supports many scenarios where native Online DDL would lock the table.
Compared to other tools like PT-Online and OSC, DMS lock-free schema change does not rely on triggers. Its asynchronous execution has minimal impact on the database and can be safely interrupted at any time.
DMS lock-free schema change is compatible with DTS. If a table being modified has a DTS table-level replication link, using DMS lock-free schema change does not interrupt DTS replication.
Note
The DTS replication link must have been restarted after February 14, 2020.

The following table compares the capabilities of native MySQL Online DDL and DMS lock-free schema change.

Operation	MySQL 5.5 and earlier	MySQL 5.6 and later	DMS lock-free schema change
Add column	N	Y	Y
Drop column	N	Y	Y
Rename column	N	Y	Y
Add index	N	Y	Y
Modify index	N	Y	Y
Defragmentation	N	Y	Y
Modify column type	N	N	Y
Modify column length	N	N	Y
Modify character set	N	N	Y
Convert character	N	N	Y
Time zone correction	N	N	Y
Mitigate or eliminate standby latency	N	N	Y
Add unique key	Y	Y	N
Add constraint	Y	Y	N

N: Not supported.
Y: Supported.

For a comparison between DMS lock-free schema change and other solutions, see Comparison of lock-free schema change solutions.

Usage notes

DMS supports schema changes on existing partitioned tables.
You can use a single lock-free schema change ticket to modify multiple tables in the same database.
When you configure the SQL statements for the ticket, you can separate the statements for different tables with a semicolon (;).
During a lock-free schema change on a table with only a primary key or a unique key, you cannot update these keys. Doing so causes the change task to fail.

How it works

When you submit a lock-free schema change ticket, DMS automatically performs the following steps to execute the change without locking the table:

Create a temporary table. DMS creates a temporary table in the target database that mirrors the original table's schema to prepare for the data copy.
SQL syntax: CREATE TABLE tmp_table_name LIKE table_name.
Modify the temporary table schema. DMS modifies the temporary table's schema according to the specified changes.
SQL syntax: ALTER TABLE tmp_table_name XXXX.
Copy full data. DMS synchronizes all data from the original table to the temporary table.
SQL syntax: INSERT IGNORE INTO tmp_table_name (SELECT %s FROM table_name FORCE INDEX (%s) WHERE XXX LOCK IN SHARE MODE).
Parse the binlog and synchronize incremental data. DMS synchronizes changes made to the original table during the copy process to the temporary table.
SQL syntax: UPDATE/INSERT/DELETE tmp_table_name.
Switch tables. DMS renames the original table as a backup and the temporary table to replace the original.
SQL syntax: RENAME TABLE table_name to old_tmp_table_name, tmp_table_name to table_name.

Note

tmp_table_name is an example name for the temporary table. You can find the actual name of the temporary table in the Lock-free change task progress section. For more information, see View the progress of a lock-free change.

The following formats are used for temporary table names:

Data table: tp_{ChangeID}_ogt_{OriginalTableName} or tp_{ChangeID}_g_{OriginalTableName}
Heartbeat table: tp_{ChangeID}_ogl_{OriginalTableName} or tp_{ChangeID}_l_{OriginalTableName}
Auxiliary table before the switch and original table after the switch: tp_{ChangeID}_del_{OriginalTableName} or tp_{ChangeID}_d_{OriginalTableName}
Table lock detection table: tpa_xxx_xxx

Note

The change ID is an internal execution ID of the DMS engine, not the ticket ID or task ID.

References

After understanding the background and principles of lock-free schema change, you can perform the following operations:
1. Enable the lock-free schema change feature for your instance. For more information, see Enable lock-free schema change.
2. Submit a lock-free schema change ticket. For more information, see Perform lock-free schema change by using a ticket.
3. (Optional) View the progress of a lock-free change task. For more information, see View the progress of a lock-free change.

You can also use APIs to configure and manage lock-free schema change tickets:

API	Description
CreateFreeLockCorrectOrder	Creates a lock-free change ticket.
GetDataCorrectTaskDetail	Retrieves the details of a lock-free change task, including its execution status and the number of affected data rows.

FAQ

Q: Does lock-free schema change affect my business?
A: Under normal circumstances, this process does not affect your business operations. However, because it involves data copying, it may slightly impact instance performance.
Q: I tried to add an index by using the CREATE_INDEX statement in a lock-free change ticket, but it failed. Does DMS not support adding an index?
A: DMS supports adding an index. You can run the following ALTER TABLE statement to add an index:
```
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;
```
Q: I tried to rename a column by using the rename column statement in a lock-free change ticket, but it failed. Does DMS not support renaming a column?
A: DMS supports renaming a column. You can run the following ALTER TABLE statement to rename a column:
```
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype;
```