Data Management (DMS) provides the DDL-based lockless change feature. You can use this feature to change schemas without locking tables. This prevents your business from being affected by table locks that are caused due to 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.

Prerequisites

The Open (DMS OnlineDDL first) feature is enabled for a database instance.

After the Open (DMS OnlineDDL first) feature is enabled, you can use the following methods to change schemas without locking tables:

Usage notes

Item Description
Database Use self-managed MySQL, ApsaraDB RDS for MySQL, or PolarDB for MySQL databases.
Database engine Use the following database engines:
  • InnoDB
  • Rocksdb
  • X-Engine
Control mode Database instances must be managed in the following control modes:
  • Stable Change
  • Security Collaboration
Database account Use a privileged account or a database account with the read and write permissions. Specifically, the account must have the following 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 lockless changes has sufficient disk space for you to copy the data of the original table to a new table.
Note If the disk space of an RDS 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 this feature, see Enable binary logging.
Primary key or unique index of the original table To change the schema without locking a table, make sure that the original table contains a primary key or unique index. This allows you to copy all or part of data from the original table and synchronize incremental data.
  • If the table contains only a primary key, make sure that the primary key is not updated. Otherwise, the schema change fails.
  • If the table contains no primary key but a unique index, make sure that the unique index is not updated. Otherwise, the schema change fails.
  • If the table contains no primary key nor unique index, add a primary key or unique index to the table in the database instance. Then, change the schema without locking the table.

Procedure

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Lockless change.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Change > Lockless change.
  3. On the Data Change Ticket Application tab, set the parameters as required.
    Figure 1. Data Change Ticket Application tab
    Data Change Ticket Application tab
    Table 1. Parameters for data change
    Parameter Description
    Database Required. The database in which you want to perform a data change.
    Note You must have the change permissions on the database. If you have only the read permissions on the database or the change permissions on specific tables in the database, you cannot submit a data change ticket. For more information, see View owned permissions.
    Reason Category Required. The reason for the data change. This helps you find the ticket in subsequent operations.
    Business Background Required. The purpose or objective of the data change. This reduces unnecessary communication.
    Execution Method Required. The way in which you want the ticket to be submitted for execution. Valid values: After Audit Approved, Order Submitter Execute, After Audit Approved, Auto Execute, and Last Auditor Execute. Default value: After Audit Approved, Order Submitter Execute.
    SQL Statements for Change Required. The SQL statements that can be executed to perform the data change. DMS verifies the syntax of the SQL statements when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.
    Note Enter DDL statements in the field, such as ALTER TABLE or OPTIMIZE statements. For more information about the scenario where you enter DML statements in this field, see DML-based lockless change.
    SQL Statements for Rollback Optional. The SQL statements that can be executed to roll back the data change. Valid values:
    • Text: Enter the SQL statements in the SQL Text field.
    • Attachment: Click Upload a file to upload a TXT file or an SQL file.
    Change Stakeholder Optional. The stakeholders involved in the data change. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
    Attachments Optional. The images or files that are uploaded to add more information about the data change.
  4. Click Submit.
    DMS prechecks the SQL statements.
  5. Click Submit for Approval and wait for approval.
    Note
    • Before you submit the ticket for approval, you can modify the settings and scheduling policy that you have configured. You are not allowed to change these configurations after you submit the ticket for approval.
    • On the Lockless changeTicket Details tab, you can view the approval progress in the Approval step.
  6. After the ticket is approved, click Execute Change in the Execute step. In the dialog box that appears, set the Execute Immediately parameter that is described in the following table.
    Note If you set the Execution Method parameter to After Audit Approved, Auto Execute in the Apply step, this step is automatically skipped.
    Parameter Description
    Execute Immediately Specifies whether to run the task immediately or at a scheduled point in time. Valid values:
    • Running immediately: The task is immediately run after you click Confirm Execution. This is the default value.
    • Schedule: The task starts to be run at the point in time that you specify.
  7. Click Confirm Execution.
    When the task is running or after the task is run, you can click Details and Scheduling Details to view the task progress and operation logs. The operation logs contain information such as the SQL statements, execution duration, and scheduling details.
    Note If you are a database owner, you can click the O&M tab in the top navigation bar. Then, click the Task tab in the left-side navigation pane to manage the change process. For more information, see Manage change processes.
    The task is running

Manage change processes

When a DDL-based lockless change task is running, database owners can perform the following steps to manage the change process:

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. Click the task number of the task that you want to manage. The Execution details dialog box appears.
  3. Click Progress in the Operation column.
    Note
    • This button is displayed only when the task is being run.
    • You can also click the SQL statement or Log in the Scripts/logs column to view the statements or logs. You can click Skip or Repair tasks to skip or repair the task.
  4. In the Lock-Free structure change message, view the progress information of the task.
    Note If you need to specify the data amount for each copy operation to meet your specific business requirements, seek for after-sales support.

Appendix

For more information, see Appendix to DDL-based lockless change.