Data Management (DMS) provides the 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 Edition
  • MariaDB
Note For more information, see Supported database types and features.

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 page, set the parameters as required.
    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 DML statements in the field, such as UPDATE, DELETE, or INSERT_SELECT statements. For more information about the scenario where you enter DDL statements in this field, see DDL-based lockless change.
    • You cannot specify SQL transactions.
    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. 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 performs 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 Specifies whether to check the synchronization latency between the primary and secondary databases. Valid values:
    • on: DMS checks whether the synchronization latency between the 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 synchronization latency 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 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. This reduces unnecessary traverse operations.
    • 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 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.
    Specify End Time Specifies whether to stop running the task at the specified point in time. Valid values:
    • on: The task stops running at the specified point in time as required. When the specified point in time is reached, 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 operation logs. The operation logs contain information such as the SQL statements, execution duration, and scheduling details. The task is running