Data Management (DMS) provides the lock-free DDL feature. You can use this feature to 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 synchronization latency from occurring between primary and secondary databases when schemas are changed by using native online DDL operations. This topic describes how to perform a lock-free DDL operation.

Prerequisites

  • The database type is ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, or MySQL databases from other sources.
    Note Databases from other sources refer to databases from other cloud service providers or self-managed databases.
  • The database engine is InnoDB, RocksDB, or X-Engine.
  • The binary logging feature is enabled for the database.
    Note By default, the binary logging feature is disabled for a PolarDB for MySQL cluster. For more information about how to enable the feature, see Enable binary logging.
  • 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.
  • The lock-free schema change feature is enabled for the database instance. For more information, see Enable the lock-free schema change feature.

Usage notes

  • Use a database account with the read and write permissions or a privileged database account. If the current database account does not have the required permissions, you can perform one of the following operations:
    • Grant permissions to the database account. For more information, see Manage user permissions on MySQL databases.
      Note Grant the ALL PRIVILEGES permission or the following read and write permissions to the database account:

      ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE, REPLICATION CLIENT, and REPLICATION SLAVE permissions

    • Change the database account. For more information, see Modify database instances.
  • Make sure that the database on which you want to perform the lock-free DDL operation has sufficient disk space. This is because the database needs to create a temporary table and copy the data of the original table to the temporary table. You must make sure that the available disk space of the database instance is more than twice the size of the table on which you want to perform the lock-free DDL operation. If the disk space of the database instance is insufficient, the instance is locked. For more information, see View the performance details of a database instance.
  • Make sure that the table on which you want to perform the lock-free DDL operation contains a primary key or unique key. When you perform lock-free schema changes, the primary key or unique key is used to copy all or part of data from the table and synchronize incremental data.
    Note If the table contains only a primary key or unique key, make sure that the primary key or unique key is not updated during the schema change. Otherwise, the schema change task fails.
  • The table name can be up to 56 characters in length.

Procedure

Note In this example, a database instance managed in Stable Change mode is used. If you want to perform a lock-free DDL operation on a database instance managed in Security Collaboration mode, you need to configure more parameters other than the parameters described in the following table, such as the Reason Category and Execution Method parameters. For more information, see the "Procedure" section of the Perform lock-free DML operations topic.
  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Lockless change.
  3. On the page that appears, configure the parameters that are described in the following table.
    ParameterDescription
    DatabaseThe 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 ChangeEnter DDL statements in the field, such as the ALTER TABLE or OPTIMIZE statement.
    Note You can also enter DML statements to perform lock-free data changes. For more information, see Perform lock-free DML operations.
  4. 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.
  5. After the ticket is approved, click Execute Change in the Execute step.
  6. Configure the parameters that are described in the following table for the task.
    ParameterDescription
    Execution Strategy
    • Running immediately: This is the default value. After you click Confirm Execution, the task is immediately run.
    • 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.
  7. 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.
    • Alternatively, you can choose O&M > Task in the top navigation bar. On the Task tab, find the task and view the task progress. For more information, see View the execution progress of a lock-free change task.
After you enable the lock-free schema change feature for the specified database instance, DMS preferentially applies this feature when you submit a specific type of ticket (including the type of ticket described in this topic) or run a specific type of task. For more information, see the following topics: