Data Management (DMS) provides the data definition language (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 avoids the latency in synchronization between primary and secondary databases that occurs when schemas are changed by using native online DDL operations.

Prerequisites

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

After the Open (DMS OnlineDDL first) feature is enabled, the following methods can be used to change schemas without locking tables:
  • Use the Schema Design feature.
  • Use the Normal Data Modify feature.
  • Use the DDL-based lockless change feature.
  • Use the task management feature. Database administrators (DBAs) and DMS administrators can create SQL tasks to change schemas in specified databases.

Usage notes

Item Description
Databases Use the following databases:
  • MySQL
Database engines Use the following database engines:
  • InnoDB
  • RocksDB
  • X-Engine
Source instances Use the following source instances:
  • ApsaraDB RDS instances on the classic network or self-managed databases that are hosted on Elastic Compute Service (ECS) instances on the classic network
  • ApsaraDB RDS instances in virtual private clouds (VPCs) or self-managed databases that are hosted on ECS instances in VPCs
  • Self-managed databases on the Internet
Control modes Instances must be managed in the following control modes:
  • Stable Change
  • Security Collaboration
Database accounts Log on to databases by using one of the following accounts:
  • A privileged account
  • An account that has logging permissions
  • An account that has read and write permissions
Note For more information about how to modify the database account information of a database instance, see Modify an instance.
Disk space Make sure that the database where you perform lockless change 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 instance. Then, change the schema without locking the table.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Data Plans > 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 a schema change
    Parameter Description
    Database Required. The database where you want to perform a schema change.
    Note You must have the change permission on the database. If you have only the read permissions on the database or the change permission on specific tables in the database, you cannot submit a schema change ticket. For more information, see View owned permissions.
    Reason Category Required. The reason for the schema change. This helps you find the ticket in subsequent operations.
    Business Background Required. The purpose or objective of the schema 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 schema 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 data manipulation language (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 schema change that is performed by the SQL statements for changing schemas. 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 of the schema change. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details.
    Attachments Optional. The images or files that are uploaded to add more information about the schema 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 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 time that you specify based on your needs.
  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 operations logs. The operations logs contain information such as the SQL statements, execution duration, and scheduling details.
    Note If you are a database owner, you can choose System > Task and 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.
  2. In the top navigation bar, move the pointer over the More icon and choose System > Task.
    The Task tab appears.
  3. Click the ID of the task that you want to manage. The Execution details dialog box appears.
  4. 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.
  5. 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.