When a large amount of data is stored, you must separate production data from historical data or regularly delete data from tables. When data changes in a table, DMS provides the feature to change data without the need to lock tables. You can change data in batches based on the primary key or a unique key without a null value in a table. This feature ensures high execution efficiency and minimizes the impact on database performance and capacity.

Procedure

The following procedure describes how to change all the data in the long_text_b column of the big_table table to random long text.

  1. Submit a ticket as a common user.
    1. Log on to the DMS console as a common user.
    2. In the top navigation bar, move the pointer over the More icon and choose Data Plans > Lockless change.
    3. Set the following parameters and click Submit.The Ticket Details page of changing data without the need to lock the table
      Parameter Description
      Database Required. Select the poc_dev database.
      Reason Category Required. The reason for this data change, which helps you find the ticket in subsequent operations.
      Business Background Required. The purpose or objective of the data change, which helps accelerate the approval process.
      SQL Statements for Change Enter the following UPDATE statement:
      UPDATE `big_table` SET `long_text_b` = 'ramdom long text' WHERE id < 1000000;
      Note This statement is used to change all data in the long_text_b column to random long text in the big_table table.
      SQL Statements for Rollback Optional. The SQL statements that you can execute to roll back the data change.
      Change Stakeholder Optional. The stakeholders can view the ticket details and assist developers in accelerating the approval process. Except for DMS administrators and database administrators (DBAs), users irrelevant to the ticket are not allowed to view the ticket details.
    4. On the ticket details page, click chunk option. In the dialog box that appears, set the following parameters and click Submit Change.chunk option
      Parameter Description
      chunk size(rows) The number of rows that are processed at a time. Default value: 1000.
      sleep seconds per 10000 rows The interval at which the execution of SQL statements pauses after every 10,000 rows are processed. Unit: seconds. Default value: 0.5.
      Is enable master-slave delay check Specifies whether to check the latency in primary-secondary replication.
      master-slave delay threshold (seconds) The maximum latency that is allowed in primary-secondary replication. Unit: seconds. Default value: 10.
      Is enable pruning optimization Specifies whether to enable pruning optimization.
    5. Confirm the ticket details.
      Note You can view the ticket details on the Ticket Details page. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user that submitted the ticket, and the estimated number of rows to scan.
    6. After the ticket details are confirmed and the precheck is completed, click Submit for Approval. In the message that appears, click OK.Submit for Approval
      Note You can change the ticket details only before you submit the ticket for approval.
  2. Approve and execute the ticket as an administrator.
    1. Log on to the DMS console as an administrator.
    2. On the Workbench tab of the DMS console, click Pending Tickets in the My Tickets section.Pending Tickets
    3. On the My Tickets page, click the number of the ticket that you want to approve.
    4. On the Ticket Details page, confirm the change information in the ticket and click Approve.Confirm the ticket of changing data without the need to lock the table
    5. In the dialog box that appears, enter comments and click Submit.Confirm and approve the data change ticket
    6. In the Execute step, click Execute Change. In the dialog box that appears, set the Execute Immediately parameter to Running immediately and click Confirm Execution.

      After you click Confirm Execution, DMS starts to execute the ticket.

      Task Settings
      Note
      • The default value of Execute Immediately is Running immediately. You can also set the Execute Immediately parameter to Schedule to select a ticket execution time.
      • If you turn on Specify End Time, DMS stops executing SQL statements when the end time that you specified arrives. This prevents SQL statements from affecting your business during peak hours.
    7. In the Execute step, click Details. In the dialog box that appears, click Execution Progress to view the progress.View the task progress
  3. Verify that the data is updated as an administrator.
    1. Log on to the DMS console as an administrator.
    2. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQL poc_dev tab.Go to the SQL poc_dev tab
    3. On the SQL poc_dev tab, enter the following SQL statement in the SQL editor and click Execute.
      SELECT * FROM `big_table`;
      Verify that the data is changed without the need to lock the table
      Note All data in the long_text_b column is updated.