Data Management (DMS) provides the data manipulation language (DML)-based lockless change feature. You can use this feature to change data without locking tables.

Background information

You can use this feature to perform lockless change in different batches based on the primary key or a non-null unique index in a table. This ensures high execution efficiency and minimizes the impact on database performance and capacity. For more information, see DML-based lockless change.

Prerequisites

One or more of the following databases are used:
  • MySQL series: self-managed MySQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and ApsaraDB OceanBase for MySQL
  • PostgreSQL series: self-managed PostgreSQL, ApsaraDB RDS for PostgreSQL, and PolarDB for PostgreSQL
  • PolarDB-O
  • MariaDB
Note For more information, see Supported database types and features.

Procedure

  1. Log on to the DMS console.
  2. , 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.
    Table 1. Parameters for a data change
    Parameter Description
    Database Required. The database where you want to perform a data 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 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 DML statements in the field, such as UPDATE, DELETE, or INSERT_SELECT statements. For more information about the scenario where you enter data definition language (DDL) statements in this field, see Change schemas without locking tables.
    • You cannot specify SQL transactions.
    SQL Statements for Rollback Optional. The SQL statements that can be executed to roll back the data change that is performed by the SQL statements for changing data. 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 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 one or more images or files that contain other necessary information for the ticket.
  4. Click Submit.
    DMS prechecks the SQL statements.
  5. Optional:Click chunk option. In the dialog box that appears, set the parameters as required and click Submit Change. The following table describes the parameters.
    Parameter Description
    chunk size(rows) The number of data rows to be processed at a time. Default value: 1000. DMS will perform batch operations based on table indexes in ascending order.
    sleep seconds per 10000 rows The interval at which the execution of SQL statements sleeps after every 10,000 data rows are processed. Unit: seconds. Default value: 0.5.
    Is enable master-slave delay check Valid values:
    • on: DMS checks whether the latency in synchronization between primary and secondary databases exceeds the upper limit. If the latency exceeds the upper limit, DMS suspends the SQL execution and waits until the primary and secondary databases are synchronized. This is the default value.

      After you set this parameter to on, you must set the master-slave delay threshold (seconds) parameter.

    • off
    master-slave delay threshold (seconds) The maximum latency in synchronization between the primary and secondary databases. Unit: seconds. Default value: 10.

    This parameter is displayed only when you set the Is enable master-slave delay check parameter to on.

    is enable pruning optimization The algorithm that is used to enable pruning optimization so as to reduce unnecessary traverse operations. Valid values:
    • on: If the current batch contains no UPDATE nor DELETE statement, DMS stops the task.
      Note You can set this parameter to on when the table has the primary key named id and the values of IDs increase in order.
    • off: This is the default value.
  6. 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.
  7. After the ticket is approved, click Execute Change in the Execute step. In the dialog box that appears, set the parameters that are 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.
    Specify End Time Specifies whether to stop running the task at a specified end time. Valid values:
    • on: The task stops running at the specified end time as required. When the specified end time arrives, DMS stops executing the remaining SQL statements. This prevents the SQL statements from affecting your business during peak hours.
    • off: This is the default value.
  8. 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. The task is running