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.
The Open (DMS OnlineDDL first) feature is enabled for an instance.
|Databases||Use the following databases:
|Database engines||Use the following database engines:
|Source instances||Use the following source instances:
|Control modes||Instances must be managed in the following control modes:
|Database accounts||Log on to databases by using one of the following accounts:
Note For more information about how to change an account, 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.
- Log on to the DMS console.
- , move the pointer over the More icon and choose .
- On the Data Change Ticket Application tab, set the parameters as required.
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
OPTIMIZEstatements. 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 the schema. 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 one or more 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 one or more images or files that contain other necessary information for the ticket.
- Click Submit. DMS prechecks the SQL statements.
- 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.
- 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: After you submit the ticket, the task is immediately run. This is the default value.
- Schedule: The task starts to be run at the time that you specify based on your needs.
- 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 Manage change processes.and manage the change process. For more information, see
Manage change processes
When a DDL-based lockless change task is running, database owners can perform the following steps to manage the change process:
- Log on to the DMS console.
- In the top navigation bar, move the pointer over the More icon and choose . The Task tab appears.
- Click the ID of the task that you want to manage. The Execution details dialog box appears.
- Click Progress in the Operation column. Note 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.
- 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.
For more information, see Appendix to DDL-based lockless change.