Get started with lock-free schema change

Updated at:

The lock-free schema change feature in Data Management Service (DMS) lets you modify table schemas without locking tables. This prevents business interruptions and mitigates primary-standby replication latency from native online DDL operations. This topic uses an ApsaraDB RDS for MySQL instance as an example to show you how to perform a lock-free schema change in DMS.

Add instance and enable lock-free schema change

  1. Log in to DMS 5.0.

  2. In the navigation pane on the left, click the xinzeng icon.

    Note

    If you are using the console in simple mode, click Database Instances in the navigation pane on the left, and then click the xinzeng icon.

  3. In the Add Instance dialog box, configure the following parameters:

    1. On the Alibaba Cloud tab, select MySQL.

    2. Configure the parameters in the Basic Information and Advanced Information sections. For more information, see Register a cloud database instance.

  4. After you configure the parameters, click Test Connection in the lower-left corner of the Basic Information section and wait for the test to succeed.

    Note

    If the connection test fails, check and modify your settings based on the error message.

  5. After the connection test succeeds, click Submit.

Lock-free schema change

  1. Log in to DMS 5.0.

  2. In the top navigation bar, choose Database Development > Data Change > Lock-free Change.

    Note

    If you use the DMS console in simple mode, move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner of the console and choose All Features > Database Development > Data Change > Lockless Change.

  3. Configure the lock-free change ticket. The following table describes key parameters. For more information about other parameters, see Lock-free DML change.

    Parameter

    Description

    Database

    Search for and select one or more destination databases.

    • Have Permission: Shows only databases for which you have change permissions.

    • All: Shows all databases except for those with metadata access control enabled.

      Note

      If you do not have change permissions on the target database, choose Security and Specifications > Permission Center > Permission Tickets in the top navigation bar. Then, click Apply for Permission > Database Permissions to apply for the required permissions.

    SQL statements for change

    Enter Data Definition Language (DDL) statements, such as ALTER TABLE or OPTIMIZE.

    Note

    If you enter Data Manipulation Language (DML) statements, it initiates a lock-free data change task. For more information, see Lock-free DML change.

  4. Click Submit Application.

    The system automatically performs an SQL precheck. If the precheck fails, you can click Modify SQL to revise the SQL statements based on the error and then resubmit.

  5. After the ticket is approved, click Execute Change in the Execute section of the ticket details page.

  6. Configure the task execution parameters.

    Parameter

    Description

    Execution strategy

    • Execute Immediately: This is the default option. The task starts immediately after you click Confirm Execution.

    • Schedule: Select a start time for the task. The task will run at the specified time after you click Confirm Execution.

    Specify end time

    • On: Select an end time for the task. If the task is not completed by the specified end time, the system stops the task. This prevents tasks from running during peak hours and affecting business operations.

    • Off: This is the default option.

  7. Click Confirm Execution.

    Note

    A paused task restarts from the beginning.

    • In the Execute section, you can view the task status, task settings, scheduling logs, and other information.

    • You can also view the progress of the lock-free schema change task by choosing O&M > Task Management. For more information, see View the progress of a lock-free change task.

Related operations

  • Track the progress of change tasks in the Task Management feature of DMS. For more information, see Task Management.

  • If the lock-free schema change feature is enabled, you can also perform Standard Data Change tasks without locking tables. For more information, see Standard Data Change.