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.

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 your role, see View system roles.

Procedure

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Data Assets. In the left-side navigation pane, click Instances.
  3. Find the database instance for which you want to enable the lock-free schema change feature, move the pointer over More in the Actions column, and then select Edit.
    Note Alternatively, you can click Home in the top navigation bar. Then, find the database instance for which you want to enable the lock-free schema change feature in the left-side navigation pane. Right-click the database instance and select Edit.
  4. Click Advanced Information.
  5. In the Lock-free Schema Change drop-down list, select Open (Open (DMS OnlineDDL first)) or Open (Open (MySQL Native OnlineDDL first)).
    OptionDescriptionRemarks
    Open (Open (DMS OnlineDDL first))DMS uses its own lock-free DDL feature to ensure that schemas can be changed without the need to lock tables.
    Note In the following scenarios, the lock-free DDL feature is unavailable and the native online DDL operations of MySQL are automatically used. If tables are locked or an error occurs in these scenarios, the task fails.
    • The table for which you want to change schemas 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 in 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.
    Open (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.
    CloseDMS routes SQL statements to the MySQL database for execution. In this case, DMS does not process the statements. N/A
  6. Click Submit.
    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: