Lock-free schema change - Data Management - Alibaba Cloud Documentation Center

Data Management (DMS) provides the lock-free DDL feature to help you change schemas without the need to lock tables. This prevents your business from being affected by table locking that is caused by schema changes. This also prevents the synchronization latency between the primary and secondary databases from occurring when your table schemas are changed by using native online DDL operations. We recommend that you submit lock-free change tickets during off-peak hours.

Background information

If the table for which you want to change the schema contains a large volume of data, the table may be locked when you change its schema. In this case, data cannot be written to the table. MySQL provides the following native capabilities:

In MySQL 5.5 and earlier, the Table-Copy and In-Place algorithms can be used for DDL statements.
- Table-Copy: The system changes the schema of a table by generating a temporary table and copying data from the original table to the temporary table. In this process, the original table is locked and you cannot write data to the original table.
- In-Place: In MySQL 5.5 and later, the In-Place algorithm is available. When you add or modify indexes for a table by using this algorithm, data read and write operations on the table are supported.
In MySQL 5.6 and later, InnoDB-based online DDL operations are supported. For more information, see InnoDB and Online DDL.
This allows you to perform a wide range of DDL operations online. For example, you can create, delete, or rename columns, and add or modify indexes. However, some regular DDL operations are not supported. For example, you cannot change the data types or lengths of columns, or change the character set online.

Scenarios

Change the schemas of database tables.
Change character sets and collations for tables and adjust time zones.
Prevent table locking that occurs when you execute the OPTIMIZE TABLE statement to recycle space fragments. For more information, see Recycle tablespace fragments.

Supported database types

ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, and MySQL databases from other sources

Benefits

Compared with the native capabilities of MySQL, DMS allows you to change schemas at a manageable speed. This prevents the latency in synchronization between primary and secondary databases and has less impact on database performance. In addition, you can apply this feature to various scenarios in which tables may be locked when schemas are changed by using native online DDL operations.
Compared with other tools such as pt-online-schema-change and Online Schema Change (OSC), DMS allows you to perform lock-free schema changes without a trigger. You can change schemas in an asynchronous manner, which has a minor impact on databases. In addition, you can interrupt schema changes at any time in a safe manner.
The lock-free schema change feature of DMS can seamlessly work with Data Transmission Service (DTS). If a DTS copy link is configured for a table, table copy will not be interrupted when the schema of the table is being changed.
Note
The DTS copy link must have been restarted since February 14, 2020.

The following table compares the online DDL feature of MySQL and the lock-free DDL feature of DMS.

Operation	Online DDL in MySQL 5.5 and earlier	Online DDL in MySQL 5.6 and later	Lock-free DDL in DMS
Create columns	N	Y	Y
Delete columns	N	Y	Y
Rename columns	N	Y	Y
Create indexes	N	Y	Y
Modify indexes	N	Y	Y
Recycle space fragments	N	Y	Y
Change data types of columns	N	N	Y
Change column lengths	N	N	Y
Change character sets	N	N	Y
Convert characters	N	N	Y
Correct time zones	N	N	Y
Alleviate or eliminate latency in secondary databases	N	N	Y

N: indicates that the operation is not supported.
Y: indicates that the operation is supported.

For more information about the comparison between the lock-free DDL feature of DMS and other schema change solutions, see Comparison of lock-free schema change solutions.

Usage notes

DMS allows you to change the schema of a partitioned table.
You can use the lock-free schema change feature to change multiple tables in the same database by using a data change ticket.
When you specify SQL statements for a schema change, separate SQL statements for different tables with semicolons (;).
If the table for which you want to change the schema contains only a primary key or a unique key, you cannot update the primary key or unique key during the schema change. Otherwise, the schema change task fails.

Principle of lock-free schema change

Create a temporary table. Statement: CREATE TABLE tmp_table_name LIKE table_name.
Change the schema of the temporary table. Statement: ALTER TABLE tmp_table_name XXXX.
Copy the full data of the original table to the temporary table. Statement: INSERT IGNORE INTO tmp_table_name (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx.
Use binary logs to synchronize incremental data. Statement: UPDATE/INSERT/DELETE tmp_table_name.
Swap the names of the temporary table and the original table. Statement: RENAME TABLE table_name to old_tmp_table_name, tmp_table_name to table_name.

DMS names temporary tables in the following formats:

Data table: tp_{Change ID}_ogt_{Original table name} or tp_{Change ID}_g_{Original table name}
Heartbeat table: tp_{Change ID}_ogl_{Original table name} or tp_{Change ID}_l_{Original table name}
Temporary table before the switchover and original table after the switchover: tp_{Change ID}_del_{Original table name} or tp_{Change ID}_d_{Original table name}
Table that DMS checks whether it is locked: tpa_xxx_xxx

Note

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

References

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

You can also call the following API operations to configure and manage lock-free schema change tickets.

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

FAQ

Q: Does lock-free schema change affect my business?
A: Your business is not affected during lock-free schema change. However, the instance performance is affected because data is copied during lock-free schema change.
Q: What do I do if an error is reported when the CREATE_INDEX statement is executed in a lock-free change ticket to add an index? Can I add indexes by using DMS?
A: DMS allows you to add indexes. If an error is reported when you add an index, you can attempt to execute another statement such as ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;.