Lock-free Data Definition Language (DDL) changes prevent tables from being locked during schema changes. This feature avoids service interruptions caused by locked tables and prevents latency on active/standby links that can occur with native Online DDL. We recommend that you submit lock-free changes during off-peak hours.
Background information
When you change the schema of a large table, you must consider the risk of table locking. A locked table prevents write operations. Native MySQL provides the following capabilities:
In MySQL 5.5 and earlier, DDL provides two execution algorithms:
Table-Copy: This algorithm makes changes by copying data to a temporary table. During this process, the source table is locked and does not allow write operations.
In-Place (available since MySQL 5.5): This algorithm allows read and write operations on the database while it runs. However, it supports only index-related changes.
In MySQL 5.6 and later, DDL also provides Innodb-OnlineDDL. For more information about Innodb-OnlineDDL, see Innodb-OnlineDDL.
This feature covers a wide range of DDL types, such as adding, deleting, or renaming columns, and adding or modifying indexes. However, it does not cover some common DDL types, such as modifying column types, modifying column lengths, or changing character sets.
Scenarios
Change the schema of a database table.
Change the character set and collation of a table, or adjust the time zone.
Reclaim tablespace and reduce fragmentation using OPTIMIZE TABLE operations without causing table locks. For more information, see Use lock-free schema changes to reclaim fragmented space.
Supported database types
RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, and other self-managed MySQL databases.
Features
Compared with native database features, the DMS lock-free schema change feature lets you control the execution speed of changes. This helps avoid latency on active/standby links, minimizes the impact on database performance, and supports many scenarios where native Online DDL would lock tables.
Compared with other tools such as pt-online-schema-change and OSC, the DMS lock-free schema change feature does not rely on triggers. This feature has a minimal impact on the database during asynchronous execution and can be safely interrupted at any time.
The DMS lock-free schema change feature is highly compatible with DTS. If a table being changed has a DTS table-level replication link, using the DMS lock-free schema change feature does not interrupt DTS replication.
NoteThe DTS replication link must have been restarted after February 14, 2020.
The following table compares the capabilities of native MySQL Online DDL and the DMS lock-free schema change feature.
Supported item
MySQL 5.5 and earlier
MySQL 5.6 and later
DMS lock-free schema change
Add column
N
Y
Y
Delete column
N
Yes
Yes.
Rename column
N
Yes
Yes
Add index
N
Yes
Yes.
Modify index
N
Y
Y
Defragmentation operation
N
Y
Yes
Modify column type
N
N
Y
Modify column length
N
N
Yes
Modify character set
N
N
Yes
Convert character set
N
N
Y
Time zone correction operation
N
N
Yes.
Mitigate or eliminate secondary database latency
N
N
Yes
No: Not supported.
Yes: Supported
For a comparison between the DMS lock-free schema change feature and other solutions, see Comparison of lock-free schema change solutions.
Notes
DMS supports schema changes for existing partitioned tables.
The lock-free schema change feature supports changing multiple tables in the same database within a single change ticket.
When you configure the change SQL in the ticket, use a semicolon (;) to separate the SQL statements for different tables.
When you perform a lock-free schema change on a table that has only a primary key or a unique key, do not update the primary key or unique key. Otherwise, the change task fails.
DMS in US regions does not support this feature.
How it works
When you submit a lock-free schema change ticket, DMS automatically performs the following operations to complete the change without locking the table.
Create a temporary table: DMS creates a temporary table in the target database with the same schema as the source table. This table is used for copying data.
The SQL syntax is
CREATE TABLE tmp_table_name LIKE table_name.Change the temporary table schema: The schema of the temporary table is modified as specified.
The SQL syntax is
ALTER TABLE tmp_table_name XXXX.Synchronize full data: The full data from the source table is synchronized 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 binary logging files and synchronize incremental data: Incremental data from the source table is synchronized to the temporary table.
The SQL syntax is
UPDATE/INSERT/DELETE tmp_table_name.Switch tables: The source table is renamed as a backup, and the temporary table replaces the source table.
The SQL syntax is
RENAME TABLE table_name to old_tmp_table_name, tmp_table_name to table_name.
tmp_table_name is an example temporary table name. You can find the actual temporary table name in the task progress section of the lock-free change. For more information, see View the progress of a lock-free change.
The following list describes the formats of temporary table names:
Data table: `tp_{Change_ID}_ogt_{Source_Table_Name}`, `tp_{Change_ID}_g_{Source_Table_Name}`
Heartbeat table: `tp_{Change_ID}_ogl_{Source_Table_Name}`, `tp_{Change_ID}_l_{Source_Table_Name}`
Auxiliary table before the switch and source table after the switch: `tp_{Change_ID}_del_{Source_Table_Name}`, `tp_{Change_ID}_d_{Source_Table_Name}`
Table lock detection: `tpa_xxx_xxx`
The change ID is the execution ID of the internal DMS engine. It is not the ticket number or task number.
References
After you understand the background and working principles of lock-free schema changes, you can perform the following operations:
Enable the lock-free schema change feature for an instance. For more information, see Enable lock-free schema changes.
Submit a lock-free schema change ticket. For more information, see Use a lock-free change ticket to perform lock-free schema changes.
(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 API operations to configure and manage lock-free schema change tickets:
API
Description
This API operation is used to create a lock-free change ticket.
This API operation is used to obtain the details of a lock-free change task, including the execution status and the number of affected data rows.
FAQ
Q: Do lock-free schema changes affect my services?
A: Under normal circumstances, your services are not affected. However, because the process involves data copying, it has a minor impact on instance performance.
Q: An error is reported when I run CREATE_INDEX to add an index in a lock-free change ticket. Does DMS not support adding indexes?
A: Yes, DMS supports adding indexes. If you encounter an error with CREATE_INDEX, you can use an alternative method, such as the `ALTER TABLE` statement:
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;