All Products
Search

This Product

    Data Management:Enable the lock-free schema change feature

    Document Center

    Data Management:Enable the lock-free schema change feature

    Last Updated:Oct 19, 2023

    To use the lock-free schema change feature provided by Data Management (DMS), you must enable this feature for the database instance that you want to manage. This topic describes how to enable the lock-free schema change feature. This topic describes how to enable the lock-free schema change feature.

    Prerequisites

    • The database type is ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, or MySQL database from other sources.

    • The database instance is managed in Stable Change or Security Collaboration mode in DMS. For more information, see View the control mode of an instance.

    • You are a database administrator (DBA) or a DMS administrator. For more information about how to view the role of a user, see View system roles.

    Procedure

    1. Log on to the DMS console V5.0.

    2. In the left-side-navigation pane, find the database instance for which you want to enable the lock-free schema change feature, right-click the database instance, and then select Edit.

      Note

      Alternatively, you can choose Data Assets > Instance in the top navigation bar. On the Instance List tab of the Instances page, find the instance for which you want to enable the lock-free schema change feature. Move the pointer over More in the Actions column and select Edit.

    3. In the Advanced Information section of the Edit dialog box, select Enabled (Open (DMS OnlineDDL first)) or Enabled (Open (MySQL Native OnlineDDL first)) from the Lock-free Structure Change drop-down-list.

      Option

      Description

      Remarks

      Enabled (Open (DMS OnlineDDL first))

      DMS uses its own lock-free DDL feature to ensure that schemas can be changed without locking tables.

      Note

      The lock-free DDL feature is unavailable in the following cases, and the native online DDL operations of MySQL are automatically used. If tables are locked or an error occurs in these cases, the task fails.

      • The table for which you want to change the schema is empty. Executing SQL statements on an empty table involves no risks. The native online DDL operations of MySQL are faster in this case.

      • A unique key is added to the SQL statement. The lock-free DDL feature of DMS does not support SQL statements that add unique keys.

      The schema change takes longer than it takes by using native online DDL operations of MySQL. However, this feature does not affect data copy and generates no latency.

      Enabled (Open (MySQL Native OnlineDDL first))

      DMS preferentially uses native online DDL operations of MySQL to change schemas. If tables are locked or schema change fails in this case, DMS uses the lock-free DDL feature to change schemas. This ensures that tables are not locked.

      The schema change is faster, but parallel threads for replication may become serial threads. This leads to synchronization latency between primary and secondary databases.

      Close

      DMS routes SQL statements to the MySQL database for execution. In this case, DMS does not process the statements.

      N/A

    4. Click Save.

      After you enable the lock-free schema change feature for the specified database instance, DMS preferentially applies this feature when you submit the following types of tickets: