Lockless schema evolution - Data Management - Alibaba Cloud Documentation Center

Lockless Data Definition Language (DDL) changes prevent tables from being locked during schema evolution. This feature avoids business interruptions and the latency on active/standby links that native online DDL can cause. You should submit lockless changes during off-peak business hours.

Background information

Changing the schema of a large table can lock the table. A locked table blocks write operations. Native MySQL features include the following:

MySQL 5.5 and earlier versions provide two DDL execution algorithms: Table-Copy and In-Place.
- Table-Copy: Copies data to a temporary table to complete the change. The table is locked and cannot be written to during the change.
- In-Place (available since MySQL 5.5): The database can be read from and written to while this algorithm runs. However, it supports only operations on indexes.
MySQL 5.6 and later versions also provide Innodb-OnlineDDL. For more information about Innodb-OnlineDDL, see Innodb-OnlineDDL.
This feature supports a wide range of DDL types, such as adding columns, deleting columns, renaming columns, adding indexes, and modifying indexes. However, it does not support some common DDL types, such as modifying column types, modifying column lengths, and modifying character sets.

Scenarios

Changing the table schema of a database.
Changing the character set and collation of a table, or adjusting the time zone.
Avoiding locked tables caused by `Optimize Table` operations that reclaim tablespace and reduce the fragmentation rate. For more information, see Reclaim fragmented space with lockless schema evolution.

Supported database types

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

Features

Compared to native database features, DMS lockless schema evolution lets you control the execution speed of changes. This capability reduces the impact on database performance, avoids latency on active/standby links, and supports many scenarios where native online DDL would lock the table.
Compared to other tools such as PT-Online and OSC, DMS lockless schema evolution does not depend on triggers. This feature has a minimal impact on the database during asynchronous execution and can be safely interrupted at any time.
DMS lockless schema evolution is highly compatible with Data Transmission Service (DTS). If a table undergoing a schema change has a DTS table-level replication task, using DMS for the lockless schema evolution does not cause a replication interruption.
Note
The DTS replication task must have been restarted after February 14, 2020.

Comparison between native MySQL online DDL and DMS lockless schema evolution:

Supported resources	MySQL 5.5 and earlier	MySQL 5.6 and later	DMS lockless schema evolution
Add column	No	Yes	Yes
Delete column	N	Yes	Yes
Rename column	N	Yes	Yes
Add index	N	Yes	Yes
Modify index	N	Yes.	Yes
Defragmentation	N	Yes	Yes
Modify column type	N	N	Yes
Modify column length	N	N	Yes
Modify character set	N	N	Yes
Convert characters	N	No.	Yes
Adjust time zone	N	N	Yes
Reduce or eliminate secondary database latency	N	N	Yes

No: Not supported.
Yes: Supported

For a comparison of DMS lockless schema evolution with other solutions, see Comparison of lockless schema evolution solutions.

Precautions

DMS supports schema evolution for existing partitioned tables.
Lockless schema evolution supports changing multiple tables in the same database within a single ticket.
When you configure the change SQL for the ticket, use a semicolon (;) to separate the SQL statements for different tables.
When you perform a lockless schema evolution on a table that has only a primary key or only a unique key, do not update the columns of that key. Otherwise, the change task will fail.
DMS in the US (Silicon Valley) region does not support this feature.

How it works

When you submit a lockless schema evolution 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 original table. This temporary table is used to copy data.
The SQL syntax is CREATE TABLE tmp_table_name LIKE table_name.
Modify temporary table structure: You can modify the structure of the temporary table.
The SQL syntax is ALTER TABLE tmp_table_name XXXX.
Copy full data: DMS copies all data from the original 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 binary logs and synchronize incremental data: DMS synchronizes incremental data from the original table to the temporary table.
The SQL syntax is UPDATE/INSERT/DELETE tmp_table_name.
Switch tables: DMS renames the original table to a backup name and then renames the temporary table to the original table 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 of a temporary table name. You can find the actual temporary table name on the task progress page for the lockless change. For more information, see View the progress of a lockless change.

The formats for temporary table names are 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}`
Helper table before the switch and original table after the switch: `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 from the internal DMS engine. It is not the ticket number or the task number.

References

You can perform the following operations:
1. Enable the lockless schema evolution feature for an instance. For more information, see Enable lockless schema evolution.
2. Submit a lockless schema evolution ticket. For more information, see Perform lockless schema evolution using a ticket.
3. (Optional) View the progress of the lockless change task. For more information, see View the progress of a lockless change.

You can also use API operations to configure and manage lockless schema evolution tickets:

API	Description
CreateFreeLockCorrectOrder	Creates a lockless change ticket.
GetDataCorrectTaskDetail	Queries the details of a lockless change ticket, including the execution status and the number of affected data rows.

FAQ

Q: Does lockless schema evolution affect my business?
A: Your services are not affected. However, instance performance will be slightly impacted because the change process involves data copying.
Q: I received an error when I tried to add an index by running CREATE_INDEX in a lockless change ticket. Does DMS not support adding indexes?
A: DMS supports adding indexes. You can use 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 field by running rename column in a lockless change ticket. Does DMS not support renaming fields?
A: DMS supports renaming fields. You can use the following ALTER TABLE statement to rename a field:
```
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype;
```