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 the synchronization latency between primary and secondary databases from occurring 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 database from other sources.
  • The database engine is InnoDB, RocksDB, or X-Engine.
  • 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.

Limits

Item Description
Database account
  • Use a privileged account.
  • Use a database account that has the following read and write permissions:

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

Note
Disk space Make sure that the database in which you 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.
Note If the disk space of the database instance is insufficient, the instance will be locked.
Log format Enable the binary logging feature.
Note By default, the binary logging feature is disabled for PolarDB. For more information about how to enable the binary logging feature for PolarDB, see Enable binary logging.
Primary key or unique key of the original table To perform a lock-free DDL operation on a table, make sure that the table contains a primary key or unique key. The primary key or unique key is used to copy all or part of data from the table and synchronize incremental data.
  • If the table contains only a primary key, make sure that the primary key is not updated during the schema change. Otherwise, the schema change task fails.
  • If the table contains no primary key but a unique key, make sure that the unique key is not updated during the schema change. Otherwise, the schema change task fails.
  • If the table contains no primary key or unique key, add a primary key or unique key to the table before you perform the lock-free DDL operation.
Table name length The table name can be at most 56 characters in length.

Methods to perform lock-free schema changes

Submit a lock-free DDL ticket

  1. Go 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 Data Change Ticket Application page, set the parameters described in the following table.
    Parameter Description
    Database Select the database in which you want to perform a schema change from the Database drop-down list. You can also enter a keyword to search for the database.
    Note You must have the permissions to perform schema changes on the required database. For more information, see View owned permissions.
    SQL Statements for Change Enter DDL statements in the field, such as ALTER TABLE or OPTIMIZE statements.
    Note You can also enter DML statements to perform lock-free DML operations. 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 to modify the SQL statements and try again.
  5. After the ticket is approved, click Execute Change in the Execute step.
    Note On the Ticket Details page, you can view the approval progress in the Approval step.
  6. Set the parameter for the task.
    Parameter Description
    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.
  7. Click Confirm Execution.
    • You can view the execution status, settings, and details of the task in the Execute step. You can also view the scheduling logs.
    • Alternatively, you can click O&M in the top navigation bar. In the left-side navigation pane, click Task Management. Then, find the task and view the task progress. For more information, see View the progress of a lock-free change task.