Get started with lock-free schema changes - Data Management

Data Management (DMS) provides the lock-free schema change 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. This topic describes how to use the lock-free schema change feature of DMS. In this example, ApsaraDB RDS for MySQL is used.

Create an instance and enable lock-free schema change

Log on to the DMS console V5.0.
In the left-side navigation pane, click the icon.
Note
If you use the DMS console in simple mode, click Database Instances in the left-side navigation pane and then click the icon.

In the Add Instance dialog box, configure the instance information.

On the Alibaba Cloud tab, select MySQL.

Configure instance information in the Basic Information and Advanced Information sections. For more information, see Register an Alibaba Cloud database instance.

Section	Parameter	Description
Data Source	N/A	The source of the database instance. In this example, Alibaba Cloud is selected.
Basic Information	Database Type	The type of the database instance.
	Instance Region	The region in which the database instance resides.
	Other primary accounts	This parameter appears if you click Cross-Alibaba Cloud account instances. The Alibaba Cloud account to which the database instance belongs. Note If the Alibaba Cloud account is not in the list, you can click Add User to add the Alibaba Cloud account that is used to purchase the database instance. For more information, see the Add a user section of the "Manage users" topic.
	Entry mode	The method that you use to specify the information about the database instance. Valid values: Instance ID and Connection string address.
	Instance ID or Connection string address	The ID of the database instance or the connection string of the database instance. Note The connection string is in the following format: Internal network address:Port number. Example: rm-XXXXXXX.mysql.rds.aliyuncs.com:3306.
	Database Account	The account that is used to log on to the database. Note You can use the specified database account to perform operations such as read and write on the data of the database instance in DMS. You must specify a database account that has the required permissions.
	Database Password	The password of the account that is used to log on to the database.
	Control Mode	The control mode that is used to manage the database instance. For more information, see Control modes.
	Sensitive Data Protection	Specifies whether to enable the sensitive data protection feature. This feature allows you to control and mask sensitive data. For more information, see Enable the sensitive data protection feature.
	Classification template	If you turn on Sensitive Data Protection, the Classification template parameter appears. You can bind a classification and grading template to an instance to identify whether the fields in databases and tables in the instance comply with the identification rules of the template. If the fields comply with the identification rules, the fields are labeled for classification and grading to protect the fields with high sensitivity levels. For more information, see Manage DMS classification and grading templates.
Advanced Information	Environment type	The type of the environment in which the database instance is deployed. For more information, see the Environment types section of the "Change the environment type of an instance" topic.
	Instance Name	The name that you specify for the database instance. Note If you register an Alibaba Cloud database instance for the first time, the name of the Alibaba Cloud database instance is synchronized to DMS. DMS does not synchronize the name if the Alibaba Cloud database instance is not registered for the first time. You can change the name of the database instance in the DMS console. For more information, see Modify database instances.
	Lock-free Schema Change	Specifies whether to enable lock-free schema change. Valid values: Open (DMS OnlineDDL first), Open (MySQL Native OnlineDDL first), and Close. For more information, see Enable the lock-free schema change feature. Note This parameter appears only for a MySQL database instance.
	Enable SSL	Specifies whether to allow DMS to connect to the database instance by using SSL connections. SSL encrypts network connections at the transport layer to improve the security and integrity of data in transmission. However, SSL increases the response time of network connections. Before you use SSL connections, make sure that the SSL encryption feature is enabled for the database instance. Valid values: Default (DMS automatically checks whether self-negotiation is enabled for the database instance.): DMS automatically checks whether the SSL encryption feature is enabled for the database instance. If the SSL encryption feature is enabled, DMS connects to the database instance by using SSL connections. Otherwise, DMS connects to the database instance without encryption. Enabled: DMS connects to the database instance by using SSL connections. This value is invalid if the SSL encryption feature is disabled for the database instance. Close: DMS does not connect to the database instance by using SSL connections. Note This parameter appears only for a MySQL database instance.
	DBA	The DBA of the database instance. The DBA can grant permissions to users.
	Query Timeout Period(s)	The timeout period for the execution of an SQL query statement. If the execution of an SQL query statement lasts longer than the specified timeout period, the execution of the statement is terminated to protect the database.
	Export Timeout Period(s)	The timeout period for the execution of an SQL export statement. If the execution of an SQL export statement lasts longer than the specified timeout period, the execution of the statement is terminated to protect the database.

After the instance information is configured, click Test Connection in the lower-left corner of the dialog box. Wait until the test is passed.
Note
If the connectivity test fails, check the parameter values that you specify based on the error message.
If the connectivity test succeeds, click Submit.

Perform a lock-free schema change

Log on to the DMS console V5.0.
In the top navigation bar, choose Database Development > Data Change > Lockless change.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All functions > Database Development > Data Change > Lockless change.

On the page that appears, configure the parameters. The following table describes only the key parameters. For more information about other parameters, see Perform lock-free DML operations.

Parameter

Description

Database

The database on which you want to perform a schema change. Select the database from the Database drop-down list. You can also enter a keyword to search for the database. You can specify one or more databases.

Have Permission: You can search for or select only the databases on which you have change permissions.
All: You can search for or select all databases except the databases for which metadata access control is enabled.
Note
If you do not have change permissions on a database, choose Security and Specifications > Permission Center > Permission Tickets in the top navigation bar. On the Permission Tickets tab, choose Access apply > Database-Permission in the upper-right corner. On the Access apply Tickets page, apply for the required permissions.

SQL Statements for Change

Enter DDL statements in the field, such as ALTER TABLE or OPTIMIZE.

Note

You can also enter DML statements to perform lock-free data changes. For more information, see Perform lock-free DML operations.

Click Submit.
DMS prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification in the Precheck step to modify the SQL statements and try again.
After the ticket is approved, click Execute Change in the Execute step.

Configure the parameters that are described in the following table for the task.

Parameter	Description
Execution Strategy	Running immediately: If you select this option, the task is immediately run after you click Confirm Execution. This is the default value. Schedule: If you select this option, you must specify the start time for the task. After you click Confirm Execution, the task is run at the specified point in time.
Specify End Time	on: Specify the time when the task ends. The system stops the task at the specified end time regardless of whether the task is complete. This prevents the task from affecting your business during peak hours. off: This is the default value.

Click Confirm Execution.
Note
A suspended task can be restarted.
- You can view the status, settings, and details of the task in the Execute step. You can also view the scheduling logs of the task.
- You can view the task progress. To do so, perform the following steps: In the top navigation bar, choose O&M > Task. On the Task tab, find the task and view the task progress. For more information, see View the progress of a lock-free schema change task.

Related operations

View the progress of the lock-free schema change task. For more information, see Manage tasks.
If the lock-free schema change feature is enabled, you can also perform lock-free regular data changes. For more information, see Perform regular data change.