Lock-free table schema change-Data Management-阿里云帮助中心

Lock-free DDL changes prevent table locking during schema changes, avoiding business disruptions and replication lag caused by native online DDL operations.

Background

When performing a schema change on a large table, you must consider the risk of table locks blocking write operations.

In MySQL 5.5 and earlier versions, DDL supports two execution algorithms: Table-Copy and In-Place.
- Table-Copy: Copies data to a temporary table, locking the source table and blocking writes until the change is complete.
- In-Place (available since MySQL 5.5): Allows read and write operations during execution but supports only index-related operations.
MySQL 5.6 and later versions also introduced Innodb-OnlineDDL. For more information about Innodb-OnlineDDL, see Innodb-OnlineDDL.

Innodb-OnlineDDL covers a wide range of DDL types, such as adding or deleting columns, renaming columns, and adding or modifying indexes. However, it does not support some common types, such as modifying column types, changing column lengths, or altering character sets.

Use cases

Change the schema of a database table.
Change the character set and collation of a table, or adjust its time zone.
Avoid table locks from OPTIMIZE TABLE operations that reclaim space and reduce the fragmentation rate. For more information, see Defragment space by using lock-free schema change.

Supported database types

RDS MySQL, PolarDB for MySQL, MyBase for MySQL, and other MySQL-compatible databases.

Features

The DMS lock-free schema change feature lets you control execution speed, helping prevent replication lag and minimize database performance impact. It also supports changes where native online DDL would typically lock a table.
Unlike pt-online-schema-change and OSC, the DMS lock-free schema change feature does not rely on triggers. It runs asynchronously with minimal impact on the database and can be safely interrupted at any time.
The DMS lock-free schema change feature is highly compatible with DTS and does not interrupt table-level DTS replication links.

Note
The DTS replication link must have been restarted after February 14, 2020.

The following table compares native MySQL online DDL capabilities with the DMS lock-free schema change feature:

Operation	MySQL 5.5 and earlier	MySQL 5.6 and later	DMS lock-free schema change
Add a column	❌	✅	✅
Delete a column	❌	✅	✅
Rename a column	❌	✅	✅
Add an index	❌	✅	✅
Modify an index	❌	✅	✅
Defragment a table	❌	✅	✅
Modify a column type	❌	❌	✅
Modify a column length	❌	❌	✅
Modify a character set	❌	❌	✅
Convert a character set	❌	❌	✅
Adjust a time zone	❌	❌	✅
Mitigate or eliminate read replica delay	❌	❌	✅
Add a unique key	✅	✅	✅
Add a constraint	✅	✅	✅

Important

When you add a unique key or a constraint using the DMS lock-free schema change feature, be aware that any data in the source table that violates the new unique key or constraint is automatically discarded by MySQL during the copy process to the temporary table.

For a comparison of the DMS lock-free schema change feature with other solutions, see Comparison of lock-free schema change solutions.

Usage notes

DMS supports schema changes for existing partitioned tables.
You can use a single lock-free schema change ticket to modify multiple tables within the same database.

When you configure the SQL statements for the ticket, you can separate the statements for different tables with a semicolon (;).
You cannot update a table's primary or unique key during a lock-free schema change if it is the only key on the table. Attempting this will cause the task to fail.

How it works

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

Create a temporary table. DMS creates a new table that mirrors the schema of the source table.

The SQL syntax is CREATE TABLE tmp_table_name LIKE table_name.
Alter the temporary table schema. DMS applies the requested DDL changes to the temporary table.

The SQL syntax is ALTER TABLE tmp_table_name XXXX.
Copy the full data. DMS copies all data from the source table to the temporary table.

The SQL syntax is INSERT IGNORE INTO tmp_table_name (SELECT %s FROM table_name FORCE INDEX (%s) WHERE XXX LOCK IN SHARE MODE).
Parse the binlog to synchronize incremental data. DMS applies ongoing changes from the source table's binlog to the temporary table.

The SQL syntax is UPDATE/INSERT/DELETE tmp_table_name.
Swap the tables. DMS atomically renames the source table to a backup name and the temporary table to the original table's name.

The SQL syntax is RENAME TABLE table_name to old_tmp_table_name, tmp_table_name to table_name.

Note

tmp_table_name is an example temporary table name. You can find the specific temporary table name in the progress area of the lock-free schema change task. For more information, see View lock-free schema change progress.

The format for temporary table names is as follows:

Data table: tp_{change_ID}_ogt_{original_table_name}, tp_{change_ID}_g_{original_table_name}
Heartbeat table: tp_{change_ID}_ogl_{original_table_name}, tp_{change_ID}_l_{original_table_name}
Auxiliary table before switchover and original table after switchover: tp_{change_ID}_del_{original_table_name}, tp_{change_ID}_d_{original_table_name}
Table lock detection: tpa_xxx_xxx

Note

The change ID is the execution ID for the internal DMS engine. It is not the ticket ID or task ID.

Frequently asked questions

Q: Does a lock-free schema change affect my services?

A: A lock-free schema change does not normally affect your services. However, the data copying process can impact instance performance.
Q: I received an error when I tried to add an index with CREATE_INDEX in a lock-free schema change ticket. Does DMS not support adding indexes?

A: Yes, DMS supports adding indexes. Run the following ALTER TABLE statement to add an index:
```
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;
```
Q: I received an error when I tried to rename a column with rename column in a lock-free schema change ticket. Does DMS not support renaming columns?

A: Yes, DMS supports renaming columns. Run the following ALTER TABLE statement to rename a column:
```
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype;
```

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

Lock-free schema change